分区表

前言

分区表是解决单表过大引起的性能问题的一种方式，因为单表如果过大，执行全表扫描的成本就会增加，进而导致查询变慢。一般情况下，当单表大小超过内存大小的时候就应该考虑使用分区。使用表分区有以下好处：

删除历史数据更快。如果是按时间分区，在删除历史数据时，直接删除历史分区即可。
某些场景下查询性能会得到极大提升。当热点数据分布在同一块分区或少数几个分区时，查询性能会提升很多。
低频使用分区数据可以通过表空间的方法，将其移动到较廉价的慢速存储介质中。

Postgres 支持三种形式的分区：范围分区，列表分区，哈希分区，以及两种创建分区的方式：继承式分区和声明式分区(使用声明式分区表需要postgres版本高于10，若pg版本高于10，尽量使用声明式分区表)。

范围分区

范围分区是根据一个分区键或一组分区键划分为相应的“范围”的。并且不同的分区的范围之间没有重叠。分区键的值是连续的，在表中插入数据时，按照分区键的匹配关系决定将数据保存到哪个分区中。例如，可以根据日期范围或特定业务对象的标志符划分范围分区。

示例：用声明式创建父表

create table sales_detail (
	product_id int not null, -- 产品编号
    price numeric(12,2), -- 价格
    amount int not null, -- 数量
    sale_date date not null, -- 销售日期
    buyer varchar(40), -- 买家名称
    buyer_contact text -- 买家联系方式
) partition by range (sale_date);

创建分区表

create table sales_detail_y2014m01 partition of sales_detail for values from ('2014-01-01') to ('2014-02-01');
create table sales_detail_y2014m02 partition of sales_detail for values from ('2014-02-01') to ('2014-03-01');
create table sales_detail_y2014m03 partition of sales_detail for values from ('2014-03-01') to ('2014-04-01');

声明式分区表是通过DDL语句直接创建分区表，实际上内部还是继承式的逻辑。

改用继承式实现：

-- 1. 创建父表
create table sales_detail (
	product_id int not null, -- 产品编号
    price numeric(12,2), -- 价格
    amount int not null, -- 数量
    sale_date date not null, -- 销售日期
    buyer varchar(40), -- 买家名称
    buyer_contact text -- 买家联系方式
);

-- 2. 创建分区表
create table sales_detail_y2014m01 (check (sale_date >= date '2014-01-01' and sale_date < date '2014-02-01')) inherits (sales_detail);
create table sales_detail_y2014m02 (check (sale_date >= date '2014-02-01' and sale_date < date '2014-03-01')) inherits (sales_detail);
create table sales_detail_y2014m03 (check (sale_date >= date '2014-03-01' and sale_date < date '2014-04-01')) inherits (sales_detail);

列表分区

列表分区是根据特定的值来划分相应的区间。

示例：基于员工的职位job创建列表分区

-- 1. 创建父表
create table emp_list_by_job (
    empno int,
    ename varchar(10),
    job varchar(10)
) partition by list(job);

-- 2. 创建分区表
create table emp_job1 partition of emp_list_by_job for values in ('JOB1');
create table emp_job2 partition of emp_list_by_job for values in ('JOB2');

哈希分区

哈希分区是指按照分区键的哈希值建立分区，如果分区键的哈希值相同，那么对应的数据将保存到同一个分区中。

-- 1. 创建父表
create table emp_hash_by_job (
    empno int,
    ename varchar(10),
    job varchar(10)
) partition by hash(job);

-- 2. 创建子表
-- 采用取模的方式，要创建N个哈希分区，就要取N次模
create table emp_hash_job1 partition of emp_hash_by_job for values with (modulus 4, remainder 0);
create table emp_hash_job1 partition of emp_hash_by_job for values with (modulus 4, remainder 1);
create table emp_hash_job1 partition of emp_hash_by_job for values with (modulus 4, remainder 2);
create table emp_hash_job1 partition of emp_hash_by_job for values with (modulus 4, remainder 3);

补充

对于声明式分区表，可以直接在父表上管理索引；对于继承式分区表需要在子表上挨个管理。

前言​

范围分区​

列表分区​

哈希分区​

补充​

前言

范围分区

列表分区

哈希分区

补充