原文：https://zhuanlan.zhihu.com/p/116589705
PostgreSQL 有两种父、子表关系：分区（partition）和继承（inherit）。在PostgreSQL中，表分区是内置声明式分区（built-in built-in declarative partitioning），适用于大部分常见用例；另外通过表继承也能实现表分区，而且具有一些声明式分区不具备的特性。
注意：内置声明式分区表（built-in declaratively patitioned table），在不同的地方可能会有不同的叫法，总体上：原生分区 = 内置分区表 = 声明式分区表 = 分区表。
一、内置声明式表分区
表的分区就是将一个逻辑上的大表(主要指数据量大)，切分为多个小的物理的分片。
分区的优点：
1 在某些情况下，尤其是当表中大多数被频繁访问的行位于单个分区或少量分区中时，查询性能可以得到显着提高。分区替代了索引的前几列，从而减小了索引的大小，并使索引中频繁使用的部分更有可能装入内存。
2 当查询或更新访问单个分区的很大一部分时，可以通过对该分区进行顺序扫描而不是使用索引和分散在整个表中的随机访问读取，来提高性能。
直接从分区表查询数据比从一个大而全的全量数据表中读取数据效率更高。
3 如果计划将这种需求计划到分区设计中，则可以通过添加或删除分区来完成批量加载和删除。使用ALTER TABLE DETACH PARTITION或使用DROP TABLE删除单个分区比批量操作要快得多。这些命令还完全避免了由批量DELETE引起的VACUUM开销。
数据维护成本降低。比如：某一部分数据失效，不需要执行命令来更新数据，可以直接解绑指定关系，解除绑定的数据和分区表都依然保留，需要时可以随时恢复绑定。通过 Flyway 等数据脚本管理能够方便的控制数据维护，避免人为直接操作数据。
4 很少使用的数据可以迁移到更廉价、更迟缓的存储介质上。
一个表只能放在一个物理空间上，使用分区表之后可以将不同的表放置在不同的物理空间上，从而达到冷数据放在廉价的物理机器上，热点数据放置在性能强劲的机器上。
通常只有在表很大的情况下，这些好处才是值得的。表可以从分区中受益的确切时间取决于应用程序，尽管经验法则是表的大小应超过数据库服务器的物理内存。
分区的使用限制：
1 无法创建跨所有分区的排除约束。只能单独约束每个叶子分区。
2 因为PostgreSQL只能在每个分区中单独进行唯一性约束；因此，分区表上的唯一约束必须包括所有分区键列。
3 如果需要 BEFORE ROW 触发器，则必须定义在单个分区（而不是分区父表）。
4 不允许在同一分区树中混合临时和永久关系。因此，如果父表是永久性的，则其分区也必须是永久性的；如果父表是临时的，则其分区也必须是临时的。当使用临时关系时，分区树的所有成员必须来自同一会话。
3种分区策略：
PostgreSQL内置支持以下3种方式的分区：
- 范围（Range ）分区：表被划分为由键列或列集定义的“范围”，分配给不同分区的值的范围之间没有重叠。例如：可以按日期范围或特定业务对象的标识符范围，来进行分区。
- 列表（List）分区：通过显式列出哪些键值出现在每个分区中来对表进行分区。
- 哈希（Hash）分区：（自PG11才提供HASH策略）通过为每个分区指定模数和余数来对表进行分区。每个分区将保存行，分区键的哈希值除以指定的模数将产生指定的余数。
分区的创建和使用
PostgreSQL提供了将一个表切分为多个片的方法，这些片叫做分区，被切分的表称作被分区表（这里简称父表）。该规范包括分区方法和用作分区键的列或表达式列表。
创建分区的方法总结：
1 创建父表：指定分区键字段、分区策略（RANGE | LIST | HASH）；
2 创建分区：指定父表、分区键范围（分区键范围重叠之后会直接报错）或DEFAULT；
3 在分区上创建索引：通常，分区键字段上的索引是必须的。
创建父表：
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
{ column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option ... ] }
[, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
...
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
OF type_name [ (
{ column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
| table_constraint }
[, ... ]
) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
...创建分区：
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
PARTITION OF parent_table [ (
{ column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
| table_constraint }
[, ... ]
) ] { FOR VALUES partition_bound_spec | DEFAULT }
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
....
partition_bound_spec is:
IN ( partition_bound_expr [, ...] ) |
FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )父表与分区建立绑定关系
ALTER TABLE [ IF EXISTS ] name
ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
partition_bound_spec is:
IN ( partition_bound_expr [, ...] ) |
FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )父表与分区解除绑定关系：
ALTER TABLE [ IF EXISTS ] name
DETACH PARTITION partition_name;删除分区及其中数据（与删除表方法相同）
DROP TABLE [ IF EXISTS ] partition_name [, ...] [ CASCADE | RESTRICT ]
RESTRICT：缺省选项，如果有任何对象依赖该表则拒绝删除该表。
CASCADE：自动删除依赖于表的对象（例如视图），然后自动删除依赖于那些对象的所有对象（请参见：5.14 依赖追踪）参考：PostgreSQL12 Doc - 5.14 依赖追踪：
SQL实战：
1 创建父表
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);2 创建分区及子分区
--创建分区
CREATE TABLE measurement_y2007m11 PARTITION OF measurement
FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');
CREATE TABLE measurement_y2007m12 PARTITION OF measurement
FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
TABLESPACE fasttablespace;
CREATE TABLE measurement_y2008m01 PARTITION OF measurement
FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
WITH (parallel_workers = 4)
TABLESPACE fasttablespace;
--创建子分区 sub-partition
CREATE TABLE measurement_y2006m02 PARTITION OF measurement_y2006
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
PARTITION BY RANGE (peaktemp);
3 创建索引
在父表的键字段创建索引，以及创建需要的任何其他索引。 （键索引不是严格必需的，但是在大多数情况下它是有帮助的。）这将在每个分区上自动创建一个索引，以后创建或附加的任何分区也将包含该索引。
CREATE INDEX ON measurement (logdate);4 确保 postgresql.conf 中的 enable_partition_pruning 启用，否则，查询将不会被优化。
在上面的示例中，我们每个月都会创建一个新分区，因此编写一个脚本自动生成所需的DDL可能是明智的。
5 维护分区
Analyze measurement;声明式分区的最佳实践
应该谨慎地选择如何对表进行分区，因为糟糕的设计会对查询计划和执行的性能产生负面影响。
最关键的设计决策之一将是用来划分数据的一列或多列。通常，最好的选择是按在分区表上执行的查询的WHERE子句中最常见的列或列集进行分区。与分区键匹配并兼容的WHERE子句项可用于修剪不需要的分区。但是，您可能会因对PRIMARY KEY或UNIQUE约束的要求而被迫做出其他决定。在计划分区策略时，删除不需要的数据也是要考虑的因素。整个分区可以相当快地拆离，因此设计分区策略可能有益于将要立即删除的所有数据都放在单个分区中。
选择应将表划分为分区的目标数量也是一个至关重要的决定。没有足够的分区可能意味着索引仍然太大，并且数据局部性仍然很差，这可能导致较低的缓存命中率。但是，将表划分为太多分区也会导致问题。过多的分区可能意味着更长的查询计划时间和更高的内存消耗，同时查询计划和执行。在选择如何对表进行分区时，考虑将来可能发生的更改也很重要。例如，如果您选择为每个客户分配一个分区，而您目前只有少量大客户，那么考虑几年后如果您却发现自己拥有大量小客户，将产生什么影响。在这种情况下，最好选择按HASH分区并选择合理数量的分区，而不是尝试按LIST分区，并希望客户数量不会超出对数据进行分区的实际范围。
子分区对于进一步划分预期会比其他分区更大的分区很有用，尽管过度的子分区很容易导致大量分区，并可能引起与上段所述的相同问题。
在查询计划和执行过程中考虑分区的开销也很重要。查询计划程序通常能够很好地处理多达数千个分区的分区层次结构，条件是典型的查询允许查询计划程序修剪除少数分区以外的所有分区。在计划者执行分区修剪后，如果剩余更多分区，则计划时间会更长，内存消耗也会更高。对于UPDATE和DELETE命令尤其如此。担心拥有大量分区的另一个原因是，服务器的内存消耗可能会在一段时间内显着增长，尤其是在许多会话涉及大量分区的情况下。这是因为每个分区都需要将其元数据加载到与之接触的每个会话的本地内存中。
对于数据仓库类型的工作负载，与使用OLTP类型的工作负载相比，使用更多数量的分区是有意义的。通常，在数据仓库中，由于大多数处理时间是在查询执行过程中花费的，因此查询计划时间就不再那么重要了。对于这两种类型的工作负载中的任何一种，重要的是及早做出正确的决定，因为对大量数据进行重新分区可能会非常缓慢。预期工作负载的模拟通常有助于优化分区策略。永远不要假设更多的分区比更少的分区更好，反之亦然。
二、通过继承(inherit)实现表分区
尽管内置的声明性分区适用于大多数常见用例，但在某些情况下，可能会使用更灵活的方法。可以使用表继承来实现分区，它具有一些声明性分区不支持的一些功能，例如：
1 对于声明性分区，分区必须与父表具有完全相同的列集，而通过表继承，子表可能具有父级中不存在的额外列。
2 表继承允许多重继承。 （一个子表继承多个父表）
3 声明性分区仅支持范围、列表、哈希分区，而表继承允许按照用户选择的方式对数据进行拆分。 （但是请注意，如果约束排除无法有效地修剪子表，则查询性能可能会很差。）
4 与使用表继承相比，使用声明性分区时，某些操作需要更强的锁定。例如，在分区表中添加分区或从分区表中删除分区都需要对父表进行ACCESS EXCLUSIVE锁，而对于常规继承，SHARE UPDATE EXCLUSIVE锁就足够了。
例子：
1 创建要继承的父表
2 创建多个子表
CREATE TABLE measurement_y2007m11 () INHERITS (measurement);
CREATE TABLE measurement_y2007m12 () INHERITS (measurement);
CREATE TABLE measurement_y2008m01 () INHERITS (measurement);
3 向子表中添加非重叠表约束，以在每个子表中定义允许的键值。典型示例为：
CHECK ( x = 1 )
CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
CHECK ( outletID >= 100 AND outletID < 200 )
要确保约束能保证不同子表中允许的键值之间没有重叠。一个常见的错误是设置范围约束。
例如下面是错误的，因为不知道键值200属于哪个子表：
CHECK ( outletID BETWEEN 100 AND 200 )
CHECK ( outletID BETWEEN 200 AND 300 )
下面是好的例子，能够清晰的看到范围：
CREATE TABLE measurement_y2006m02 (
CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 (
CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
) INHERITS (measurement);
4 对每个子表，在键字段上创建一个索引，以及任何你想要的索引：
CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
5 我们希望应用程序能够执行 INSERT INTO measurement ...并将数据重定向到适当的子表中。
我们可以通过在主表上附加合适的触发函数来解决。
如果仅将数据添加到最新的子级，则可以使用非常简单的触发函数：
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO measurement_y2008m01 VALUES (NEW.*);
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
创建函数之后，我们创建一个触发器，该触发器调用该触发器函数：
CREATE TRIGGER insert_measurement_trigger
BEFORE INSERT ON measurement
FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();
我们必须每月重新定义触发函数，以便它始终指向当前子表。但是，触发器定义不需要更新。
我们可能要插入数据，并让服务器自动找到应在其中添加行的子表。我们可以使用更复杂的触发函数来做到这一点，例如：
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.logdate >= DATE '2006-02-01' AND
NEW.logdate < DATE '2006-03-01' ) THEN
INSERT INTO measurement_y2006m02 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
NEW.logdate < DATE '2006-04-01' ) THEN
INSERT INTO measurement_y2006m03 VALUES (NEW.*);
...
ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
NEW.logdate < DATE '2008-02-01' ) THEN
INSERT INTO measurement_y2008m01 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
触发器定义与之前的相同。请注意，每个IF测试必须与其子表的CHECK约束完全匹配。
尽管此功能比单月情况更为复杂，但由于分支可以根据需要先添加，因此无需经常更新。
将插入重定向到适当的子表的另一种方法是，在主表上设置规则，而不是触发器。例如：
CREATE RULE measurement_insert_y2006m02 AS
ON INSERT TO measurement WHERE
( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
DO INSTEAD
INSERT INTO measurement_y2006m02 VALUES (NEW.*);
...
CREATE RULE measurement_insert_y2008m01 AS
ON INSERT TO measurement WHERE
( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
DO INSTEAD
INSERT INTO measurement_y2008m01 VALUES (NEW.*);