PostgreSQL使用表继承实现分区表

2021-11-05 20:03:33

PostgreSQL9.6支持基本表的分区。这部分将描述为什么以及如何来实现表分区作为你数据库设计的一部分。

概述
分区指的是将逻辑上一的一个大表分成多个小的物理上的片(子表)，分区可以提供以下好处:
.在某些情况下查询性能能够显著提升，特别是当那些访问压力大的行在一个分区或者少数几个分区时。分区可以取代索引的主导列、减小索引尺寸以及使索引中访问压力大的部分更有可能被放在内存中。

.当查询或更新访问一个分区的大部分行时，可以通过该分区上的一个顺序扫描来取代分散到整个表上的索引和随机访问，这样可以改善性能。

.如果需求计划使用划分设计，可以通过增加或移除分区来完成批量载入和删除。ALTER TABLE NO INHERIT和DROP TABLE都远快于一个批量操作。这些命令也完全避免了由批量DELETE造成的VACUUM负载。

.很少使用的数据可以被迁移到便宜且较慢的存储介质上。

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

当前，PostgreSQL支持通过表继承来实现分区。每个分区必须被创建为单个父表的子表。父表它本身正常来说是空的；它存在仅仅是代表整个数据库。在试图设置分区之前应该要先熟悉表继承。

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

范围分区
表被根据一个关键列或一组列划分为"范围"分区，不同的分区的范围之间没有重叠。例如，我们可以根据日期范围划分分区，或者根据特定业务对象的标识符划分分区。

列表分区
通过显式地列出每一个分区中出现的键值来划分表。

实现分区
要建立一个分区表，可以这样做：
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
);
insert into measurement values(1,date '2008-02-01',1,1);
由于该表的主要用途是为管理层提供在线报告，我们知道大部分查询将只会访问上周、上月或者上季度的数据。为了减少需要保存的旧数据的量，我们决定只保留最近3年的数据。在每一个月的开始，我们将删除最老的一个月的数据。

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

1.主表是measurement表，完全按照以上的方式声明。

