# 5.11.表分区

5.11.1. 概述

5.11.2. 声明性分区

5.11.3. 使用继承进行分区

5.11.4. 分区剪枝

5.11.5. 划分和约束排除

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

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

# 5.11.1.概述

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

  • 在某些情况下,查询性能可以显著提高,尤其是当表中大部分被频繁访问的行位于单个分区或少量分区中时。分区有效地替代了索引的上层树级别,使得索引中使用频繁的部分更有可能适合内存。

  • 当查询或更新访问单个分区的很大一部分时,可以通过使用该分区的顺序扫描来提高性能,而不是使用索引,这将需要分散在整个表中的随机访问读取。

  • 如果分区设计中考虑了使用模式,则可以通过添加或删除分区来完成批量加载和删除。使用删除单个分区升降台,或做什么ALTER TABLE DETACH分区,比批量操作快得多。这些命令也完全避免了真空由一个大体积的物体引起的开销删去.

  • 很少使用的数据可以迁移到更便宜、速度更慢的存储介质中。

    这些好处通常只有在桌子非常大的情况下才有价值。表从分区中受益的确切点取决于应用程序,尽管经验法则是表的大小应该超过数据库服务器的物理内存。

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

范围分区

表被划分为一个键列或一组列定义的“范围”,分配给不同分区的值范围之间没有重叠。例如,可以按日期范围或特定业务对象的标识符范围进行分区。每个范围的边界都被理解为在低端包含,在高端独占。例如,如果一个分区的范围是1.10,下一个的范围是1020,然后是价值10属于第二个分区,而不是第一个分区。

列表分区

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

散列分区

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

如果您的应用程序需要使用上面没有列出的其他形式的分区,可以使用继承和联合所有可以使用视图。这种方法提供了灵活性,但没有内置声明性分区的一些性能优势。

# 5.11.2.声明性分区

PostgreSQL允许您声明一个表被划分为多个分区。划分的表格称为a分区表。声明包括划分方法如上所述,再加上要用作分区密钥.

分区表本身是一个“虚拟”表,没有自己的存储空间。相反,存储属于分区,它们是与分区表关联的普通表。每个分区存储由其分区定义的数据子集划分界限。插入分区表的所有行将根据分区键列的值路由到相应的分区之一。如果一行的分区键不再满足其原始分区的分区边界,则更新该行的分区键将导致该行移动到另一个分区。

分区本身可能被定义为分区表,从而导致子分区。尽管所有分区都必须具有与其分区父分区相同的列,但分区可能有自己的索引、约束和默认值,与其他分区的索引、约束和默认值不同。看见创建表格有关创建分区表和分区的详细信息。

将常规表转换为分区表是不可能的,反之亦然。但是,可以将现有的常规表或分区表添加为分区表的分区,或者从分区表中删除分区,将其转换为独立表;这可以简化和加快许多维护过程。看见改变桌子了解更多关于附加分区分离分区子命令。

分区也可以是外国餐桌,尽管需要相当谨慎,因为外部表的内容满足分区规则是用户的责任。还有其他一些限制。看见创建外部表了解更多信息。

# 5.11.2.1.范例

假设我们正在为一家大型冰淇淋公司构建一个数据库。该公司每天测量峰值温度,以及每个地区的冰淇淋销量。从概念上讲,我们想要一个如下表:

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

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

