5.10. 表分区

PostgreSQL 支持基本的表分区。本节描述了为什么以及如何在数据库设计中实现分区。

5.10.1. Overview

分区是指将一张大表从逻辑上拆分为较小的物理块。分区可以带来以下好处：

  • 在某些情况下，尤其是当表中大多数被频繁访问的行位于单个分区或少量分区中时，查询性能可以得到显着提高。分区替代了索引的前几列，从而减小了索引的大小，并使索引中频繁使用的部分更有可能装入内存。

  • 当查询或更新访问单个分区的很大一部分时，可以通过对该分区进行 Sequences 扫描而不是使用索引和分散在整个表中的随机访问读取来提高性能。

  • 如果计划将这种需求计划在分区设计中，则可以通过添加或删除分区来完成批量加载和删除。使用ALTER TABLE DETACH PARTITION或使用DROP TABLE删除单个分区比批量操作要快得多。这些命令还完全避免了由于DELETE造成的VACUUM开销。

  • 很少使用的数据可以迁移到更便宜，更慢的存储介质上。

通常只有在表很大的情况下，这些好处才是值得的。表可以从分区中受益的确切时间取决于应用程序，尽管经验法则是表的大小应超过数据库服务器的物理内存。

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

  • Range Partitioning

    • 该表被划分为由键列或列集定义的“范围”，分配给不同分区的值的范围之间没有重叠。例如，可以按日期范围或特定业务对象的标识符范围进行分区。

  • List Partitioning

    • 通过显式列出哪些键值出现在每个分区中来对表进行分区。

  • Hash Partitioning

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

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

5 .10.2. 声明式分区

PostgreSQL 提供了一种指定如何将表划分为多个部分的方法。被划分的表称为分区表。规范包括分区方法和用作分区键的列或表达式列表。

插入分区表中的所有行将根据分区键的值被路由到* partitions 之一。每个分区都有一个由其 partition bounds *定义的数据子集。当前支持的分区方法是范围，列表和哈希。

可以使用所谓的* sub-partitioning *将分区本身定义为分区表。分区可能具有自己的索引，约束和默认值，与其他分区的索引，约束和默认值不同。有关创建分区表和分区的更多详细信息，请参见CREATE TABLE。

无法将常规表转换为分区表，反之亦然。但是，可以将包含数据的常规表或分区表添加为分区表的分区，也可以从分区表中删除分区，从而将其转变为独立表；请参阅ALTER TABLE，以了解有关ATTACH PARTITION和DETACH PARTITION子命令的更多信息。

各个分区通过后台继承链接到分区表。但是，无法对声明性分区的表或其分区使用继承的某些通用功能(如下所述)。例如，一个分区除了作为分区的分区表之外，不能有其他任何父级，常规表也不能从使其成为其父级的分区表继承。这意味着分区表及其分区不参与常规表的继承。由于由分区表及其分区组成的分区层次结构仍然是继承层次结构，因此所有正常的继承规则都按照Section 5.9中的描述适用，但有一些 exception，最值得注意的是：

  • 分区表的CHECK和NOT NULL约束始终由其所有分区继承。标记为NO INHERIT的CHECK约束不允许在分区表上创建。

  • 只要没有分区，就支持使用ONLY仅在分区表上添加或删除约束。一旦存在分区，使用ONLY将导致错误，因为不支持存在分区时仅在分区表上添加或删除约束。相反，可以添加对分区本身的约束，并且可以删除(如果它们不在父表中)。

  • 由于分区表没有直接的任何数据，因此尝试在分区表上使用TRUNCATE ONLY总是会返回错误。

  • 分区不能具有父级中不存在的列。使用CREATE TABLE创建分区时无法指定列，也不能使用ALTER TABLE在事后添加分区中的列。仅当表的列与父级(包括任何oid列)完全匹配时，才可以将它们添加为带有ALTER TABLE ... ATTACH PARTITION的分区。

  • 如果父表中存在NOT NULL约束，则不能将其删除。

分区也可以是外部表，尽管它们具有普通表所没有的一些限制。有关更多信息，请参见创建外表。

更新行的分区键可能会导致将其移到该行满足分区范围的其他分区中。

5.10.2.1. Example

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

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

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

要在这种情况下使用声明性分区，请执行以下步骤：

  • 通过指定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(按此 Sequences)分区的表范围作为分区键。

  • 创建分区。每个分区的定义必须指定与父级的分区方法和分区键相对应的界限。请注意，指定范围以使新分区的值与一个或多个现有分区中的值重叠将导致错误。将数据插入未 Map 到现有分区之一的父表将导致错误；必须手动添加适当的分区。

