pgsql.cc 提供对 postgresql.org 官网内容的中文翻译，由 Pigsty 团队维护。
PostgreSQL支持基本的表分区。本节介绍为什么以及如何把分区作为数据库设计的一部分来实现。
分区是指将逻辑上的一个大表拆分成较小的物理部分。分区可以带来以下好处：
在某些情况下查询性能能够显著提升，特别是当那些访问压力大的行在一个分区或者少数几个分区时。分区替代了索引的前导列，从而减小索引大小，使索引中被大量使用的部分更有可能容纳在内存中。
当查询或更新访问单个分区的很大一部分时，可以通过使用该分区的顺序扫描来提高性能，而不是使用索引，这将需要分散在整个表中的随机访问读取。
如果在分区设计中考虑到了这种使用模式，就可以通过添加或移除分区来完成批量加载和删除。ALTER TABLE比批量操作快得多。它还完全避免了批量DELETE所导致的VACUUM开销。
很少使用的数据可以被迁移到便宜且较慢的存储介质上。
通常只有表本身非常大时，这些好处才值得考虑。表从多大开始受益于分区，取决于具体应用；不过，一条经验法则是表的大小应超过数据库服务器的物理内存。
目前，PostgreSQL通过表继承来支持分区。每个分区都必须作为单个父表的子表创建。父表本身通常为空；它的存在只是为了表示整个数据集。在尝试设置分区之前，你应当先熟悉继承（见第 5.8 节）。
在PostgreSQL中可以实现以下几种分区形式：
按照某个键列或一组列定义的“范围”对表进行分区，不同分区所分配的值范围互不重叠。例如，可以按日期范围，或特定业务对象的标识符范围分区。
通过明确列出每个分区包含哪些键值来对表进行分区。
要设置一个分区表，可以按以下步骤操作：
创建所有分区都将继承的“主”表。
这个表将不包含数据。不要在这个表上定义任何检查约束，除非想让它们等同地应用到所有分区上。在这个表上定义索引或者唯一约束也没有意义。
创建若干“子”表，每个都从主表继承。通常，这些表不会在从主表继承的列集合之外增加任何列。
我们将这些子表称为分区，尽管它们在各方面都是普通的PostgreSQL表。
为各个分区表添加表约束，定义每个分区中允许的键值。
典型示例如下：
CHECK ( x = 1 ) CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' )) CHECK ( outletID >= 100 AND outletID < 200 )
确保约束保证不同分区所允许的键值互不重叠。常见错误是设置如下范围约束：
CHECK ( outletID BETWEEN 100 AND 200 ) CHECK ( outletID BETWEEN 200 AND 300 )
这是错误的，因为无法明确键值 200 属于哪个分区。
注意，范围分区和列表分区在语法上没有区别；这些术语只是描述性的。
对于每个分区，在键列上创建索引，以及其他所需的索引。（键索引不是严格必需的，但在大多数场景中是有帮助的。如果希望键值唯一，则应始终为每个分区创建唯一或主键约束。）
可以选择定义一个规则或触发器，把对主表的修改重定向到适当的分区。
确认constraint_exclusion配置参数在postgresql.conf中已启用。如果没有启用，查询将无法按预期得到优化。
例如，假设正在为一家大型冰淇淋公司构建数据库。该公司每天测量最高温度，并统计各个地区的冰淇淋销量。从概念上看，我们需要如下表：
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
我们知道，大多数查询只会访问最近一周、一个月或一个季度的数据，因为该表主要用于为管理层生成在线报表。为减少需要存储的旧数据量，决定只保留最近 3 年的数据，并在每月月初删除最早一个月的数据。
在这种情况下，可以利用分区来帮助满足测量表的各种不同需求。按照上面概述的步骤，可以这样设置分区：
主表就是measurement表，完全按上面的方式声明。
然后，为每个活动月份创建一个分区：
CREATE TABLE measurement_y2004m02 ( ) INHERITS (measurement); CREATE TABLE measurement_y2004m03 ( ) INHERITS (measurement); ... CREATE TABLE measurement_y2005m11 ( ) INHERITS (measurement); CREATE TABLE measurement_y2005m12 ( ) INHERITS (measurement); CREATE TABLE measurement_y2006m01 ( ) INHERITS (measurement);
每个分区本身都是完整的表，但它们从measurement表继承其定义。
这解决了我们的一个问题：删除旧数据。每个月，我们只需对最旧的子表执行DROP TABLE，并为新月份的数据创建一个新的子表。
我们必须提供互不重叠的表约束。与其像上面那样只创建分区表，表创建脚本其实应该是：
CREATE TABLE measurement_y2004m02 (
CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2004m03 (
CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
) INHERITS (measurement);
...
CREATE TABLE measurement_y2005m11 (
CHECK ( logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2005m12 (
CHECK ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m01 (
CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
) INHERITS (measurement);
我们可能还需要在键列上创建索引：
CREATE INDEX measurement_y2004m02_logdate ON measurement_y2004m02 (logdate); CREATE INDEX measurement_y2004m03_logdate ON measurement_y2004m03 (logdate); ... CREATE INDEX measurement_y2005m11_logdate ON measurement_y2005m11 (logdate); CREATE INDEX measurement_y2005m12_logdate ON measurement_y2005m12 (logdate); CREATE INDEX measurement_y2006m01_logdate ON measurement_y2006m01 (logdate);
我们此时选择不添加更多索引。
如果数据只会添加到最新的分区，我们可以设置一条非常简单的插入规则。我们必须每个月重新定义它，使其始终指向当前分区。
CREATE OR REPLACE RULE measurement_current_partition AS
ON INSERT TO measurement
DO INSTEAD
INSERT INTO measurement_y2004m01 VALUES ( NEW.city_id,
NEW.logdate,
NEW.peaktemp,
NEW.unitsales );
我们也可能希望插入数据时由服务器自动定位该行应添加到的分区。这可以通过下面这样一组更复杂的规则来完成。
CREATE RULE measurement_insert_y2004m02 AS
ON INSERT TO measurement WHERE
( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
DO INSTEAD
INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id,
NEW.logdate,
NEW.peaktemp,
NEW.unitsales );
...
CREATE RULE measurement_insert_y2005m12 AS
ON INSERT TO measurement WHERE
( logdate >= DATE '2005-12-01' AND logdate < DATE '2004-01-01' )
DO INSTEAD
INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id,
NEW.logdate,
NEW.peaktemp,
NEW.unitsales );
CREATE RULE measurement_insert_y2004m01 AS
ON INSERT TO measurement WHERE
( logdate >= DATE '2004-01-01' AND logdate < DATE '2004-02-01' )
DO INSTEAD
INSERT INTO measurement_y2004m01 VALUES ( NEW.city_id,
NEW.logdate,
NEW.peaktemp,
NEW.unitsales );
注意，每条规则的WHERE子句都与其分区的CHECK约束完全匹配。
如我们所见，一个复杂的分区方案可能需要大量的DDL。在上面的示例中，我们可能每个月创建一个新分区，因此编写一个脚本来自动生成所需要的DDL可能会更好。
也可以使用UNION ALL视图来安排分区：
CREATE VIEW measurement AS
SELECT * FROM measurement_y2004m02
UNION ALL SELECT * FROM measurement_y2004m03
...
UNION ALL SELECT * FROM measurement_y2005m11
UNION ALL SELECT * FROM measurement_y2005m12
UNION ALL SELECT * FROM measurement_y2004m01;
不过，重新创建视图的需要给数据集分区的添加和删除增加了一个额外步骤。
通常，最初定义表时建立的分区集合并不会保持不变。常见的需求是删除旧数据分区，并定期为新数据添加新分区。分区最重要的优势之一，恰恰在于它允许通过操纵分区结构，而不是物理地移动大量数据，来近乎瞬时地完成这种原本非常痛苦的任务。
移除旧数据最简单的选择，是直接删除不再需要的分区：
DROP TABLE measurement_y2004m02;
由于不必逐条删除每条记录，这可以非常快速地删除数百万条记录。
另一种通常更可取的选择，是将该分区从分区表中移除，但保留它作为独立表的可访问性：
ALTER TABLE measurement_y2004m02 NO INHERIT measurement;
这样在删除数据之前，还可以对其执行进一步的操作。例如，这常常是使用COPY、pg_dump或类似工具备份数据的好时机。也可能是把数据聚合成更小格式、执行其他数据处理或运行报告的好时机。
类似地，我们可以添加一个新分区来处理新数据。可以像上面创建初始分区那样，在分区表中创建一个空分区：
CREATE TABLE measurement_y2006m02 (
CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);
作为替代，有时更方便的做法是在分区结构之外创建新表，之后再把它变成正式的分区。这允许数据在出现在分区表中之前先被载入、检查和转换：
CREATE TABLE measurement_y2006m02 (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS); ALTER TABLE measurement_y2006m02 ADD CONSTRAINT y2006m02 CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ); \copy measurement_y2006m02 from 'measurement_y2006m02' -- possibly some other data preparation work ALTER TABLE measurement_y2006m02 INHERIT measurement;
约束排除是一种查询优化技术，可提高按上述方式定义的分区表的性能。例如：
SET constraint_exclusion = on; SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
如果没有约束排除，上述查询会扫描measurement表的每个分区。启用约束排除后，规划器会检查每个分区的约束，并尝试证明该分区不需要扫描，因为它不可能包含满足查询WHERE子句的行。如果规划器能够证明这一点，就会将该分区排除在查询计划之外。
可以使用EXPLAIN命令，显示启用constraint_exclusion时的计划与禁用时的计划之间的差异。对于这种表结构，典型的未优化计划如下：
SET constraint_exclusion = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
QUERY PLAN
-----------------------------------------------------------------------------------------------
Aggregate (cost=158.66..158.68 rows=1 width=0)
-> Append (cost=0.00..151.88 rows=2715 width=0)
-> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0)
Filter: (logdate >= '2008-01-01'::date)
-> Seq Scan on measurement_y2004m02 measurement (cost=0.00..30.38 rows=543 width=0)
Filter: (logdate >= '2008-01-01'::date)
-> Seq Scan on measurement_y2004m03 measurement (cost=0.00..30.38 rows=543 width=0)
Filter: (logdate >= '2008-01-01'::date)
...
-> Seq Scan on measurement_y2005m12 measurement (cost=0.00..30.38 rows=543 width=0)
Filter: (logdate >= '2008-01-01'::date)
-> Seq Scan on measurement_y2006m01 measurement (cost=0.00..30.38 rows=543 width=0)
Filter: (logdate >= '2008-01-01'::date)
某些或全部分区可能使用索引扫描，而不是全表顺序扫描，但这里的重点是，为回答该查询，根本不需要扫描较旧的分区。启用约束排除后，可以得到代价明显更低、结果却相同的计划：
SET constraint_exclusion = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
QUERY PLAN
-----------------------------------------------------------------------------------------------
Aggregate (cost=63.47..63.48 rows=1 width=0)
-> Append (cost=0.00..60.75 rows=1086 width=0)
-> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0)
Filter: (logdate >= '2008-01-01'::date)
-> Seq Scan on measurement_y2006m01 measurement (cost=0.00..30.38 rows=543 width=0)
Filter: (logdate >= '2008-01-01'::date)
注意，约束排除只由CHECK约束驱动，与是否存在索引无关。因此，不必在键列上定义索引。是否需要为某个分区创建索引，取决于预期查询扫描该分区时通常会扫描其中的大部分，还是仅扫描一小部分。后一种情况下索引会有帮助，前一种情况下则不会。
分区表有以下注意事项：
目前没有办法验证所有CHECK约束是否互斥。这需要数据库设计者自行小心。
目前还没有简单的方法指定不许把行插入到主表中。主表上的CHECK (false)约束会被所有子表继承，因此不能用于这一目的。一种可能的做法是在主表上设置一个总是引发错误的ON INSERT触发器。（或者，也可以用这样的触发器把数据重定向到适当的子表，而不是像上面建议的那样使用一组规则。）
约束排除有以下注意事项：
只有查询的WHERE子句包含常量时，约束排除才能有效果。参数化查询不会被优化，因为规划器无法知道参数值在运行时会选择哪些分区。出于同样的原因，必须避免CURRENT_DATE这类“稳定”函数。
避免在CHECK约束中使用跨数据类型的比较，因为规划器目前无法证明这类条件为假。例如，下面的约束在x是integer列时可行，但在x是bigint列时不行：
CHECK ( x = 1 )
对于bigint列，必须使用如下形式的约束：
CHECK ( x = 1::bigint )
这个问题并不限于bigint数据类型——只要常量的默认数据类型与被比较列的数据类型不匹配，就可能出现。所提供查询中的跨数据类型比较通常没有问题，只是不能出现在CHECK条件中。
约束排除期间会考虑主表所有分区上的全部约束，因此大量分区很可能显著增加查询规划时间。
别忘了你仍需要分别在各个分区上运行ANALYZE。像这样的命令：
ANALYZE measurement;
只会处理主表。
译文有误、术语不当或页面显示问题，请到译文仓库 pgsty/pgdoc 报告译文问题。 英文原文本身的问题，请在当前版本的对应页面向上游反馈；上游不再修订已结束维护的版本。