5.11. 表分区#

5.11.1. 概述

5.11.2. 声明式分区

5.11.3. 使用继承进行分区

5.11.4. 分区修剪

5.11.5. 分区和约束排除

5.11.6. 声明式分区的最佳实践

PostgreSQL支持基本表分区。本部分将介绍在数据库设计中实现分区的理由和方法。

5.11.1. 概述#

分区是指将逻辑上一个大的表拆分为较小的物理部分。分区可以提供以下好处

• 在某些情况下，查询性能可以得到显著提升，尤其是当表中访问量最大的行位于单个分区或少量分区中时。分区实际上取代了索引的上层树，从而使索引中使用频率最高的部分更有可能放入内存中。

• 当查询或更新访问单个分区的大部分内容时，可以通过对该分区进行顺序扫描来提高性能，而不是使用索引，因为索引需要在整个表中进行随机访问读取。

• 如果分区设计中考虑了使用模式，则可以通过添加或删除分区来完成批量加载和删除。使用 DROP TABLE 删除单个分区或执行 ALTER TABLE DETACH PARTITION 的速度要比批量操作快得多。这些命令还可以完全避免批量 DELETE 导致的 VACUUM 开销。

• 不常使用的数据可以迁移到更便宜、更慢的存储介质。

通常只有当表非常大时，这些好处才值得考虑。表从分区中受益的确切点取决于应用程序，尽管经验法则是表的尺寸应该超过数据库服务器的物理内存。

PostgreSQL为以下形式的分区提供了内置支持

范围分区 #

表被划分为由键列或列集定义的 “范围”，不同分区分配的值范围之间没有重叠。例如，可以按日期范围或特定业务对象的标识符范围进行分区。每个范围的边界在较低端被理解为包含，在较高端被理解为不包含。例如，如果一个分区的范围是从 1 到 10，而下一个分区的范围是从 10 到 20，那么值 10 属于第二个分区，而不是第一个分区。

列表分区 #

表通过明确列出每个分区中出现的键值来进行分区。

哈希分区 #

表通过为每个分区指定模数和余数来进行分区。每个分区将保存分区键的哈希值除以指定模数后产生指定余数的行。

如果您的应用程序需要使用上述未列出的其他形式的分区，则可以使用继承和UNION ALL视图等替代方法。此类方法提供了灵活性，但没有内置声明式分区的一些性能优势。

5.11.2. 声明式分区#

PostgreSQL允许您声明一个表已划分为多个分区。被划分的表称为分区表。声明包括上述分区方法，以及用作分区键的列或表达式的列表。

分区表本身是一个“虚拟”表，没有自己的存储。相反，存储属于分区，这些分区是与分区表关联的其他普通表。每个分区根据其分区边界存储定义的数据子集。插入到分区表中的所有行都将根据分区键列的值路由到适当的分区之一。更新行的分区键将导致将其移动到不同的分区（如果不再满足其原始分区的边界条件）。

分区本身可以定义为分区表，从而产生子分区。尽管所有分区都必须与其分区父级具有相同的列，但分区可以具有自己的索引、约束和默认值，不同于其他分区。有关创建分区表和分区的更多详细信息，请参见CREATE TABLE。

无法将常规表变成分区表，反之亦然。但是，可以将现有的常规表或分区表添加为分区表的分区，或从分区表中删除分区，将其变成独立表；这可以简化和加速许多维护过程。请参见ALTER TABLE以了解有关ATTACH PARTITION和DETACH PARTITION子命令的更多信息。

分区也可以是外部表，尽管需要非常小心，因为用户有责任确保外部表的内容满足分区规则。还有一些其他限制。有关更多信息，请参见CREATE FOREIGN TABLE。

5.11.2.1. 示例#

假设我们正在为一家大型冰淇淋公司构建一个数据库。该公司每天测量最高温度以及每个地区冰淇淋的销量。从概念上讲，我们需要一个类似于

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

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

要在此情况下使用声明式分区，请使用以下步骤