这样创建的分区在所有方面都是普通的 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的分区后，Map 到measurement_y2006m02的任何插入measurement的数据(或满足其分区约束的情况下直接插入measurement_y2006m02的数据)将根据peaktemp列进一步重定向到其分区之一。指定的分区键可能与父级的分区键重叠，尽管在指定子分区的边界时应格外小心，以使其接受的数据集构成该分区自身边界所允许的子集；系统不会尝试检查是否确实如此。

  • 在分区表上的键列以及您可能需要的任何其他索引上创建索引。 (并非严格要求键索引，但在大多数情况下它很有用.)这将在每个分区上自动创建一个索引，以后创建或附加的任何分区也将包含该索引。
CREATE INDEX ON measurement (logdate);
  • 确保postgresql.conf中未禁用enable_partition_pruning配置参数。如果是这样，查询将不会根据需要进行优化。

在上面的示例中，我们每个月都会创建一个新分区，因此编写一个脚本自动生成所需的 DDL 可能是明智的。

5 .10.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锁。可能需要在ATTACH PARTITION完成后删除冗余CHECK约束。

如上所述，可以在分区表上创建索引，并将它们自动应用于整个层次结构。这非常方便，因为不仅现有分区将被索引，而且将来创建的任何分区也将被索引。一个局限性是创建此类分区索引时无法使用CONCURRENTLY限定词。为了克服较长的锁定时间，可以使用CREATE INDEX ON ONLY分区表；这样的索引被标记为无效，并且分区不会自动应用该索引。可以使用CONCURRENTLY分别创建分区上的索引，然后使用ALTER INDEX .. ATTACH PARTITION将*附加到父索引上。将所有分区的索引附加到父索引后，父索引将自动标记为有效。例：

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

以下限制适用于分区表：

  • 无法创建跨所有分区的排除约束。只能单独约束每个叶分区。

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

  • 虽然分区表支持主键，但不支持引用分区表的外键。 (支持从分区表到其他表的外键引用.)

  • BEFORE ROW触发器(如有必要)必须在单个分区(而不是分区表)上定义。

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

5 .10.3. 使用继承的实现

尽管内置的声明性分区适用于大多数常见用例，但在某些情况下，可能会使用更灵活的方法。可以使用表继承来实现分区，这允许声明性分区不支持的一些功能，例如：

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

  • 表继承允许多重继承。

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

  • 与使用表继承相比，使用声明性分区时，某些操作需要更强的锁定。例如，在分区表中添加分区或从分区表中删除分区都需要对父表进行ACCESS EXCLUSIVE锁定，而对于常规继承，使用SHARE UPDATE EXCLUSIVE锁定就足够了。

5.10.3.1. Example

我们使用上面未分区的measurement表。要使用继承实现分区，请使用以下步骤：

  • 创建一个“ master”表，所有“ child”表将从中继承。该表将不包含任何数据。不要在此表上定义任何检查约束，除非您打算将它们平等地应用于所有子表。定义任何索引或唯一约束都没有意义。对于我们的示例，主表是最初定义的measurement表。

  • 创建几个“子”表，每个子表都从主表继承。通常，这些表不会在从主服务器继承的集合中添加任何列。就像声明性分区一样，这些表在任何方面都是普通的 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);
  • 向子表中添加非重叠表约束，以在每个子表中定义允许的键值。

典型示例为：

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);
  • 对于每个子表，在键列上以及您可能需要的任何其他索引上创建一个索引。
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);
  • 我们希望我们的应用程序能够说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约束完全匹配。

尽管此功能比单月情况更为复杂，但由于需要先添加分支机构，因此无需经常更新。

Note

实际上，最好是先检查最新的孩子，如果大多数插入物都进入了那个孩子。为简单起见，我们以与本示例其他部分相同的 Sequences 显示了触发器的测试。

将插入重定向到适当的子表的另一种方法是在主表上设置规则，而不是触发器。例如：

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会触发触发器，因此如果使用触发器方法，则可以正常使用它。

规则方法的另一个缺点是，如果规则集不能涵盖插入日期，则没有简单的方法来强制执行错误。数据将以静默方式进入主表。

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

如我们所见，复杂的表层次结构可能需要大量的 DDL。在上面的示例中，我们每个月都会创建一个新的子表，因此编写一个脚本自动生成所需的 DDL 可能是明智的。

5 .10.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.10.3.3. Caveats

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

  • 没有自动的方法来验证所有CHECK约束是否互斥。与手动编写每个对象相比，创建用于生成子表以及创建和/或修改关联对象的代码更安全。

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

  • 如果您使用的是手动VACUUM或ANALYZE命令，请不要忘记需要分别在每个子表上运行它们。像这样的命令：

ANALYZE measurement;

将只处理主表。

  • 带有ON CONFLICT子句的INSERT语句不太可能按预期工作，因为ON CONFLICT操作仅在指定目标关系(而不是其子关系)发生唯一违规的情况下执行。

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

5 .10.4. 分区修剪

分区修剪是一种查询优化技术，可提高声明式分区表的性能。举个例子：

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)

