划分

PostgreSQL

PostgreSQL 9.6.0 手册
上一页	上一级	章 5. 数据定义	下一页

5.10. 划分

PostgreSQL支持基本的表划分。本小节介绍为何以及怎样把划分实现为数据库设计的一部分。

5.10.1. 概述

划分指的是将逻辑上的一个大表分成一些小的物理上的片。划分有很多益处：

在某些情况下查询性能能够显著提升，特别是当那些访问压力大的行在一个分区或者少数几个分区时。划分可以取代索引的主导列、减小索引尺寸以及使索引中访问压力大的部分更有可能被放在内存中。
当查询或更新访问一个分区的大部分行时，可以通过该分区上的一个顺序扫描来取代分散到整个表上的索引和随机访问，这样可以改善性能。
如果需求计划使用划分设计，可以通过增加或移除分区来完成批量载入和删除。ALTER TABLE NO INHERIT和DROP TABLE都远快于一个批量操作。这些命令也完全避免了由批量DELETE造成的VACUUM负载。
很少使用的数据可以被迁移到便宜且较慢的存储介质上。

当一个表非常大时，划分所带来的好处是非常值得的。一个表何种情况下会从划分获益取决于应用，一个经验法则是当表的尺寸超过了数据库服务器物理内存时，划分会为表带来好处。

目前，PostgreSQL支持通过表继承来进行划分。每一个分区被创建为父表的一个子表。父表本身通常是空的，它的存在仅仅为了表示整个数据集。在尝试建立划分之前，应该先熟悉继承（参见第 5.9 节）。

在PostgreSQL中可以实现下列形式的划分：

范围划分: 表被根据一个关键列或一组列划分为"范围"，不同的分区的范围之间没有重叠。例如，我们可以根据日期范围划分，或者根据特定业务对象的标识符划分。
列表划分: 通过显式地列出每一个分区中出现的键值来划分表。

5.10.2. 实现划分

要建立一个划分的表，可以这样做：

创建"主"表，所有的分区都将继承它。

这个表将不会包含任何数据。不要在这个表上定义任何检查约束，除非准备将它们应用到所有分区。同样也不需要定义任何索引或者唯一约束。
创建一些继承于主表的"子"表。通常，这些表不会在从主表继承的列集中增加任何列。

我们将这些子表认为是分区，尽管它们在各方面来看普通的PostgreSQL表（或者可能是外部表）。
为分区表增加表约束以定义每个分区中允许的键值。