jydb=# CREATE TABLE measurement (
jydb(# city_id int not null,
jydb(# logdate date not null,
jydb(# peaktemp int,
jydb(# unitsales int
jydb(# );
CREATE TABLE
2.下一步我们为每一个活动月创建一个分区：

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命令并为新一个月的数据创建一个新的子表。

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

jydb=# CREATE TABLE measurement_y2006m02 (
jydb(# CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) jydb(# ) INHERITS (measurement); CREATE TABLE jydb=# CREATE TABLE measurement_y2006m03 ( jydb(# CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' ) jydb(# ) INHERITS (measurement); CREATE TABLE jydb=# CREATE TABLE measurement_y2008m02 ( jydb(# CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
jydb(# ) INHERITS (measurement);
CREATE TABLE
jydb=# CREATE TABLE measurement_y2008m03
jydb-# (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
CREATE TABLE
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.我们希望我们的应用能够使用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();

我们必须在每个月重新定义触发器函数，这样它才会总是指向当前分区。而触发器的定义则不需要被更新。

我们也可能希望插入数据时服务器会自动地定位应该加入数据的分区。我们可以通过一个更复杂的触发器函数来实现之，例如：

jydb=# CREATE OR REPLACE FUNCTION measurement_insert_trigger()
jydb-# RETURNS TRIGGER AS

$$ jydb$# BEGIN jydb$# jydb$# IF ( NEW.logdate >= DATE '2006-03-01' AND jydb$# NEW.logdate < DATE '2006-04-01' ) THEN jydb$# INSERT INTO measurement_y2006m03 VALUES (NEW.*); jydb$# ELSIF ( NEW.logdate >= DATE '2008-02-01' AND jydb$# NEW.logdate < DATE '2008-03-01' ) THEN jydb$# INSERT INTO measurement_y2008m02 VALUES (NEW.*); jydb$# ELSE jydb$# RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!'; jydb$# END IF; jydb$# RETURN NULL; jydb$# END; jydb$# $$

jydb-# LANGUAGE plpgsql;
CREATE FUNCTION
jydb=# CREATE TRIGGER insert_measurement_trigger
jydb-# BEFORE INSERT ON measurement
jydb-# FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
CREATE TRIGGER
jydb=# insert into measurement values(1,date '2006-03-03',1,1);
INSERT 0 0
jydb=# insert into measurement values(1,date '2008-02-03',1,1);
INSERT 0 0
jydb=# select * from measurement_y2006m03;

(2 rows)
jydb=# select * from measurement_y2008m02;

(5 rows)
jydb=# select * from measurement;

(7 rows)
触发器的定义和以前一样。注意每一个IF测试必须准确地匹配它的分区的CHECK约束。当该函数比单月形式更加复杂时，并不需要频繁地更新它，因为可以在需要的时候提前加入分支。

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

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

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

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

jydb=# DROP TABLE measurement_y2006m02;
DROP TABLE
这可以非常快地删除百万级别的记录，因为它不需要逐一地删除记录。

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

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

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

jydb=# CREATE TABLE measurement_y2008m02 (
jydb(# CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
jydb(# ) INHERITS (measurement);
CREATE TABLE
作为一种选择方案，有时创建一个在分区结构之外的新表更方便，并且在以后才将它作为一个合适的分区。这使得数据可以在出现于分区表中之前被载入、检查和转换：

jydb=# CREATE TABLE measurement_y2008m03
jydb-# (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
CREATE TABLE
jydb=# ALTER TABLE measurement_y2008m03 ADD CONSTRAINT y2008m03
jydb-# CHECK ( logdate >= DATE '2008-03-01' AND logdate < DATE '2008-04-01' );
ALTER TABLE
jydb=# ALTER TABLE measurement_y2008m03 INHERIT measurement;
ALTER TABLE
分区与约束排除
约束排除是一种查询优化技术，它可以为按照以上方式定义的分区表提高性能。例如：

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

count

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

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

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

                                QUERY PLAN

Aggregate (cost=107.47..107.48 rows=1 width=8)
-> Append (cost=0.00..102.69 rows=1913 width=0)

     ->  Seq Scan on measurement  (cost=0.00..3.31 rows=62 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_y2008m02  (cost=0.00..33.12 rows=617 width=0)
           Filter: (logdate >= '2008-01-01'::date)
     ->  Seq Scan on measurement_y2008m03  (cost=0.00..33.12 rows=617 width=0)
           Filter: (logdate >= '2008-01-01'::date)

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

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

                                QUERY PLAN

Aggregate (cost=72.80..72.81 rows=1 width=8)
-> Append (cost=0.00..69.56 rows=1296 width=0)

     ->  Seq Scan on measurement  (cost=0.00..3.31 rows=62 width=0)
           Filter: (logdate >= '2008-01-01'::date)
     ->  Seq Scan on measurement_y2008m02  (cost=0.00..33.12 rows=617 width=0)
           Filter: (logdate >= '2008-01-01'::date)
     ->  Seq Scan on measurement_y2008m03  (cost=0.00..33.12 rows=617 width=0)
           Filter: (logdate >= '2008-01-01'::date)

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

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

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

jydb=# CREATE RULE measurement_insert_y2006m03 AS
jydb-# ON INSERT TO measurement WHERE
jydb-# ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' ) jydb-# DO INSTEAD jydb-# INSERT INTO measurement_y2006m03 VALUES (NEW.*); CREATE RULE jydb=# CREATE RULE measurement_insert_y2008m02 AS jydb-# ON INSERT TO measurement WHERE jydb-# ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
jydb-# DO INSTEAD
jydb-# INSERT INTO measurement_y2008m02 VALUES (NEW.*);
CREATE RULE
jydb=# insert into measurement values(1,date '2006-03-02',1,1);
INSERT 0 0
jydb=# insert into measurement values(1,date '2008-02-02',1,1);
INSERT 0 0
jydb=# select * from measurement;

(5 rows)
jydb=# select * from measurement_y2006m03;

(1 row)
jydb=# select * from measurement_y2008m02;

(4 rows)
一个规则比一个触发器具有明显更高的负荷，但是该负荷是由每个查询承担而不是每一个行，因此这种方法可能对于批量插入的情况有益。但是，在大部分情况下触发器方法能提供更好的性能。

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

警告
下面的警告适用于分区表:
.没有自动的方法来验证所有的CHECK约束是互斥的。创建代码来生成分区并创建或修改相关对象比手工写命令要更安全。

.这里展示的模式都假设分区的关键列从不改变，或者是其改变不足以导致它被移到另一个分区。一个尝试将行移到另一个分区的UPDATE会失败，因为CHECK约束的存在。如果我们需要处理这类情况，我们可以在分区表上放置合适的更新触发器，但是它会使得结构的管理更加复杂。

.如果我们在使用手工的VACUUM或ANALYZE命令，别忘了需要在每一个分区上都运行一次。以下的命令：
ANALYZE measurement;
只会处理主表。

.带有ON CONFLICT子句的INSERT 语句不太可能按照预期的方式工作，因为ON CONFLICT动作只有在指定的目标关系（而非它的子关系）上有唯一违背的情况下才会被采用。

下面的警告适用于约束排除：
.只有在查询的WHERE子句包含常量（或者外部提供的参数）时，约束排除才会起效。例如，一个与非不变函数（例如CURRENT_TIMESTAMP）的比较不能被优化，因为规划器不知道该函数的值在运行时会落到哪个分区内。

.保持分区约束简单，否则规划器可能没有办法验证无需访问的分区。按前面的例子所示，为列表分区使用简单相等条件或者为范围分区使用简单范围测试。一个好的经验法则是分区约束应该只包含使用B-tree索引操作符的比较，比较的双方应该是分区列和常量。

.在约束排除期间，主表所有的分区上的所有约束都会被检查，所以大量的分区将会显著地增加查询规划时间。使用这些技术的分区在大约最多100个分区的情况下工作得很好，但是不要尝试使用成千个分区。

码农公寓

count

相关文章