1. 通过指定 PARTITION BY 子句（在本例中为 RANGE）和用作分区键的列列表，将 measurement 表创建为分区表，其中包括分区方法。

   CREATE TABLE measurement (
       city_id         int not null,
       logdate         date not null,
       peaktemp        int,
       unitsales       int
   ) PARTITION BY RANGE (logdate);
   

2. 创建分区。每个分区的定义都必须指定与父分区的分区方法和分区键相对应的界限。请注意，指定界限时，如果新分区的数值与一个或多个现有分区的数值重叠，将导致错误。

   这样创建的分区在各个方面都是正常的 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 列进一步重定向到其一个分区。指定的 partition key 可能会与父 partition key 重叠，尽管在指定子分区的界限时应小心，以使其接受的数据集构成分区自身界限允许的子集；系统不会尝试检查这是否属实。

   将数据插入到未映射到现有分区之一的父表中将导致错误；必须手动添加适当的分区。

   无需手动创建描述分区边界条件的表约束。此类约束将自动创建。

3. 在分区表上创建键列上的索引以及您可能需要的任何其他索引。（键索引并非严格必需，但在大多数情况下它很有用。）这会自动在每个分区上创建匹配的索引，您稍后创建或附加的任何分区也将具有此类索引。在分区表上声明的索引或唯一约束与分区表一样是“虚拟”的：实际数据位于各个分区表上的子索引中。

   CREATE INDEX ON measurement (logdate);
   

4. 确保在postgresql.conf中未禁用enable_partition_pruning配置参数。如果禁用，查询将不会按预期进行优化。

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

5.11.2.2. 分区维护#

通常，在最初定义表时建立的分区集并不打算保持静态。通常需要删除保存旧数据的分区，并定期添加新分区以保存新数据。分区最重要的优势之一恰恰在于它允许通过操作分区结构来几乎立即执行此类原本痛苦的任务，而不是在物理上移动大量数据。

删除旧数据的最简单选项是删除不再必要的那个分区

DROP TABLE measurement_y2006m02;

这可以非常快速地删除数百万条记录，因为它不必单独删除每条记录。但是请注意，上述命令需要在父表上获取ACCESS EXCLUSIVE锁。

另一种通常更可取的选项是从分区表中删除分区，但保留对它的访问权限，使其成为一个独立的表。这有两种形式

ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;
ALTER TABLE measurement DETACH PARTITION measurement_y2006m02 CONCURRENTLY;

这些允许在删除数据之前对数据执行进一步的操作。例如，这通常是使用COPY、pg_dump或类似工具备份数据的一个有用时机。它也可能是将数据聚合到较小格式、执行其他数据操作或运行报告的一个有用时机。该命令的第一种形式需要对父表进行ACCESS EXCLUSIVE锁定。在第二种形式中添加CONCURRENTLY限定符允许分离操作仅需要对父表进行SHARE UPDATE EXCLUSIVE锁定，但请参阅ALTER TABLE ... DETACH PARTITION了解有关限制的详细信息。

类似地，我们可以添加一个新分区来处理新数据。我们可以像上面创建原始分区一样在分区表中创建一个空分区

CREATE TABLE measurement_y2008m02 PARTITION OF measurement
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01')
    TABLESPACE fasttablespace;

作为一种替代方法，有时在分区结构外部创建新表并稍后将其作为分区附加会更方便。这允许在分区表中显示新数据之前加载、检查和转换新数据。此外，ATTACH PARTITION操作仅需要对分区表进行SHARE UPDATE EXCLUSIVE锁定，而不是CREATE TABLE ... PARTITION OF所需的ACCESS EXCLUSIVE锁定，因此它对分区表上的并发操作更友好。CREATE TABLE ... LIKE选项有助于避免繁琐地重复父表的定义

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锁的同时扫描表以验证分区约束。建议在ATTACH PARTITION完成后删除现在多余的CHECK约束。如果要附加的表本身是一个分区表，那么它的每个子分区将被递归锁定并扫描，直到遇到合适的CHECK约束或达到叶分区。

