概念描述
一、什么是分区表
分区表就是根据分区策略，将数据分散到不同的子表中，并通过父表建立关联关系，从而实现数据物理上分区。
优点:
1.在莫某些情况下查询性能能够显著提升，特别是当那些访问压力大的行在一个分区或者少数几个分区时。划分可以取代索引的主导列、减小索引尺寸以及使索引中访问压力大的部分更有可能被放在内存中。
2.当查询或更新访问一个分区的大部分行时，可以通过该分区上的一个顺序扫描来取代分散到整个表上的索引和随机访问，可以改善性能
3.如果批量操作的需求是在分区设计时就规划好的，则批量加载和删除可以通过增加或者去除分区的方式来完成。执行alter table detach partition或者使用drop table 删除一个分区速度快于批量操作。也避免了delete带来的vacuum的开销
4.很少使用的数据可以被迁移到便宜且较慢的存储介质上测试验证
二、分区表的两种形式
2.1 表继承
表继承是10版本以前的主要使用方式。继承的方式需要使用约束来定义分区和规则，还需要使用触发器将数据路由到适合的分区，用户必须编写和维护这些代码。表分区就是把逻辑上的一个大表分割成物理上的几块。当表的大小超过了数据库服务器的物理内存大小时，建议使用分区表。在使用继承实现分区表时，一般会让父表为空，数据都存储在子表中。
实现步骤：
1.创建父表，所有的分区都从父表继承。该表中没有数据，不要在其上定义任何检查约束，除非你希望约束所有的分区。同样，在其上定义索引或者唯一约束也没有意义。
2.创建几个子表，每个都是从主表上继承的。通常这些表不会增加任何字段。我们将把子表称作分区，实际上它们就是普通的PostgreSQL表。
3.给分区表增加约束，定义每个分区允许的健值。
4.对于每个分区，在关键字字段上创建一个索引，也可创建其他你想创建的索引。严格来说，关键字字段索引并非必需的，但是在大多数情况下它是很有帮助的。如果你希望关键字值是唯一的，那么应该总是给每个分区创建一个唯一约束或者主键约束。
5.定义一个规则或者触发器，把对主表的数据插入重定向到合适的分区表中。
6.确保constraint_exclusion中的配置参数postgresql.conf是打开的。打开后，如果查询中WHERE子句的过滤条件与分区的约束条件匹配，那么该查询会智能地只查询此分区，而不会查询其他分区。例如：
--表继承是PostgreSQL中特有的，假设有一张人员表person
create table person(
name text,
age int,
sex boolean
);
--现在要再加有个学生表student，学生表比人员表多了一个班级号字段class_no，查询person可以查询到这两条数据
create table students(
class_no int
) inherits(person);
--插入两条数据
insert into students values('张三',15,true,1);
insert into students values('翠莲',14,false,2);
postgres=# select * from person;
name | age | sex
------+-----+-----
张三 | 15 | t
翠莲 | 14 | f
(2 rows)
postgres=# select * from students;
name | age | sex | class_no
------+-----+-----+----------
张三 | 15 | t | 1
翠莲 | 14 | f | 2
(2 rows)表继承中父表与子表的关系是，当查询父表时会把父表中子表的数据也查询出来，反之则不行。
如果只想把父表本身的数据查询出来，只需要在查询的表名前加上only关键字
postgres=# select * from only person;
name | age | sex
------+-----+-----
张三 | 15 | t
翠莲 | 14 | f
(2 rows)所有父表的检查约束和非空约束都会自动被所有子表继承。不过其他类型的约束（唯一、主键、外键）不会被继承。
一个子表可以从多个父表继承，这种情况下它将拥有所有父表字段的总和，并且子表中定义的字段也会加入其中。如果同一个字段名出现在多个父表中，或者同时出现在父表和子表的定义里，那么这些字段就会被“融合”，因此在子表里就只有一个这样的字段。要想融合，字段的数据类型必须相同，否则就会报错。融合的字段将会拥有其父字段的所有检查约束，并且如果某个父字段存在非空约束，那么融合后的字段也必须是非空的。采用SELECT、UPDATE、DELETE等命令访问或操作父表时，也会同时访问或操作相应的子表，而使用ALTER TABLE命令修改父表的结构定义时，大多数情况下也会同时修改子表的结构定义，但“REINDEX”“VACUUM”命令不会影响到子表。
此外，唯一约束、外键的使用域也不会扩大到子表上。
2.2 声明式分区
postgresql提供一种方法将表划分成称为分区的片段，被划分的表称为分区表，这种分区方式由分区的方法以及要被用作分区键的列或者表达式列表组成。当前支持的分区方法是范围(range)、列表（list）和哈希（hash）。
分区本身也可能被定义为分区表，这种用法被称为子分区。分区可以有自己的与其他分区不同的索引、约束和默认值。
常规表无法转换成分区表，分区表可以转成常规表。 可以把一个包含数据的常规表或者分区表作为分区加入到另一个分区表，或者从分区表中移走一个分区并且把它变成一个独立的表。可以使用 ATTACH PARTITION和 DETACH PARTITION命令。
2.2.1 PostgreSQL分区表基本使用
List分区：
列表分区明确指定根据某个字段的某个具体值进行分区，默认分区（可选值）保存不属于任何指定分区的列表值。
CREATE TABLE students (id INTEGER, status character varying(30), name character varying(30)) PARTITION BY LIST(status);
CREATE TABLE stu_active PARTITION OF students FOR VALUES IN ('ACTIVE');
CREATE TABLE stu_exp PARTITION OF students FOR VALUES IN ('EXPIRED');
CREATE TABLE stu_others PARTITION OF students DEFAULT;
postgres=# \d+ students
Partitioned table "public.students"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | character varying(30) | | | | extended | |
name | character varying(30) | | | | extended | |
Partition key: LIST (status)
Partitions: stu_active FOR VALUES IN ('ACTIVE'),
stu_exp FOR VALUES IN ('EXPIRED'),
stu_others DEFAULT
postgres=# \d+ stu_active
Table "public.stu_active"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | character varying(30) | | | | extended | |
name | character varying(30) | | | | extended | |
Partition of: students FOR VALUES IN ('ACTIVE')
Partition constraint: ((status IS NOT NULL) AND ((status)::text = 'ACTIVE'::character varying(30)))
Access method: heap
postgres=# INSERT INTO students VALUES (1,'ACTIVE','zhangyit'), (2,'EXPIRED','lias'), (3,'EXPIRED','wangwei'), (4,'REACTIVATED','liulah');
postgres=# select tableoid::regclass,* from students;
tableoid | id | status | name
------------+----+-------------+----------
stu_active | 1 | ACTIVE | zhangyit
stu_exp | 2 | EXPIRED | lias
stu_exp | 3 | EXPIRED | wangwei
stu_others | 4 | REACTIVATED | liulahrange分区
范围分区就是根据某个字段值的范围来进行分区
CREATE TABLE students (id INTEGER, status character varying(30), grade INTEGER) PARTITION BY RANGE(grade);
CREATE TABLE stu_fail PARTITION OF students FOR VALUES FROM (MINVALUE) TO (60);
CREATE TABLE stu_pass PARTITION OF students FOR VALUES FROM (60) TO (75);
CREATE TABLE stu_excellent PARTITION OF students FOR VALUES FROM (75) TO (MAXVALUE);
postgres=# \d+ students
Partitioned table "public.students"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | character varying(30) | | | | extended | |
grade | integer | | | | plain | |
Partition key: RANGE (grade)
Partitions: stu_excellent FOR VALUES FROM (75) TO (MAXVALUE),
stu_fail FOR VALUES FROM (MINVALUE) TO (60),
stu_pass FOR VALUES FROM (60) TO (75)
postgres=# \d+ stu_fail
Table "public.stu_fail"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | character varying(30) | | | | extended | |
grade | integer | | | | plain | |
Partition of: students FOR VALUES FROM (MINVALUE) TO (60)
Partition constraint: ((grade IS NOT NULL) AND (grade < 60))
Access method: heap
postgres=# \d+ stu_pass
Table "public.stu_pass"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | character varying(30) | | | | extended | |
grade | integer | | | | plain | |
Partition of: students FOR VALUES FROM (60) TO (75)
Partition constraint: ((grade IS NOT NULL) AND (grade >= 60) AND (grade < 75))
Access method: heap
postgres=# \d+ stu_excellent
Table "public.stu_excellent"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | character varying(30) | | | | extended | |
grade | integer | | | | plain | |
Partition of: students FOR VALUES FROM (75) TO (MAXVALUE)
Partition constraint: ((grade IS NOT NULL) AND (grade >= 75))
Access method: heap
postgres=# INSERT INTO students VALUES (1,'ACTIVE',58), (2,'ACTIVE',60), (3,'ACTIVE',75), (4,'ACTIVE',90);
INSERT 0 4
postgres=# select tableoid::regclass,* from students;
tableoid | id | status | grade
---------------+----+--------+-------
stu_fail | 1 | ACTIVE | 58
stu_pass | 2 | ACTIVE | 60
stu_excellent | 3 | ACTIVE | 75
stu_excellent | 4 | ACTIVE | 90注意：minvalue的上限值是小于，不是小于等于。而maxvalue的区间的下限是大于等于，不是大于。
hash分区
通过对每个分区使用取模和余数来创建hash分区，modulus指定了对N取模，而remainder指定了除完后的余数
CREATE TABLE students (id INTEGER, status character varying(30), grade INTEGER) PARTITION BY HASH(id);
CREATE TABLE stu_part1 PARTITION OF students FOR VALUES WITH (modulus 3, remainder 0);
CREATE TABLE stu_part2 PARTITION OF students FOR VALUES WITH (modulus 3, remainder 1);
CREATE TABLE stu_part3 PARTITION OF students FOR VALUES WITH (modulus 3, remainder 2);
postgres=# \d+ stu_part1
Table "public.stu_part1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | character varying(30) | | | | extended | |
grade | integer | | | | plain | |
Partition of: students FOR VALUES WITH (modulus 3, remainder 0)
Partition constraint: satisfies_hash_partition('20026077'::oid, 3, 0, id)
Access method: heap
postgres=# \d+ stu_part2
Table "public.stu_part2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | character varying(30) | | | | extended | |
grade | integer | | | | plain | |
Partition of: students FOR VALUES WITH (modulus 3, remainder 1)
Partition constraint: satisfies_hash_partition('20026077'::oid, 3, 1, id)
Access method: heap
postgres-# \d+ stu_part3
Table "public.stu_part3"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | character varying(30) | | | | extended | |
grade | integer | | | | plain | |
Partition of: students FOR VALUES WITH (modulus 3, remainder 2)
Partition constraint: satisfies_hash_partition('20026077'::oid, 3, 2, id)
Access method: heap
postgres=# INSERT INTO students VALUES (1,'ACTIVE',58), (2,'ACTIVE',60), (3,'ACTIVE',75), (4,'ACTIVE',90);
INSERT 0 4
postgres=# select tableoid::regclass,* from students;s
tableoid | id | status | grade
-----------+----+--------+-------
stu_part1 | 2 | ACTIVE | 60
stu_part1 | 4 | ACTIVE | 90
stu_part2 | 3 | ACTIVE | 75
stu_part3 | 1 | ACTIVE | 58当在分区表上创建一个索引，postgresql会自动为每个分区创建具有相同属性的索引
三、分区表转换
3.1 分区表转换成普通表
可以使用DETACH PARTITION命令把分区表转换成普通表
postgres=# alter table students detach partition stu_part3;
ALTER TABLE
postgres=# \d+ students
Partitioned table "public.students"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | character varying(30) | | | | extended | |
grade | integer | | | | plain | |
Partition key: HASH (id)
Partitions: stu_part1 FOR VALUES WITH (modulus 3, remainder 0),
stu_part2 FOR VALUES WITH (modulus 3, remainder 1)
postgres=# \d+ stu_part3
Table "public.stu_part3"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | character varying(30) | | | | extended | |
grade | integer | | | | plain | |
Access method: heap3.2 普通表转换成分区表
我们也可以把某个普通表在附加到分区表上去，使用attach partition命令。
postgres=# alter table students attach partition stu_part3 FOR VALUES WITH (modulus 3, remainder 2);
ALTER TABLE
postgres=# \d+ students
Partitioned table "public.students"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | character varying(30) | | | | extended | |
grade | integer | | | | plain | |
Partition key: HASH (id)
Partitions: stu_part1 FOR VALUES WITH (modulus 3, remainder 0),
stu_part2 FOR VALUES WITH (modulus 3, remainder 1),
stu_part3 FOR VALUES WITH (modulus 3, remainder 2)
postgres=# \d+ stu_part3
Table "public.stu_part3"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
status | character varying(30) | | | | extended | |
grade | integer | | | | plain | |
Partition of: students FOR VALUES WITH (modulus 3, remainder 2)
Partition constraint: satisfies_hash_partition('20026077'::oid, 3, 2, id)
Access method: heap附加上去的表必须具有相同的列/类型，不能存在额外的列。
在运行ATTACH PARTITION命令之前，建议在要附加的表上创建一个与所需分区约束匹配的约束。这样，系统将能够跳过扫描以验证隐式分区约束。如果没有约束，则将扫描附加表以验证分区约束正确性，同时对该分区持有ACCESS EXCLUSIVE锁定，并在父表上持有SHARE UPDATE EXCLUSIVE锁。在完成ATTACH PARTITION后，可能需要删除冗余CHECK约束。
四、分区裁剪
分区裁剪就是当已知分区不包含我们要的数据时，在查询的时候就不扫描这个分区，以获得更高的性能。 在PostgreSQL 10中，这种消除是通过constraint_exclusion机制来实现的，这种机制是一种线性算法，需要一个一个地查看每个分区的元数据，以检查该分区是否与查询WHERE子句匹配，很显然这种效率并不高。而在PostgreSQL 11版本引入了新的算法，不再是详细的搜索，而使用二分查找法搜索，可以快速识别匹配的LIST和RANGE分区，而HASH分区则通过散列函数查找匹配的分区。
postgresql 12中通过enable_partition_pruning进行控制，默认是on。
例如：表t1
pepsidb=# \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-----------------+-------------------+----------+---------+-------------
pepsi | t1 | partitioned table | postgres | 0 bytes |
pepsi | t1_partition_t1 | table | postgres | 1275 MB |
pepsi | t1_partition_t2 | table | postgres | 544 MB |
pepsi | t1_partition_t3 | table | postgres | 558 MB |
(4 rows)
pepsidb=# \d+ t1
Partitioned table "pepsi.t1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------------------------+--------------------------------+-----------+----------+------------------------+----------+--------------+-------------
obj_id | character varying(42) | | not null | | extended | |
ast_id | character varying(42) | | not null | | extended | |
operatemaint_workorder_id | character varying(42) | | not null | | extended | |
work_content | character varying(4000) | | | | extended | |
professional_type | character varying(10) | | | | extended | |
actual_end_time | timestamp(0) without time zone | | | | plain | |
actual_start_time | timestamp(0) without time zone | | | | plain | |
person_amount | integer | | | | plain | |
work_order_team | character varying(42) | | | | extended | |
station_line_id | character varying(42) | | | | extended | |
station_line_name | character varying(200) | | | | extended | |
station_line_voltage_level | character varying(20) | | | | extended | |
standard_operate_code | character varying(42) | | | | extended | |
equip_name | character varying(200) | | | | extended | |
equip_type | character varying(20) | | | | extended | |
equip_type_name | character varying(200) | | | | extended | |
equip_code | character varying(50) | | | | extended | |
equip_voltage_level | character varying(5) | | | | extended | |
asset_code | character varying(50) | | | | extended | |
asset_value | numeric(10,2) | | | | main | |
collect_state | character varying(2) | | | | extended | |
standard_artificial | numeric(10,2) | | | | main | |
standard_machine | numeric(10,2) | | | | main | |
practical_artificial | numeric(10,2) | | | | main | |
practical_machine | numeric(10,2) | | | | main | |
sync_time | timestamp(0) without time zone | | | | plain | |
last_update_time | timestamp(0) without time zone | | | | plain | |
belong_ast_id | character varying(42) | | | | extended | |
ticket_make_unit | character varying(42) | | | | extended | |
year | character varying(4) | | | | extended | |
month | character varying(2) | | | | extended | |
person_name | character varying(500) | | | | extended | |
ticket_number | character varying(200) | | | | extended | |
is_calc | character varying(2) | | | '0'::character varying | extended | |
operate_steps | integer | | | | plain | |
name_match | character varying(2) | | | | extended | |
Partition key: RANGE (actual_start_time)
Indexes:
"idx_ast_id" btree (ast_id)
Partitions: t1_partition_t1 FOR VALUES FROM (MINVALUE) TO ('2020-12-31 23:59:59'),
t1_partition_t2 FOR VALUES FROM ('2021-01-01 00:00:00') TO ('2021-06-30 23:59:59'),
t1_partition_t3 FOR VALUES FROM ('2021-07-01 00:00:00') TO ('2021-12-31 23:59:59')如果没有分区裁剪，查询就会扫描表t1的每一个分区，如果启用了分区裁剪，优化器将会检查每个分区的定义并检查该分区是否因为不包含符合查询where子句的行而无需扫描。当优化器可以证实这一点时，它就会把不符合要求的分区从执行计划中排除。
通过使用explain命令和enable_partition_pruning参数，可以展示裁剪掉分区的计划与没有裁剪的计划之间的差别。
pepsidb=# set enable_partition_pruning = off;
SET
pepsidb=# explain select * from t1 where actual_start_time = '2020-11-04 10:59:51';
QUERY PLAN
------------------------------------------------------------------------------------------------
Gather (cost=1000.00..325912.46 rows=314 width=567)
Workers Planned: 2
-> Parallel Append (cost=0.00..324881.06 rows=131 width=567)
-> Parallel Seq Scan on t1_partition_t1 (cost=0.00..174261.00 rows=52 width=569)
Filter: (actual_start_time = '2020-11-04 10:59:51'::timestamp without time zone)
-> Parallel Seq Scan on t1_partition_t3 (cost=0.00..76284.32 rows=39 width=569)
Filter: (actual_start_time = '2020-11-04 10:59:51'::timestamp without time zone)
-> Parallel Seq Scan on t1_partition_t2 (cost=0.00..74335.09 rows=40 width=565)
Filter: (actual_start_time = '2020-11-04 10:59:51'::timestamp without time zone)
(9 rows)当我们使用分区裁剪时，我们会得到一个cost较小的执行计划。分区裁剪仅由分区键隐式定义的约束所驱动，而不是由索引驱动；因此没有必要在键列上创建索引
pepsidb=# set enable_partition_pruning = on;
SET
pepsidb=# explain select * from t1 where actual_start_time = '2020-11-04 10:59:51';
QUERY PLAN
------------------------------------------------------------------------------------------
Gather (cost=1000.00..175273.60 rows=126 width=569)
Workers Planned: 2
-> Parallel Seq Scan on t1_partition_t1 (cost=0.00..174261.00 rows=52 width=569)
Filter: (actual_start_time = '2020-11-04 10:59:51'::timestamp without time zone)
(4 rows)