Question

PostgreSQL 支持基本的表分区功能。本节描述为什么需要表分区以及如何在数据库设计中使用表分区。

5.9.1. 概述

分区的意思是把逻辑上的一个大表分割成物理上的几块。分区可以提供若干好处：

• 某些类型的查询性能可以得到极大提升。特别是表中访问率较高的行位于一个单独分区或少数几个分区上的情况下。分区可以减少索引体积从而可以将高使用率部分的索引存放在内存中。如果索引不能全部放在内存中，那么在索引上的读和写都会产生更多的磁盘访问。

• 当查询或更新一个分区的大部分记录时，连续扫描那个分区而不是使用索引离散的访问整个表可以获得巨大的性能提升。

• 如果需要大量加载或者删除的记录位于单独的分区上，那么可以通过直接读取或删除那个分区以获得巨大的性能提升，因为 ALTER TABLE 比操作大量的数据要快的多。它同时还可以避免由于大量 DELETE 导致的 VACUUM 超载。

• 很少用的数据可以移动到便宜一些的慢速存储介质上。

这种好处通常只有在表可能会变得非常大的情况下才有价值。到底多大的表会从分区中收益取决于具体的应用，不过有个基本的拇指规则就是表的大小超过了数据库服务器的物理内存大小。

目前，PostgreSQL 支持通过表继承进行分区。每个分区必须做为单独一个父表的子表进行创建。父表自身通常是空的，它的存在只是为了代表整个数据集。你在试图实现分区之前，应该先熟悉继承(参阅节5.8)。

PostgreSQL 可以实现下面形式的分区：

范围分区

表被一个或者多个关键字段分区成"范围"，这些范围在不同的分区里没有重叠。比如，我们可以为特定的商业对象根据数据范围分区，或者根据标识符范围分区。

列表分区

表通过明确地列出每个分区里应该出现那些关键字值实现。

5.9.2. 实现分区

要设置一个分区的表，做下面的步骤：

1. 创建"主表"，所有分区都从它继承。

   这个表中没有数据，不要在这个表上定义任何检查约束，除非你希望约束同样也适用于所有分区。同样，在其上定义任何索引或者唯一约束也没有意义。

2. 创建几个"子表"，每个都从主表上继承。通常，这些表不会增加任何字段。

   我们将把子表称作分区，尽管它们就是普通的 PostgreSQL 表。

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 属于那个范围。

   请注意在范围和列表分区的语法方面没有什么区别；这些术语只是用于描述的。

4. 对于每个分区，在关键字字段上创建一个索引，以及其它你想创建的索引。关键字字段索引并非严格必需的，但是在大多数情况下它是很有帮助的。如果你希望关键字值是唯一的，那么你应该总是给每个分区创建一个唯一或者主键约束。

5. 另外，定义一个规则或者触发器，把对主表的修改重定向到合适的分区表。

6. 确保 postgresql.conf 里的配置参数 constraint_exclusion 是打开的。没有这个参数，查询不会按照需要进行优化。

比如，假设我们为一个巨大的冰激凌公司构造数据库。该公司每天都测量最高温度，以及每个地区的冰激凌销售。概念上，我们需要一个这样的表：

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

我们知道大多数查询都只会访问最后一周，最后一个月或者最后一个季度的数据，因为这个表的主要用途是为管理准备在线报告。为了减少需要存储的旧数据，我们决定值保留最近三年的有用数据。在每个月的开头，我们都会删除最旧的一个月的数据。

在这种情况下，我们可以使用分区来帮助实现所有对表的不同需求。下面的步骤描述了上面的需求，分区可以这样设置：

1. 主表是 measurement 表，就像上面那样声明。

2. 然后我们为每个月创建一个分区：

   CREATE TABLE measurement_y2004m02 ( ) INHERITS (measurement);
   CREATE TABLE measurement_y2004m03 ( ) INHERITS (measurement);
   ...
   CREATE TABLE measurement_y2005m11 ( ) INHERITS (measurement);
   CREATE TABLE measurement_y2005m12 ( ) INHERITS (measurement);
   CREATE TABLE measurement_y2006m01 ( ) INHERITS (measurement);

   每个分区都是拥有自己内容的完整的表，只是它们从 measurement 表继承定义。

   这样就解决了我们的一个问题：删除旧数据。每个月，我们需要做的只是在最旧的子表上执行一个 DROP TABLE ，然后为新月份创建一个新的子表。