类似地，如果分区表具有DEFAULT分区，建议创建一个CHECK约束，该约束排除要附加的分区的约束。如果不这样做，则将扫描DEFAULT分区以验证它不包含应位于要附加的分区中的任何记录。此操作将在DEFAULT分区上持有ACCESS EXCLUSIVE锁时执行。如果DEFAULT分区本身是一个分区表，那么它的每个分区将以与上述要附加的表相同的方式递归检查。

如上所述，可以在分区表上创建索引，以便将其自动应用于整个层次结构。这非常方便，因为不仅现有分区将被索引，而且将来创建的任何分区也将被索引。一个限制是，在创建这样的分区索引时无法使用CONCURRENTLY限定符。为了避免长时间锁定，可以使用CREATE INDEX ON ONLY分区表；这样的索引被标记为无效，并且分区不会自动应用索引。分区上的索引可以使用CONCURRENTLY单独创建，然后使用ALTER INDEX .. ATTACH PARTITION将其附加到父索引。一旦所有分区的索引附加到父索引，父索引将自动标记为有效。示例

CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales);

CREATE INDEX CONCURRENTLY 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. 限制#

以下限制适用于分区表

• 要在分区表上创建唯一或主键约束，分区键不得包含任何表达式或函数调用，并且约束的列必须包含所有分区键列。此限制存在，因为构成约束的各个索引只能直接在其自己的分区内强制唯一性；因此，分区结构本身必须保证不同分区中没有重复项。

• 没有办法创建跨越整个分区表的排除约束。只能对每个叶分区单独设置这样的约束。同样，此限制源于无法强制跨分区限制。

• BEFORE ROW 触发器在 INSERT 中无法更改新行的最终目标分区。

• 不允许在同一分区树中混合临时和永久关系。因此，如果分区表是永久的，那么它的分区也必须是永久的，反之亦然。在使用临时关系时，分区树的所有成员都必须来自同一会话。

各个分区通过幕后的继承链接到它们的分区表。但是，不可能对声明式分区表或其分区使用继承的所有通用特性，如下所述。值得注意的是，分区除了它所属的分区表之外不能有任何父级，表也不能同时从分区表和常规表继承。这意味着分区表及其分区永远不会与常规表共享继承层次结构。

由于由分区表及其分区组成的分区层次结构仍然是一个继承层次结构，tableoid和所有正常的继承规则都适用，如第 5.10 节中所述，但有几个例外

• 分区不能具有父级中不存在的列。在使用 CREATE TABLE 创建分区时无法指定列，也不能在事后使用 ALTER TABLE 向分区添加列。只有当表的列与父级完全匹配时，才能使用 ALTER TABLE ... ATTACH PARTITION 将表作为分区添加。

• 分区表中的 CHECK 和 NOT NULL 约束始终会被其所有分区继承。不允许在分区表上创建标记为 NO INHERIT 的 CHECK 约束。如果父表中存在相同的约束，则不能删除分区列上的 NOT NULL 约束。

• 只要没有分区，就可以使用 ONLY 仅对分区表添加或删除约束。一旦存在分区，除了 UNIQUE 和 PRIMARY KEY 之外的任何约束，使用 ONLY 都会导致错误。相反，可以添加分区本身的约束，并且（如果它们不存在于父表中）可以删除它们。

• 由于分区表本身没有任何数据，因此尝试对分区表使用 TRUNCATE ONLY 始终会返回错误。

5.11.3. 使用继承进行分区#

虽然内置声明式分区适用于大多数常见用例，但有些情况下可能需要一种更灵活的方法。可以使用表继承来实现分区，它允许使用声明式分区不支持的几个特性，例如

• 对于声明式分区，分区必须与分区表具有完全相同的列集，而对于表继承，子表可能具有父表中不存在的额外列。

• 表继承允许多重继承。

• 声明式分区仅支持范围、列表和哈希分区，而表继承允许以用户选择的方式划分数据。（但请注意，如果约束排除无法有效地剪除子表，则查询性能可能会很差。）

5.11.3.1. 示例#

此示例构建了一个与上述声明式分区示例等效的分区结构。使用以下步骤

