PostgreSQL教程-数据定义-声明式划分

时间：2021-11-11 来源：

5.11.2. 声明式划分

PostgreSQL提供了一种方法指定如何把一个表划分成称为分区的片段。被划分的表被称作分区表。这种说明由分区方法以及要被用作分区键的列或者表达式列表组成。

所有被插入到分区表的行将被基于分区键的值路由到分区中。每个分区都有一个由其分区边界定义的数据子集。当前支持的分区方法是范围、列表以及哈希。

分区本身也可能被定义为分区表，这种用法被称为子分区。分区可以有自己的与其他分区不同的索引、约束以及默认值。创建分区表及分区的更多细节请见CREATE TABLE。

无法把一个常规表转换成分区表，反之亦然。不过，可以把一个包含数据的常规表或者分区表作为分区加入到另一个分区表，或者从分区表中移走一个分区并且把它变成一个独立的表。有关ATTACH PARTITION和DETACH PARTITION子命令的内容请见ALTER TABLE。

个体分区在内部以继承的方式链接到分区表，不过无法对声明式分区表或其分区使用继承的某些一般特性(下文讨论)。例如，分区不能有除其所属分区表之外的父表，一个常规表也不能从分区表继承使得后者成为其父表。这意味着分区表及其分区不会参与到与常规表的继承关系中。由于分区表及其分区组成的分区层次仍然是一种继承层次，所有第 5.10 节中所述的继承的普通规则也适用，不过有一些例外，尤其是：

分区表的CHECK约束和NOT NULL约束总是会被其所有的分区所继承。不允许在分区表上创建标记为NO INHERIT的CHECK约束。

只要分区表中不存在分区，则支持使用ONLY仅在分区表上增加或者删除约束。一旦分区存在，那样做就会导致错误，因为当分区存在时是不支持仅在分区表上增加或删除约束的。不过，分区表本身上的约束可以被增加(如果它们不出现在父表中)和删除。

由于分区表并不直接拥有任何数据，尝试在分区表上使用TRUNCATE ONLY将总是返回错误。

分区不能有在父表中不存在的列。在使用CREATE TABLE创建分区时不能指定列，在事后使用ALTER TABLE时也不能为分区增加列。只有当表的列正好匹配父表时，才能使用ALTER TABLE ... ATTACH PARTITION将它作为分区加入。

如果NOT NULL约束在父表中存在，那么就不能删除分区的列上的对应的NOT NULL约束。

分区也可以是外部表，不过它们有一些普通表没有的限制，详情请见CREATE FOREIGN TABLE。

更新行的分区键可能导致它满足另一个不同的分区的分区边界，进而被移动到那个分区中。

5.11.2.1. 例子

假定我们正在为一个大型的冰激凌公司构建数据库。该公司每天测量最高温度以及每个区域的冰激凌销售情况。概念上，我们想要一个这样的表：

CREATE TABLE measurement (

city_id int not null,

logdate date not null,

peaktemp int,

unitsales int

);

我们知道大部分查询只会访问上周的、上月的或者上季度的数据，因为这个表的主要用途是为管理层准备在线报告。为了减少需要被存放的旧数据量，我们决定只保留最近3年的数据。在每个月的开始我们将去除掉最早的那个月的数据。在这种情况下我们可以使用分区技术来帮助我们满足对measurement表的所有不同需求。

要在这种情况下使用声明式分区，可采用下面的步骤：

通过指定PARTITION BY子句把measurement表创建为分区表，该子句包括分区方法(这个例子中是RANGE)以及用作分区键的列列表。

CREATE TABLE measurement (

city_id int not null,

logdate date not null,

peaktemp int,

unitsales int

) PARTITION BY RANGE (logdate);

你可能需要决定在分区键中使用多列进行范围分区。当然，这通常会导致较大数量的分区，其中每一个个体都比较小。另一方面，使用较少的列可能会导致粗粒度的分区策略得到较少数量的分区。如果条件涉及这些列中的一部分或者全部，访问分区表的查询将不得不扫描较少的分区。例如，考虑一个使用列lastname和firstname(按照这样的顺序)作为分区键进行范围分区的表。

创建分区。每个分区的定义必须指定对应于父表的分区方法和分区键的边界。注意，如果指定的边界使得新分区的值会与已有分区中的值重叠，则会导致错误。向父表中插入无法映射到任何现有分区的数据将会导致错误，这种情况下应该手工增加一个合适的分区。

分区以普通PostgreSQL表(或者可能是外部表)的方式创建。可以为每个分区单独指定表空间和存储参数。

没有必要创建表约束来描述分区的分区边界条件。相反，只要需要引用分区约束时，分区约束会自动地隐式地从分区边界说明中生成。

CREATE TABLE measurement_y2006m02 PARTITION OF measurement

FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement

FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

...

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;

为了实现子分区，在创建分区的命令中指定PARTITION BY子句，例如：

CREATE TABLE measurement_y2006m02 PARTITION OF measurement

FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')