3. 我们必须增加非重叠的表约束，所以我们的建表脚本就变成：

   CREATE TABLE measurement_y2004m02 (
       CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
   ) INHERITS (measurement);
   CREATE TABLE measurement_y2004m03 (
       CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
   ) INHERITS (measurement);
   ...
   CREATE TABLE measurement_y2005m11 (
       CHECK ( logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01' )
   ) INHERITS (measurement);
   CREATE TABLE measurement_y2005m12 (
       CHECK ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
   ) INHERITS (measurement);
   CREATE TABLE measurement_y2006m01 (
       CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
   ) INHERITS (measurement);

4. 我们可能还需要在关键字字段上有索引：

   CREATE INDEX measurement_y2004m02_logdate ON measurement_y2004m02 (logdate);
   CREATE INDEX measurement_y2004m03_logdate ON measurement_y2004m03 (logdate);
   ...
   CREATE INDEX measurement_y2005m11_logdate ON measurement_y2005m11 (logdate);
   CREATE INDEX measurement_y2005m12_logdate ON measurement_y2005m12 (logdate);
   CREATE INDEX measurement_y2006m01_logdate ON measurement_y2006m01 (logdate);

   我们选择先不建立更多的索引。

5. 如果数据只进入最新的分区，我们可以设置一个非常简单的规则来插入数据。我们必须每个月都重新定义这个规则，这样它总是指向当前分区。

   CREATE OR REPLACE RULE measurement_current_partition AS
   ON INSERT TO measurement
   DO INSTEAD
       INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id,
                                                 NEW.logdate,
                                                 NEW.peaktemp,
                                                 NEW.unitsales );

   我们可能想插入数据并且想让服务器自动定位应该向哪个分区插入数据。我们可以用下面这个复杂的规则集来实现这个目标。

   CREATE RULE measurement_insert_y2004m02 AS
   ON INSERT TO measurement WHERE
       ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
   DO INSTEAD
       INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id,
                                                 NEW.logdate,
                                                 NEW.peaktemp,
                                                 NEW.unitsales );
   ...
   CREATE RULE measurement_insert_y2005m12 AS
   ON INSERT TO measurement WHERE
       ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
   DO INSTEAD
       INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id,
                                                 NEW.logdate,
                                                 NEW.peaktemp,
                                                 NEW.unitsales );
   CREATE RULE measurement_insert_y2006m01 AS
   ON INSERT TO measurement WHERE
       ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
   DO INSTEAD
       INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id,
                                                 NEW.logdate,
                                                 NEW.peaktemp,
                                                 NEW.unitsales );

   请注意每个规则里面的 WHERE 子句正好匹配其分区的 CHECK 约束。

我们可以看出，一个复杂的分区方案可能要求相当多的 DDL 。在上面的例子里我们需要每个月创建一次新分区，因此写一个脚本自动生成需要的 DDL 是明智的。

分区也可以使用一个 UNION ALL 视图来安排：

CREATE VIEW measurement AS
          SELECT * FROM measurement_y2004m02
UNION ALL SELECT * FROM measurement_y2004m03
...
UNION ALL SELECT * FROM measurement_y2005m11
UNION ALL SELECT * FROM measurement_y2005m12
UNION ALL SELECT * FROM measurement_y2006m01;

但是，重建试图也给增加和删除数据集里的独立分区增加了额外的步骤。

5.9.3. 管理分区

通常分区集在定义表的时候就已经确定了，但我们常常需要周期性的删除旧分区并添加新分区。分区最重要的好处是它能恰到好处的适应这个需求：以极快的速度操作分区的结构，而不是痛苦的物理移动大量数据。

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

DROP TABLE measurement_y2003m02;

这个命令可以迅速删除数包含数百万条记录的分区，因为它不需要单独删除每一条记录。

还可以在删除分区的同时保留其作为一个表访问的能力：

ALTER TABLE measurement_y2003m02 NO INHERIT measurement;

