Postgresql 中文操作指南

5.11. Table Partitioning #

PostgreSQL 支持基本表分区。本节介绍作为数据库设计一部分实现分区的理由和方法。

5.11.1. Overview #

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

这些好处通常仅在表本来非常大的情况下才有用。表何时会受益于分区的确切点取决于应用程序，尽管经验法则是表的规模应超过数据库服务器的物理内存。

PostgreSQL 提供对以下分区形式的内置支持：

  • Range Partitioning #

    • 该表根据键列或列集划分为“范围”，不同分区分配的值的范围之间没有重叠。例如，可以按日期范围分区，或按特定业务对象的标识符范围分区。每个范围的界限被理解为在较低端（包括）和较高端（不包括）中。例如，如果某个分区的范围为 1 到 10 ，而下一个分区的范围为 10 到 20 ，那么值 10 属于第二个分区，而不是第一个分区。

  • List Partitioning #

    • 根据明确列出的每个分区中出现哪个键值，可以对表进行分区。

  • Hash Partitioning #

    • 通过为每个分区指定模数和余数对表进行分区。将分区键的哈希值除以指定模数后，每个分区将持有将产生指定余数的行。

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

5.11.2. Declarative Partitioning #

PostgreSQL 允许您声明一个表已划分为分区。被划分的表被称为 partitioned table。声明包含如上所述的 partitioning method，加上用作 partition key 的列或表达式的列表。

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

分区本身可定义为分区表，导致 sub-partitioning 为结果。尽管所有分区必须与其所分区的父级拥有相同的列，但分区可以拥有自身索引、约束和默认值，与其他分区不同。有关创建分区表和分区的详细信息，请参阅 CREATE TABLE 。

无法将常规表变为分区表，反之亦然。但是，将现有常规表或分区表添加为分区表分区，或从分区表中移除分区并将其转换成独立表是可行的；这可以简化和加速许多维护进程。有关 ATTACH PARTITION 和 DETACH PARTITION 子命令的详细信息，请参阅 ALTER TABLE 。

分区也可以是 foreign tables ，但需要极度小心，因为这意味着用户有责任确保外部表的内容满足分区规则。还有一些其他限制条件。有关详细信息，请参阅 CREATE FOREIGN TABLE 。

5.11.2.1. Example #

假设我们正在为一家大型冰淇淋公司构建数据库。该公司每天测量峰值温度和各个地区冰淇淋的销售情况。从概念上讲，我们希望获得以下表格：

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

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

在本例中，如果要使用声明性分区，请使用以下步骤：

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

5.11.2.2. Partition Maintenance #

通常，在最初定义表时建立的分区集合并不打算保持静态。通常，要删除保存旧数据的和定期为新数据添加新分区。分区的其中一个最重要的优点就是它允许通过操作分区结构而不是实际移动大量数据来近乎立即执行此项原本十分繁琐的任务。

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

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 将其 attached 到父索引上。一旦所有分区的索引附加到父索引后，父索引会自动被标记为有效。示例：

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. Limitations #

以下限制适用于分区表：

单独的分区使用幕后的继承链接到它们的分区表。但是，如以下所述，不可能将继承的所有通用功能与声明式分区表或其分区一起使用。值得注意的是，分区不能有除它所属分区表以外的其他父母表，并且表也不能同时从分区表和常规表中继承。这意味着分区表及其分区永远不会与常规表共享继承层次结构。

由于由分区表及其分区组成的分区层次结构仍然是继承层次结构，因此 tableoid 和继承的所有一般规则均适用，如 Section 5.10 所述，但有一些例外：

5.11.3. Partitioning Using Inheritance #

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

5.11.3.1. Example #

此示例构建了一个与上述声明式分区示例等效的分区结构。按照以下步骤操作：

Note

实际上，如果大多数插入进入该子项，则最好先检查最新的子项。为简单起见，我们已按照本示例其他部分中所示的顺序显示触发器的测试。

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

5.11.3.2. Maintenance for Inheritance Partitioning #

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

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. Caveats #