PARTITION BY RANGE (peaktemp);

在创建了measurement_y2006m02的分区之后，任何被插入到measurement中且被映射到measurement_y2006m02的数据(或者直接被插入到measurement_y2006m02的数据，假定它满足这个分区的分区约束)将被基于peaktemp列进一步重定向到measurement_y2006m02的一个分区。指定的分区键可以与父亲的分区键重叠，不过在指定子分区的边界时要注意它接受的数据集合是分区自身边界允许的数据集合的一个子集，系统不会尝试检查事情情况是否如此。

在分区表的键列上创建一个索引，还有其他需要的索引(键索引并不是必需的，但是大部分场景中它都能很有帮助)。这会自动在每个分区上创建一个索引，并且后来创建或者附着的任何分区也将会包含索引。

CREATE INDEX ON measurement (logdate);

确保enable_partition_pruning配置参数在postgresql.conf中没有被禁用。如果被禁用，查询将不会按照想要的方式被优化。

在上面的例子中，我们会每个月创建一个新分区，因此写一个脚本来自动生成所需的DDL会更好。

5.11.2.2. 分区维护

通常在初始定义分区表时建立的分区并非保持静态不变。移除旧分区的数据并且为新数据周期性地增加新分区的需求比比皆是。分区的最大好处之一就是可以通过操纵分区结构来近乎瞬时地执行这类让人头痛的任务，而不是物理地去除大量数据。

移除旧数据最简单的选择是删除掉不再需要的分区：

DROP TABLE measurement_y2006m02;

这可以非常快地删除数百万行记录，因为它不需要逐个删除每个记录。不过要注意上面的命令需要在父表上拿到ACCESS EXCLUSIVE锁。

另一种通常更好的选项是把分区从分区表中移除，但是保留它作为一个独立的表：

ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;

这允许在它被删除之前在其数据上执行进一步的操作。例如，这通常是一种使用COPY、pg_dump或类似工具备份数据的好时候。这也是把数据聚集成较小的格式、执行其他数据操作或者运行报表的好时机。

类似地，我们可以增加一个新分区来处理新数据。我们可以在分区表中创建一个空分区，就像上面创建的初始分区那样：

CREATE TABLE measurement_y2008m02 PARTITION OF measurement

FOR VALUES FROM ('2008-02-01') TO ('2008-03-01')

TABLESPACE fasttablespace;

另外一种选择是，有时候在分区结构之外创建新表更加方便，然后将它作为一个合适的分区。这允许先对数据进行装载、检查和转换，然后再让它们出现在分区表中：

CREATE TABLE measurement_y2008m02

(LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)

TABLESPACE fasttablespace;

ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02

CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );

\copy measurement_y2008m02 from 'measurement_y2008m02'

-? possibly some other data preparation work

ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02

FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );

在运行ATTACH PARTITION命令之前，推荐在要被挂接的表上创建一个CHECK约束来匹配期望的分区约束。 这样，系统将能够跳过扫描来验证隐式分区约束。 没有CHECK约束，将扫描表以验证分区约束，同时对该分区持有ACCESS EXCLUSIVE锁定，并在父表上持有SHARE UPDATE EXCLUSIVE锁。 在完成ATTACH PARTITION后，可能需要删除冗余CHECK约束。

如上所述，可以在分区的表上创建索引，并自动将其应用于整个层次结构。 这非常便利，因为不仅现有分区将变为索引，而且将来创建的任何分区都将变为索引。 一个限制是，在创建这样一个分区索引时，不可能同时使用CONCURRENTLY限定符。 为了克服长时间锁，可以对分区表使用CREATE INDEX ON ONLY ;这样的索引被标记为无效，并且分区不会自动应用该索引。 分区上的索引可以使用CONCURRENTLY分别的创建。 然后使用ALTER INDEX .. ATTACH PARTITIONattached到父索引。 一旦所有分区的索引附加到父索引，父索引将自动标记为有效。 例如：

CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales);

CREATE INDEX measurement_usls_200602_idx

ON measurement_y2006m02 (unitsales);

ALTER INDEX measurement_usls_idx

ATTACH PARTITION measurement_usls_200602_idx;

...

该技术也可以与UNIQUE 和PRIMARY KEY 约束一起试用; 当创建约束时隐式创建索引。例如:

ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate);

ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate);

ALTER INDEX measurement_city_id_logdate_key

ATTACH PARTITION measurement_y2006m02_city_id_logdate_key;

...

5.11.2.3. 限制

分区表有下列限制：

没有办法创建跨越所有分区的排除约束，只可能单个约束每个叶子分区。

分区表上的惟一约束必须包括所有分区键列。存在此限制是因为PostgreSQL只能每个分区中分别强制实施唯一性。

如果必要，必须在个体分区上定义BEFORE ROW触发器，分区表上不需要。

不允许在同一个分区树中混杂临时关系和持久关系。因此，如果分区表是持久的，则其分区也必须是持久的，反之亦然。在使用临时关系时，分区数的所有成员都必须来自于同一个会话。