部分或全部分区可能使用索引扫描而不是全表 Sequences 扫描，但是这里的要点是根本不需要扫描旧的分区来回答此查询。启用分区修剪后，我们将获得便宜得多的计划，该计划将提供相同的答案：

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)
   ->  Append  (cost=0.00..36.21 rows=617 width=0)
         ->  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输出中的“ Subplans Removed”属性，可以确定在此阶段中删除的分区的数量。

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

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

Note

当前，执行时分区修剪仅在Append节点类型上发生，而不在MergeAppend或ModifyTable节点上发生。在将来的 PostgreSQL 版本中，可能会改变这种情况。

5 .10.5. 分区和约束排除

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

约束排除的工作方式与分区修剪非常相似，不同之处在于它使用每个表的CHECK约束(并为其命名)，而分区修剪使用表的分区边界，仅在声明性分区的情况下才存在。另一个区别是约束排除仅适用于计划时间。在执行时没有尝试删除分区。

约束排除使用CHECK约束的事实(这使其比分区修剪慢)有时可以用作优点：因为即使在声明性分区的表上也可以定义约束，所以除了其内部分区边界外，约束排除也可以能够从查询计划中删除其他分区。

constraint_exclusion的默认(推荐设置)既不是on也不是off，而是称为partition的中间设置，这导致该技术仅应用于可能在继承分区表上运行的查询。 on设置使计划人员在所有查询中检查CHECK约束，即使是不太可能受益的简单查询。

以下警告适用于约束排除：

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

  • 仅当查询的WHERE子句包含常量(或外部提供的参数)时，约束排除才有效。例如，无法优化与诸如CURRENT_TIMESTAMP之类的不可修改函数的比较，因为计划者无法在运行时知道该函数的值可能属于哪个子表。

  • 保持分区约束简单，否则计划者可能无法证明可能不需要访问子表。如上例所示，对列表分区使用简单的相等条件，对范围分区使用简单的范围测试。一个好的经验法则是，分区约束应只包含使用 B-tree-indexable 运算符将分区列与常量的比较，因为在分区键中仅允许 B-tree-indexable 列。

  • 在约束排除期间会检查父表的所有子约束上的所有约束，因此大量子约束可能会大大增加查询计划时间。因此，基于遗留继承的分区可以很好地与多达一百个子表一起使用。不要试图使用成千上万的孩子。

5 .10.6. 声明式分区最佳实践

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

最关键的设计决策之一将是您用来划分数据的一个或多个列。通常，最好的选择是按最常出现在对分区表执行的查询的WHERE子句中的列或列集进行分区。与分区键匹配并兼容的WHERE子句项可用于修剪不需要的分区。但是，您可能会因PRIMARY KEY或UNIQUE约束的要求而被迫做出其他决定。在计划分区策略时，删除不需要的数据也是要考虑的因素。整个分区可以相当快地分离，因此设计分区策略可能会有益，因为所有要立即删除的数据都位于单个分区中。

选择应将表划分为分区的目标数量也是一个至关重要的决定。没有足够的分区可能意味着索引仍然太大，数据局部性仍然很差，这可能导致低的缓存命中率。但是，将表分成太多分区也会引起问题。过多的分区可能意味着更长的查询计划时间和更高的内存使用量，在查询计划和执行期间。在选择如何对表进行分区时，考虑将来可能发生的更改也很重要。例如，如果您选择为每个 Client 分配一个分区，而您目前只有少量大 Client，那么考虑几年后如果您却发现自己拥有大量小 Client，将产生什么影响。在这种情况下，最好选择按HASH进行分区，然后选择合理数量的分区，而不是尝试按LIST进行分区，并希望 Client 数量不会超出对数据进行分区的实际范围。

子分区对于进一步划分预期会比其他分区更大的分区很有用，尽管过多的子分区很容易导致大量分区，并可能导致上一节中提到的相同问题。

在查询计划和执行过程中考虑分区的开销也很重要。查询计划程序通常能够很好地处理多达几百个分区的分区层次结构，前提是典型的查询允许查询计划程序修剪除少数分区以外的所有分区。随着添加更多分区，计划时间变得更长，内存消耗也越来越高。对于UPDATE和DELETE命令尤其如此。担心拥有大量分区的另一个原因是，服务器的内存消耗可能会在一段时间内显着增长，尤其是在许多会话接触大量分区的情况下。这是因为每个分区都需要将其元数据加载到与之接触的每个会话的本地内存中。

对于数据仓库类型的工作负载，与使用 OLTP 类型的工作负载相比，使用更多数量的分区是有意义的。通常，在数据仓库中，由于大多数处理时间是在查询执行过程中花费的，因此查询计划时间就不再那么重要了。对于这两种类型的工作负载中的任何一种，重要的是及早做出正确的决定，因为对大量数据进行重新分区可能会非常缓慢。预期工作负载的模拟通常有助于优化分区策略。永远不要认为更多的分区比更少的分区更好，反之亦然。

Name PostgreSQL 中文文档
Version
Language
Badge
Last Updated 2021-07-07T13:04:41