典型的例子是：
```
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并没有被清楚地分配到某一个分区。

注意在语法上范围划分和列表划分没有区别，这些术语只是为了描述方便而存在。
对于每一个分区，在关键列上创建一个索引，并创建其他我们所需要的索引（关键索引并不是严格必要的，但是在大部分情况下它都是有用的。如果我们希望键值是唯一的，则我们还要为每一个分区创建一个唯一或者主键约束。）。
还可以有选择地定义一个触发器或者规则将在主表上的数据插入重定向到合适的分区上。
确保在postgresql.conf中constraint_exclusion配置参数没有被禁用。如果它被禁用，查询将不会被按照期望的方式优化。

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

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

由于该表的主要用途是为管理层提供在线报告，我们知道大部分查询将只会访问上周、上月或者上季度的数据。为了减少需要保存的旧数据的量，我们决定只保留最近3年的数据。在每一个月的开始，我们将删除最老的一个月的数据。

在这种情况下，我们可以使用划分来帮助我们满足对于测量表的所有不同需求。按照上面所勾勒的步骤，划分可以这样来建立：

主表是measurement表，完全按照以上的方式声明。
下一步我们为每一个活动月创建一个分区：
```
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);
```
每一个分区自身都是完整的表，但是它们的定义都是从measurement表继承而来。

这解决了我们的一个问题：删除旧数据。每个月，我们所需要做的是在最旧的子表上执行一个DROP TABLE命令并为新一个月的数据创建一个新的子表。

我们必须提供不重叠的表约束。和前面简单地创建分区表不同，实际的表创建脚本应该是：

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 PROCEDURE 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约束。

当该函数比单月形式更加复杂时，并不需要频繁地更新它，因为可以在需要的时候提前加入分支。

注意: 在实践中，如果大部分插入都会进入最新的分区，最好先检查它。为了简洁，我们为触发器的检查采用了和本例中其他部分一致的顺序。

如我们所见，一个复杂的划分模式可能需要大量的DDL。在上面的例子中，我们需要每月创建一个新分区，所以最好能够编写一个脚本自动地生成所需的DDL。

5.10.3. 管理分区

通常当初始定义的表倾向于动态变化时，一组分区会被创建。删除旧的分区并周期性地为新数据增加新分区是很常见的。划分的一个最重要的优点是可以通过操纵分区结构来使得这种痛苦的任务几乎是自发地完成，而不需要去物理地移除大量的数据。

移除旧数据的最简单的选项是直接删除不再需要的分区：

DROP TABLE measurement_y2006m02;

这可以非常快地删除百万级别的记录，因为它不需要逐一地删除记录。

另一个经常使用的选项是将分区从被划分的表中移除，但是把它作为一个独立的表保留下来：

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

这允许在数据被删除前执行更进一步的操作。例如，这是一个很有用的时机通过COPY、pg_dump或类似的工具来备份数据。这也是进行数据聚集、执行其他数据操作或运行报表的好时机。

相似地我们也可以增加新分区来处理新数据。我们可以在被划分的表中创建一个新的空分区：

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'
-- 可能做一些其他数据准备工作
ALTER TABLE measurement_y2008m02 INHERIT measurement;

5.10.4. 划分和约束排除

约束排除是一种查询优化技术，它可以为按照以上方式定义的被划分表提高性能。例如：

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

如果没有约束排除，上述查询将扫描measurement表的每一个分区。在启用约束排除后，规划器将检查每一个分区的约束来确定该分区需不需要被扫描，因为分区中可能不包含满足查询WHERE子句的行。如果规划器能够证实这一点，则它将会把该分区排除在查询计划之外。

可以使用EXPLAIN命令来显示开启了constraint_exclusion的计划和没有开启该选项的计划之间的区别。一个典型的未优化的计划是：

SET constraint_exclusion = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-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 >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m02 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m03 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
...
         ->  Seq Scan on measurement_y2007m12 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)

其中的某些或者全部分区将会使用索引扫描而不是全表顺序扫描，但是关键在于根本不需要扫描旧分区来回答这个查询。当我们开启约束排除后，对于同一个查询我们会得到一个更加廉价的计划：

SET constraint_exclusion = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-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 >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)

注意约束排除只由CHECK约束驱动，而非索引的存在。因此，没有必要在关键列上定义索引。是否在给定分区上定义索引取决于我们希望查询经常扫描表的大部分还是小部分。在后一种情况中索引将会发挥作用。

constraint_exclusion的默认（也是推荐）设置实际上既不是on也不是off，而是一个被称为partition的中间设置，这使得该技术只被应用于将要在被分区表上工作的查询。设置on将使得规划器在所有的查询中检查CHECK约束，即使简单查询不会从中受益。

5.10.5. 可选划分方法

另一种将插入重定向到合适的分区表的方法是在主表上建立规则而不是触发器，例如：

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

规则方法的另一个缺点是如果一组规则没有覆盖被插入的数据，则该数据将被插入到主表中而不会发出任何错误。

划分也可以使用一个UNION ALL视图来组织。例如：

CREATE VIEW measurement AS
          SELECT * FROM measurement_y2006m02
UNION ALL SELECT * FROM measurement_y2006m03
...
UNION ALL SELECT * FROM measurement_y2007m11
UNION ALL SELECT * FROM measurement_y2007m12
UNION ALL SELECT * FROM measurement_y2008m01;

但是，如果要增加或者删除单独的分区，就需要重新地创建视图。在实践中，相对于使用继承，这种方法很少被推荐。

5.10.6. 警告

下面的警告适用于被划分表：

没有自动的方法来验证所有的CHECK约束是互斥的。创建代码来生成分区并创建或修改相关对象比手工写命令要更安全。
这里展示的模式都假设分区的关键列从不改变，或者是其改变不足以导致它被移到另一个分区。一个尝试将行移到另一个分区的UPDATE会失败，因为CHECK约束的存在。如果我们需要处理这类情况，我们可以在分区表上放置合适的更新触发器，但是它会使得结构的管理更加复杂。
如果我们在使用手工的VACUUM或ANALYZE命令，别忘了需要在每一个分区上都运行一次。以下的命令：
```
ANALYZE measurement;
```
只会处理主表。
带有ON CONFLICT子句的INSERT 语句不太可能按照预期的方式工作，因为ON CONFLICT动作只有在指定的目标关系（而非它的子关系）上有唯一违背的情况下才会被采用。

下面的警告适用于约束排除：

只有在查询的WHERE子句包含常量（或者外部提供的参数）时，约束排除才会起效。例如，一个与非不变函数（例如CURRENT_TIMESTAMP）的比较不能被优化，因为规划器不知道该函数的值在运行时会落到哪个分区内。
保持划分约束简单，否则规划器可能没有办法验证无需访问的分区。按前面的例子所示，为列表划分使用简单相等条件或者为范围划分使用简单范围测试。一个好的经验法则是划分约束应该只包含使用B-tree可索引操作符的比较，比较的双方应该是划分列和常量。
在约束排除期间，主表所有的分区上的所有约束都会被检查，所以大量的分区将会显著地增加查询规划时间。使用这些技术的划分在大约最多100个分区的情况下工作得很好，但是不要尝试使用成千个分区。

上一页	起始页	下一页
继承	上一级	外部数据

previous page start next page