1. 创建 “root” 表，所有 “child” 表都将从此表继承。此表将不包含任何数据。不要在此表上定义任何检查约束，除非你打算将它们同样应用于所有子表。同样，也没有必要在此表上定义任何索引或唯一约束。对于我们的示例，根表是最初定义的 measurement 表

   CREATE TABLE measurement (
       city_id         int not null,
       logdate         date not null,
       peaktemp        int,
       unitsales       int
   );
   

2. 创建多个 “child” 表，每个表都从根表继承。通常，这些表不会向从根继承的集合中添加任何列。与声明式分区一样，这些表在各个方面都是正常的 PostgreSQL 表（或外部表）。

   CREATE TABLE measurement_y2006m02 () INHERITS (measurement);
   CREATE TABLE measurement_y2006m03 () INHERITS (measurement);
   ...
   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 )
   

   确保约束保证不同子表中允许的关键值之间没有重叠。一个常见的错误是设置类似这样的范围约束

   CHECK ( outletID BETWEEN 100 AND 200 )
   CHECK ( outletID BETWEEN 200 AND 300 )
   

   这是错误的，因为不清楚关键值 200 属于哪个子表。相反，应该按此样式定义范围

   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);
   ...
   CREATE TABLE measurement_y2007m11 (
   CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
   ) INHERITS (measurement);
   CREATE TABLE measurement_y2007m12 (
   CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
   ) INHERITS (measurement);
   CREATE TABLE measurement_y2008m01 (
   CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-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);
   CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
   CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (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.*);
   

   规则比触发器有明显更多的开销，但开销是按查询支付一次，而不是按行支付一次，因此对于批量插入情况，此方法可能是有利的。然而，在大多数情况下，触发器方法将提供更好的性能。

   请注意，COPY 忽略规则。如果您想使用 COPY 插入数据，则需要复制到正确的子表，而不是直接复制到根表中。 COPY 会触发触发器，因此如果您使用触发器方法，则可以正常使用它。

   规则方法的另一个缺点是，如果规则集不涵盖插入日期，则没有简单的方法来强制出错；数据将默默地进入根表。

6. 确保 constraint_exclusion 配置参数在 postgresql.conf 中未禁用；否则可能会不必要地访问子表。

正如我们所见，复杂的表层次结构可能需要大量的 DDL。在上述示例中，我们将每个月创建一个新的子表，因此最好编写一个脚本以自动生成所需的 DDL。

5.11.3.2. 继承分区维护#

要快速删除旧数据，只需删除不再需要的子表

DROP TABLE measurement_y2006m02;

要从继承层次结构表中删除子表，但保留对它的访问权限，使其成为一个独立的表

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

要添加一个新的子表来处理新数据，请创建一个空子表，就像上面创建原始子表一样

CREATE TABLE measurement_y2008m02 (
    CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
) INHERITS (measurement);

或者，您可能希望在将新子表添加到表层次结构之前创建并填充它。这允许在对父表上的查询可见之前加载、检查和转换数据。

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
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_y2008m02 INHERIT measurement;

5.11.3.3. 注意事项#

以下注意事项适用于使用继承实现的分区

• 没有自动方法来验证所有 CHECK 约束是否互斥。创建生成子表并创建和/或修改关联对象，而不是手动编写每个对象，会更安全。

• 索引和外键约束适用于单个表，而不适用于它们的继承子项，因此它们有一些 注意事项 需要注意。

• 此处显示的方案假定行的键列的值永远不会更改，或者至少不会更改到需要它移动到另一个分区的地步。尝试执行此操作的 UPDATE 将因 CHECK 约束而失败。如果您需要处理此类情况，可以在子表上放置合适的更新触发器，但这会使结构管理变得更加复杂。

• 如果您使用手动 VACUUM 或 ANALYZE 命令，请不要忘记您需要在每个子表上单独运行它们。类似以下命令

  ANALYZE measurement;
  

  将只处理根表。

• 带有 ON CONFLICT 子句的 INSERT 语句不太可能按预期工作，因为 ON CONFLICT 操作仅在对指定目标关系（而不是其子关系）进行唯一性违规时才执行。