以下警告适用于使用继承实现的分区：

5.11.4. Partition Pruning #

Partition pruning 是一种查询优化技术，它可以提高声明式分区表的性能。例如：

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

如果未进行分区剪裁，上述查询将扫描 measurement 表的每个分区。启用了分区剪裁时，计划程序将检查每个分区的定义，并证明不需要扫描分区，因为它不可能包含满足查询的 WHERE 子句的任何行。当计划程序能够证明这一点时，它会将分区从查询计划中排除 (prunes)。

通过使用 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 语句中定义的参数、使用从子查询获取的值，或在嵌套循环联接的内部使用参数化的值。执行过程中的分区剪裁可以在下列任何时间执行：

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

5.11.5. Partitioning and Constraint Exclusion #

Constraint exclusion 是一种与分区剪裁类似的查询优化技术。虽然它主要用于通过旧继承方法实现的分区，但它也可用于其他目的，包括声明式分区。

除外约束的工作方式与分区剪裁极其相似，不同之处在于，它使用每张表 CHECK 约束（因此才得名），而分区剪裁使用表的表分区边界，而表分区边界仅出现在声明式分区的情况中。另一个不同之处在于，除外约束仅在计划时应用；不会尝试在执行时移除分区。

由于除外约束使用 CHECK 约束，这使得它与分区剪裁相比速度较慢，有时可以将其用作一项优势：除内部分区边界外，由于可以在声明式分区表上定义约束，除外约束可能能够从查询计划中删去其他分区。

constraint_exclusion 的默认（建议的）设置既不是 on 也不是 off，而是一个称为 partition 的中间设置，该设置只对可能在继承分区表上工作的查询应用该技术。on 设置使计划程序检查所有查询中的 CHECK 约束，即使是那些不太可能受益的简单查询也是如此。

除外约束适用于以下注意事项：

5.11.6. Best Practices for Declarative Partitioning #

应仔细选择对表分区的划分方式，因为查询计划和执行的性能可能会受到不良设计的不利影响。

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

选择应对表进行划分的目标分区数也是一个至关重要的决策。分区不够可能有意味着索引仍然太大，数据局部性也同样很差，可能导致低缓存命中率。然而，将表划分为过多分区也会导致问题。过多分区可能有意味着查询计划时间过长，并且查询计划和执行过程中的内存消耗更高，如下进一步描述。在选择对表分区的方法时，考虑将来可能发生的更改也很重要。例如，如果您选择为每个客户分配一个分区，并且目前的大客户数量较少，请考虑如果几年后发现自己拥有大量的客户会产生的影响。在这种情况下，最好选择按 HASH 分区，并选择合理数量的分区，而不是尝试按 LIST 分区并希望客户数量不会增加到超出实际数据分区能力的程度。

子分区对于进一步划分预期大于其他分区的那些分区很有用。另一个选择是将多个列与分区键一起用于范围分区。其中任何一种都容易导致过多的分区，所以建议保持克制。

在查询计划和执行过程中考虑分区开销很重要。一般来说，查询计划器能够很好地处理最多几千个分区的分区层次结构，假设典型查询允许查询计划器剪裁所有分区，但留下少量分区。计划时间过长并且在计划器执行分区剪裁后仍保留更多分区时内存消耗也会更高。需要关注分区数量的一个原因在于，服务器的内存消耗可能会随着时间的推移而大幅增长，特别是在许多会话触及大量分区时。这是因为每个分区都需要将它的元数据加载到触及它的每个会话的本地内存中。

对于数据仓库类型的工作负载而言，使用比 OLTP 类型的工作负载更多数量的分区可能是合理的。一般而言，在数据仓库中，查询计划时间的影响较小，因为大多数处理时间是在查询执行过程中花费的。对于这两种类型的任何工作负载来说，尽早做出正确决策很重要，因为重新分区大量数据可能会非常缓慢。对预期工作负载进行模拟通常有利于优化分区策略。切勿仅仅假设更多分区比更少分区更好，反之亦然。