要在这种情况下使用声明性分区,请使用以下步骤:

  1. 创建测量通过指定分割子句,其中包括分区方法(范围(在本例中)和要用作分区键的列的列表。

    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;
    

    (请记住,相邻分区可以共享一个界限值,因为范围上限被视为独占界限。)

    如果希望实现子分区,请再次指定分割用于创建单个分区的命令中的子句,例如:

    CREATE TABLE measurement_y2006m02 PARTITION OF measurement
        FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
        PARTITION BY RANGE (peaktemp);
    

    在创建测量_y2006m02,任何插入到测量映射到测量_y2006m02(或直接插入到测量_y2006m02,如果它的分区约束得到满足,它将被进一步重定向到它的一个分区高峰期柱指定的分区键可能与父分区键重叠,但在指定子分区的边界时应小心,以使其接受的数据集构成分区自身边界允许的子集;系统不会试图检查是否真的是这样。

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

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

  3. 在分区表的键列上创建索引,以及可能需要的任何其他索引。(严格来说,关键索引不是必需的,但在大多数情况下,它是有用的。)这会自动在每个分区上创建一个匹配的索引,以后创建或附加的任何分区也会有这样的索引。在分区表上声明的索引或唯一约束是“虚拟”的,其方式与分区表相同:实际数据位于各个分区表的子索引中。

    CREATE INDEX ON measurement (logdate);
    
  4. 确保使可能_隔断_修剪未在中禁用配置参数postgresql。形态。如果是,查询将不会按预期进行优化。

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

# 5.11.2.2.分区维护

通常,最初定义表时建立的一组分区并不打算保持静态。通常希望删除保存旧数据的分区,并定期为新数据添加新分区。分区最重要的优点之一就是,它允许通过操纵分区结构几乎即时地执行这项本来很痛苦的任务,而不是在物理上移动大量数据。

删除旧数据的最简单方法是删除不再需要的分区:

DROP TABLE measurement_y2006m02;

这可以非常快速地删除数百万条记录,因为它不必逐个删除每条记录。但是请注意,上面的命令需要访问独占锁定父表。

另一个通常更可取的选择是从分区表中删除分区,但保留对其本身作为表的访问权。这有两种形式:

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

这些允许在数据被删除之前对其执行进一步的操作。例如,这通常是使用复制,pg_转储或类似工具。这可能也是将数据聚合为较小格式、执行其他数据操作或运行报告的有用时间。命令的第一种形式需要访问独占锁定父表。添加同时第二种形式的限定符只允许分离操作需要共享更新独占锁定父表,但请参见改变桌子。。。分离分区有关限制的详细信息。

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

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' );

这个附加分区指挥需要采取行动共享更新独占锁在分区的桌子上。

在运行附加分区命令,建议创建一个检查要附加的表上与预期分区约束匹配的约束,如上图所示。这样,系统将能够跳过验证隐式分区约束所需的扫描。没有检查约束时,将扫描表以验证分区约束,同时保持访问独占锁上那个隔板。建议删除现在多余的检查后约束附加分区完成了。如果要附加的表本身是一个分区表,那么它的每个子分区都将被递归地锁定和扫描,直到出现合适的检查遇到约束或达到叶分区。

类似地,如果分区表具有违约分区,建议创建一个检查排除待附着分区约束的约束。如果不这样做,那么违约将对分区进行扫描,以验证它不包含应位于所附加分区中的记录。此操作将在保持访问独占把门锁上违约隔断如果违约分区本身就是一个分区表,那么它的每个分区都将以与所附加的表相同的方式进行递归检查,如上所述。

如上所述,可以在分区表上创建索引,以便将它们自动应用于整个层次结构。这非常方便,因为不仅现有分区会被索引,而且将来创建的任何分区都会被索引。一个限制是不可能使用同时在创建这样的分区索引时使用限定符。为了避免长时间锁定,可以使用仅在上创建索引分隔的桌子;这样的索引被标记为无效,分区不会自动应用索引。分区上的索引可以使用同时,然后附属的使用改变索引。。附加分区。所有分区的索引附加到父索引后,父索引将自动标记为有效。例子:

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;
...

这种技术可以用于唯一的主键也有限制;在创建约束时隐式创建索引。例子:

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.局限性

以下限制适用于分区表:

  • 分区表上的唯一约束(以及主键)必须包括所有分区键列。这种限制的存在是因为构成约束的单个索引只能在其自己的分区内直接强制唯一性;因此,分区结构本身必须保证不同分区中没有重复项。

  • 无法创建跨越整个分区表的排除约束。只能对每个叶分区单独设置这样的约束。同样,这种限制源于无法实施跨分区限制。

  • 划船前触发插入无法更改新行的最终目标分区。

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

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

    由于由分区表及其分区组成的分区层次结构仍然是继承层次结构,片状所有正常的继承规则都适用,如中所述第5.10节,除少数例外:

  • 分区不能有父分区中不存在的列。使用创建分区时,无法指定列创建表格,也不可能在使用改变桌子。表可以添加为具有改变桌子。。。附加分区仅当其列与父列完全匹配时。

  • 二者都检查非空分区表的约束总是由其所有分区继承。检查标记的约束没有继承权不允许在分区表上创建。你不能丢下一个非空如果父表中存在相同的约束,则分区列上的约束。

  • 使用只有只要不存在分区,就只支持在分区表上添加或删除约束。一旦存在分区,使用只有将导致错误。相反,可以添加和删除分区本身的约束(如果它们不在父表中)。

  • 由于分区表本身没有任何数据,因此截断 只有在分区表上,将始终返回一个错误。

# 5.11.3.使用继承进行分区

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

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

  • 表继承允许多重继承。

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

# 5.11.3.1.范例

这个示例构建了一个与上面的声明性分区示例等效的分区结构。使用以下步骤:

  1. 创建“根”表,所有“子”表都将从中继承。此表将不包含任何数据。不要在此表上定义任何检查约束,除非您希望将它们平等地应用于所有子表。对其定义任何索引或唯一约束也没有意义。对于我们的示例,根表是测量最初定义的表格:

    CREATE TABLE measurement (
        city_id         int not null,
        logdate         date not null,
        peaktemp        int,
        unitsales       int
    );
    
  2. 创建几个“子”表,每个表都从根表继承。通常,这些表不会向从根继承的集合中添加任何列。与声明性分区一样,这些表在各个方面都是普通的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. 我们希望我们的应用程序能够插入测量。。。并将数据重定向到相应的子表中。我们可以通过在根表上附加一个合适的触发器函数来安排。如果数据只添加到最新的子级,我们可以使用一个非常简单的触发函数:

    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;
    

    触发器定义与以前相同。请注意,每个如果测试必须完全匹配检查子表的约束。

    虽然这个函数比一个月的情况更复杂,但它不需要经常更新,因为可以在需要之前添加分支。

    # 笔记

    在实践中,如果大多数插入都进入了最新的子对象,那么最好先检查该子对象。为了简单起见,我们以与本例其他部分相同的顺序展示了触发器的测试。

    将插入重定向到相应子表的另一种方法是在根表上设置规则,而不是触发器。例如:

    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.*);
    

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

    要知道复制无视规则。如果你想用复制要插入数据,需要复制到正确的子表中,而不是直接复制到根表中。复制不会触发,所以如果使用触发方法,可以正常使用。

    规则方法的另一个缺点是,如果规则集不包含插入日期,则没有简单的方法强制执行错误;数据将以静默方式进入根表。

  6. 确保限制_排斥未在中禁用配置参数postgresql。形态; 否则,可能会不必要地访问子表。

    正如我们所见,复杂的表层次结构可能需要大量的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.警告

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

  • 没有自动的方法来验证所有的检查约束是相互排斥的。创建生成子表、创建和/或修改关联对象的代码比手工编写每个表更安全。

  • 索引和外键约束适用于单个表,而不是它们的继承子表,因此它们有一些警告意识到。

  • 这里显示的方案假设一行的键列的值永远不会改变,或者至少不会改变到需要移动到另一个分区的程度。一使现代化由于气候变化,这样做的尝试将失败检查限制。如果需要处理此类情况,可以在子表上放置合适的更新触发器,但这会使结构的管理变得更加复杂。

  • 如果您使用的是手动真空分析命令,不要忘了需要在每个子表上单独运行它们。命令如下:

    ANALYZE measurement;
    

    将只处理根表。

  • 插入声明与关于冲突条款不太可能像预期的那样起作用,因为关于冲突仅当指定的目标关系(而非其子关系)发生唯一的违规行为时,才会采取行动。

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

# 5.11.4.分区剪枝

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

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

如果不进行分区修剪,上述查询将扫描测量桌子启用分区修剪后,规划人员将检查每个分区的定义,并证明不需要扫描该分区,因为它不能包含任何符合查询条件的行哪里条款当规划者能够证明这一点时,它排除了(梅干)查询计划中的分区。

通过使用EXPLAIN命令和使可能_隔断_修剪配置参数,可以显示已修剪分区的计划与未修剪分区的计划之间的差异。对于这种类型的表格设置,典型的未优化计划是:

SET enable_partition_pruning = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
### 5.11.5. Partitioning and Constraint Exclusion

[]()

*Constraint exclusion* is a query optimization technique similar to partition pruning. While it is primarily used for partitioning implemented using the legacy inheritance method, it can be used for other purposes, including with declarative partitioning.

 Constraint exclusion works in a very similar way to partition pruning, except that it uses each table's `CHECK` constraints — which gives it its name — whereas partition pruning uses the table's partition bounds, which exist only in the case of declarative partitioning. Another difference is that constraint exclusion is only applied at plan time; there is no attempt to remove partitions at execution time.

 The fact that constraint exclusion uses `CHECK` constraints, which makes it slow compared to partition pruning, can sometimes be used as an advantage: because constraints can be defined even on declaratively-partitioned tables, in addition to their internal partition bounds, constraint exclusion may be able to elide additional partitions from the query plan.

 The default (and recommended) setting of [constraint\_exclusion](runtime-config-query.html#GUC-CONSTRAINT-EXCLUSION) is neither `on` nor `off`, but an intermediate setting called `partition`, which causes the technique to be applied only to queries that are likely to be working on inheritance partitioned tables. The `on` setting causes the planner to examine `CHECK` constraints in all queries, even simple ones that are unlikely to benefit.

 The following caveats apply to constraint exclusion:

* Constraint exclusion is only applied during query planning, unlike partition pruning, which can also be applied during query execution.

* Constraint exclusion only works when the query's `WHERE` clause contains constants (or externally supplied parameters). For example, a comparison against a non-immutable function such as `CURRENT_TIMESTAMP` cannot be optimized, since the planner cannot know which child table the function's value might fall into at run time.

* Keep the partitioning constraints simple, else the planner may not be able to prove that child tables might not need to be visited. Use simple equality conditions for list partitioning, or simple range tests for range partitioning, as illustrated in the preceding examples. A good rule of thumb is that partitioning constraints should contain only comparisons of the partitioning column(s) to constants using B-tree-indexable operators, because only B-tree-indexable column(s) are allowed in the partition key.

* All constraints on all children of the parent table are examined during constraint exclusion, so large numbers of children are likely to increase query planning time considerably. So the legacy inheritance based partitioning will work well with up to perhaps a hundred child tables; don't try to use many thousands of children.

### 5.11.6. Best Practices for Declarative Partitioning

 The choice of how to partition a table should be made carefully, as the performance of query planning and execution can be negatively affected by poor design.

 One of the most critical design decisions will be the column or columns by which you partition your data. Often the best choice will be to partition by the column or set of columns which most commonly appear in `WHERE` clauses of queries being executed on the partitioned table. `WHERE` clauses that are compatible with the partition bound constraints can be used to prune unneeded partitions. However, you may be forced into making other decisions by requirements for the `PRIMARY KEY` or a `UNIQUE` constraint. Removal of unwanted data is also a factor to consider when planning your partitioning strategy. An entire partition can be detached fairly quickly, so it may be beneficial to design the partition strategy in such a way that all data to be removed at once is located in a single partition.

 Choosing the target number of partitions that the table should be divided into is also a critical decision to make. Not having enough partitions may mean that indexes remain too large and that data locality remains poor which could result in low cache hit ratios. However, dividing the table into too many partitions can also cause issues. Too many partitions can mean longer query planning times and higher memory consumption during both query planning and execution, as further described below. When choosing how to partition your table, it's also important to consider what changes may occur in the future. For example, if you choose to have one partition per customer and you currently have a small number of large customers, consider the implications if in several years you instead find yourself with a large number of small customers. In this case, it may be better to choose to partition by `HASH` and choose a reasonable number of partitions rather than trying to partition by `LIST` and hoping that the number of customers does not increase beyond what it is practical to partition the data by.

 Sub-partitioning can be useful to further divide partitions that are expected to become larger than other partitions. Another option is to use range partitioning with multiple columns in the partition key. Either of these can easily lead to excessive numbers of partitions, so restraint is advisable.

 It is important to consider the overhead of partitioning during query planning and execution. The query planner is generally able to handle partition hierarchies with up to a few thousand partitions fairly well, provided that typical queries allow the query planner to prune all but a small number of partitions. Planning times become longer and memory consumption becomes higher when more partitions remain after the planner performs partition pruning. Another reason to be concerned about having a large number of partitions is that the server's memory consumption may grow significantly over time, especially if many sessions touch large numbers of partitions. That's because each partition requires its metadata to be loaded into the local memory of each session that touches it.

 With data warehouse type workloads, it can make sense to use a larger number of partitions than with an OLTP type workload. Generally, in data warehouses, query planning time is less of a concern as the majority of processing time is spent during query execution. With either of these two types of workload, it is important to make the right decisions early, as re-partitioning large quantities of data can be painfully slow. Simulations of the intended workload are often beneficial for optimizing the partitioning strategy. Never just assume that more partitions are better than fewer partitions, nor vice-versa.