• 需要触发器或规则将行路由到所需的子表，除非应用程序明确知道分区方案。触发器可能很难编写，并且会比声明性分区内部执行的元组路由慢得多。

5.11.4. 分区剪枝#

分区剪枝是一种查询优化技术，可提高声明性分区表的性能。例如

SET enable_partition_pruning = on;                 -- the default
SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

如果没有分区剪枝，上述查询将扫描measurement表的每个分区。启用分区剪枝后，规划器将检查每个分区的定义并证明不需要扫描该分区，因为它不会包含满足查询WHERE子句的任何行。当规划器能够证明这一点时，它会将分区从查询计划中排除（剪枝）。

通过使用 EXPLAIN 命令和enable_partition_pruning配置参数，可以显示已剪枝分区和未剪枝分区的计划之间的差异。此类表设置的典型未优化计划为

SET enable_partition_pruning = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-------------------------------------------------------------------​----------------
 Aggregate  (cost=188.76..188.77 rows=1 width=8)
   ->  Append  (cost=0.00..181.05 rows=3085 width=0)
         ->  Seq Scan on measurement_y2006m02  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m03  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
...
         ->  Seq Scan on measurement_y2007m11  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2007m12  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)

部分或所有分区可能使用索引扫描而不是全表顺序扫描，但重点在于根本不需要扫描较旧的分区来回答此查询。当我们启用分区剪枝时，我们会获得一个更便宜的计划，它将提供相同的答案

SET enable_partition_pruning = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-------------------------------------------------------------------​----------------
 Aggregate  (cost=37.75..37.76 rows=1 width=8)
   ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
         Filter: (logdate >= '2008-01-01'::date)

请注意，分区剪枝仅由分区键隐式定义的约束驱动，而不是由索引的存在驱动。因此，不必在键列上定义索引。是否需要为给定分区创建索引取决于你是否期望扫描该分区的查询通常会扫描分区的大部分还是一小部分。在后一种情况下，索引将有帮助，但在前一种情况下则不然。

分区剪枝不仅可以在给定查询的计划过程中执行，还可以在其执行过程中执行。这很有用，因为它允许在子句包含在查询计划时值未知的表达式的值时剪枝更多分区，例如，在PREPARE语句中定义的参数、使用从子查询获取的值，或在嵌套循环联接的内侧使用参数化值。执行期间的分区剪枝可以在以下任何时间执行

• 在查询计划初始化期间。可以在此为在执行的初始化阶段已知参数值执行分区剪枝。在此阶段剪枝的分区不会显示在查询的 EXPLAIN 或 EXPLAIN ANALYZE 中。可以通过观察 EXPLAIN 输出中的 “已删除的子计划” 属性来确定在此阶段删除的分区数。

• 在查询计划的实际执行期间。分区剪裁也可能在此执行，以使用仅在实际查询执行期间才已知的那些值来移除分区。这包括来自子查询的值和来自执行时间参数的值，例如来自参数化嵌套循环联接的值。由于这些参数的值在查询执行期间可能会多次更改，因此每当分区剪裁所使用的执行参数之一更改时，就会执行分区剪裁。确定在此阶段是否剪裁了分区需要仔细检查 loops 属性在 EXPLAIN ANALYZE 输出中。对应于不同分区的子计划可能具有不同的值，具体取决于在执行期间剪裁了它们多少次。如果每次都剪裁它们，则某些可能会显示为 (never executed)。

可以使用enable_partition_pruning设置禁用分区剪裁。

5.11.5. 分区和约束排除#

约束排除是一种类似于分区剪裁的查询优化技术。虽然它主要用于使用旧版继承方法实现的分区，但它可用于其他目的，包括与声明性分区一起使用。

约束排除的工作方式与分区剪裁非常相似，不同之处在于它使用每个表的CHECK约束——这给了它它的名称——而分区剪裁使用表的 partition 边界，而 partition 边界仅在声明性分区的情况下存在。另一个区别是约束排除仅在计划时间应用；不会尝试在执行时间移除分区。