这将允许将来对这些数据执行其它的操作(比如使用 COPY, pg_dump 之类的工具进行备份)。并且此时也是执行其它数据操(数据聚集或运行报表等)的有利时机。

同样，我们可以像前面创建最初的分区一样，创建一个新的空分区来处理新数据。

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

有时在分区结构之外创建新表并在一段时间之后将其变为分区更为方便。因为这将允许在该表变为分区之前对其中的数据进行加载、检查、转换之类的操作。

CREATE TABLE measurement_y2006m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE measurement_y2006m02 ADD CONSTRAINT y2006m02
   CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' );
\copy measurement_y2006m02 from 'measurement_y2006m02'
-- 其它可能的数据准备工作
ALTER TABLE measurement_y2006m02 INHERIT measurement;

5.9.4. 分区和约束排除

约束排除是一种查询优化技巧，它改进了用上述方法定义的表分区的性能。比如：

SET constraint_exclusion = on;
SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';

如果没有约束排除，上面的查询会扫描 measurement 表中的每一个分区。打开了约束排除之后，规划器将检查每个分区的约束然后试图证明该分区不需要被扫描(因为它不能包含任何符合 WHERE 子句条件的数据行)。如果规划器可以证明这个，它就把该分区从查询规划里排除出去。

你可以使用 EXPLAIN 命令显示一个规划在 constraint_exclusion 打开和关闭情况下的不同。用上面方法设置的表的典型的缺省规划是：

SET constraint_exclusion = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=158.66..158.68 rows=1 width=0)
   ->  Append  (cost=0.00..151.88 rows=2715 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
         ->  Seq Scan on measurement_y2004m02 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
         ->  Seq Scan on measurement_y2004m03 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
...
         ->  Seq Scan on measurement_y2005m12 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
         ->  Seq Scan on measurement_y2006m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)

部分或者全部分区可能会使用索引扫描而不是全表扫描，不过这里要表达的意思是没有必要扫描旧分区就可以回答这个查询。在打开约束排除之后，我们可以得到生成同样回答的明显简化的规划：

SET constraint_exclusion = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=63.47..63.48 rows=1 width=0)
   ->  Append  (cost=0.00..60.75 rows=1086 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
         ->  Seq Scan on measurement_y2006m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)

请注意，约束排除只由 CHECK 约束驱动，而不会由索引驱动。因此，在关键字字段上定义索引是没有必要的。在给出的分区上是否需要建立索引取决于那些扫描该分区的查询通常是扫描该分区的一大部分还是只是一小部分。对于后者，索引通常都有帮助，对于前者则没有什么好处。

5.9.5. 警告

下面的注意事项适合于已分区的表：

• 当前没有办法验证所有的 CHECK 约束是互斥的。数据库的设计者必须自己保证这一点。

• 当前没有简便的方法保证数据不被插入主表。主表上的 CHECK (false) 约束将被所有分区继承，所以不能用于此目的。一种方法是在主表上设置一个始终报错的 ON INSERT 触发器，或者将插入操作重定向至正确的子表(从而避免上述建议的一大堆规则)。

下面的注意事项适合于约束排除：

• 约束排除只是在 WHERE 子句包含约束的时候才生效。一个参数化的查询不会被优化，因为在运行时规划器不知道该参数会选择哪个分区。由于某些原因，像 CURRENT_DATE 这样"稳定的"函数必须避免。

• 在 CHECK 约束里面避免跨数据类型的比较，因为目前规划器会无法证明这样的条件为假。比如，下面的约束会在 x 是 integer 字段的时候可用，但是在 x 是一个 bigint 的时候不能用：

  CHECK ( x = 1 )

  对于 bigint 字段，我们必须使用类似下面这样的约束：

  CHECK ( x = 1::bigint )

  这个问题并不仅仅局限于 bigint 数据类型，它可能会发生在任何约束的缺省数据类型与其比较的字段的数据类型不匹配的场合。在提交的查询里的跨数据类型的比较通常是可以的，只是不能在 CHECK 条件里。

• 主表的所有分区上面的所有约束都认为是约束排除了的，因此，大量的分区会显著增加计算查询规划的时间。

• 别忘记你仍然需要为每个分区独立运行 ANALYZE 。类似下面的命令

  ANALYZE measurement;

  只会处理主表。