约束排除使用CHECK约束这一事实使其与分区剪裁相比速度较慢，有时可被用作优势：因为除了内部分区边界外，甚至在声明性分区表上也可以定义约束，因此约束排除可能能够从查询计划中删除其他分区。

constraint_exclusion的默认（推荐）设置既不是on，也不是off，而是一种称为partition的中间设置，它导致该技术仅应用于可能处理继承分区表的查询。on设置会导致规划器检查所有查询中的CHECK约束，即使是那些不太可能受益的简单查询。

以下警告适用于约束排除

• 约束排除仅在查询规划期间应用，与分区剪裁不同，分区剪裁还可以在查询执行期间应用。

• 约束排除仅在查询的 WHERE 子句包含常量（或外部提供的参数）时才起作用。例如，无法优化与非不可变函数（例如 CURRENT_TIMESTAMP）的比较，因为规划器无法知道该函数的值在运行时可能属于哪个子表。

• 保持分区约束简单，否则规划器可能无法证明不需要访问子表。对列表分区使用简单的相等条件，或对范围分区使用简单的范围测试，如前面的示例所示。一个好的经验法则是，分区约束应仅包含使用 B 树可索引运算符将分区列与常量进行比较，因为分区键中仅允许 B 树可索引列。

• 在约束排除期间检查父表的全部子表上的所有约束，因此大量的子表可能会大大增加查询规划时间。因此，基于旧继承的分区将最多适用于大约一百个子表；不要尝试使用数千个子表。

5.11.6. 声明式分区的最佳实践#

应仔细选择如何对表进行分区，因为设计不当会对查询规划和执行的性能产生负面影响。

最关键的设计决策之一将是用于划分数据的列或列集。通常，最佳选择是按在对已分区表执行的查询的WHERE子句中最常出现的列或列集进行分区。与分区边界约束兼容的WHERE子句可用于剪除不需要的分区。但是，您可能被迫根据PRIMARY KEY或UNIQUE约束的要求做出其他决策。在规划分区策略时，删除不需要的数据也是需要考虑的一个因素。可以相当快地分离整个分区，因此最好以这样的方式设计分区策略，即所有要一次删除的数据都位于单个分区中。

选择将表划分为多少个目标分区也是一个关键决策。分区不足可能意味着索引仍然太大，并且数据局部性仍然很差，这可能导致缓存命中率低。但是，将表划分为太多分区也可能导致问题。分区过多可能意味着查询规划时间更长，并且在查询规划和执行期间内存消耗更高，如下所述。在选择如何对表进行分区时，还必须考虑将来可能发生的更改。例如，如果您选择为每个客户分配一个分区，并且您目前只有少数大客户，请考虑如果几年后您拥有大量小客户会有什么影响。在这种情况下，最好选择按HASH进行分区并选择合理数量的分区，而不是尝试按LIST进行分区并希望客户数量不会增加到无法按其对数据进行分区的地步。

子分区对于进一步划分预计将比其他分区更大的分区很有用。另一个选择是在分区键中使用多列进行范围分区。其中任何一个都可能很容易导致分区数量过多，因此建议克制。

在查询规划和执行期间考虑分区开销非常重要。查询规划器通常能够很好地处理最多包含几千个分区的分区层次结构，前提是典型查询允许查询规划器剪除除少量分区之外的所有分区。在规划器执行分区剪除后，如果仍有更多分区，则规划时间会变长，内存消耗也会变高。担心拥有大量分区的原因之一是，服务器的内存消耗可能会随着时间的推移而显着增加，特别是如果许多会话触及大量分区时。这是因为每个分区都需要将其元数据加载到触及它的每个会话的本地内存中。

对于数据仓库类型的工作负载，使用比OLTP类型的工作负载更多的分区可能更有意义。通常，在数据仓库中，查询规划时间不是主要问题，因为大部分处理时间都花在查询执行上。对于这两种类型的工作负载，尽早做出正确的决策非常重要，因为重新分区大量数据可能会非常慢。模拟预期工作负载通常有助于优化分区策略。千万不要仅仅假设分区越多越好，反之亦然。