PostgreSQL 分区表, pg_pathman ,PostgreSQL 10介绍及性能对比(转载)
转载自：https://my.oschina.net/yonj1e/blog/868402
PostgreSQL 分区表, pg_pathman ,PostgreSQL 10介绍及性能对比 原
yonj1e yonj1e 发布于 2017/03/27 15:23 字数 5231 阅读 851 收藏 2 点赞 0 评论 0
PostgreSQL
简介
在数据库日渐庞大的今天，为了方便对数据库数据的管理，比如按时间，按地区去统计一些数据时，基数过于庞大，多有不便。很多商业数据库都提供分区的概念，按不同的维度去存放数据，便于后期的管理，PostgreSQL也不例外。
PostgresSQL分区的意思是把逻辑上的一个大表分割成物理上的几块儿。分区不仅能带来访问速度的提升，关键的是，它能带来管理和维护上的方便。
分区的具体好处是：
改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度
增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用
维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可
均衡I/O：可以把不同的分区映射到不同磁盘以平衡I/O，改善整个系统性能
表分区介绍
数据库表分区把一个大的物理表分成若干个小的物理表，并使得这些小物理表在逻辑上可以被当成一张表来使用。
主表 / 父表 / Master Table 该表是创建子表的模板。它是一个正常的普通表，但正常情况下它并不储存任何数据。
子表 / 分区表 / Child Table / Partition Table 这些表继承并属于一个主表。子表中存储所有的数据。主表与分区表属于一对多的关系，也就是说，一个主表包含多个分区表，而一个分区表只从属于一个主表
传统分区表
现在PostgreSQL支持通过表继承来实现表的分区。父表是普通表并且正常情况下并不存储任何数据，它的存在只是为了代表整个数据集。PostgreSQL可实现如下两种表分区
范围分区 每个分区表包含一个或多个字段组合的一部分，并且每个分区表的范围互不重叠。比如可近日期范围分区
列表分区 分区表显示列出其所包含的key值
实现分区
1，创建"主表"，所有分区都从它继承。
这个表中没有数据，不要在这个表上定义任何检查约束，除非你希望约束同样也适用于所有分区。同样，在其上定义任何索引或者唯一约束也没有意义。
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
2，创建几个"子表"，每个都从主表上继承。通常，这些表不会增加任何字段。
我们将把子表称作分区，尽管它们就是普通的PostgreSQL表。
CREATE TABLE measurement_y2017m01 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2017m02 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2017m03 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2017m04 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2017m05 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2017m06 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2017m07 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2017m08 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2017m09 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2017m10 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2017m11 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2017m12 ( ) INHERITS (measurement);
3，给分区表增加约束，定义每个分区允许的健值。
确保这些约束能够保证在不同的分区里不会有重叠的键值。
alter table measurement_y2017m01 add CHECK ( logdate >= DATE ‘2017-01-01’ AND logdate < DATE ‘2017-02-01’ );
alter table measurement_y2017m02 add CHECK ( logdate >= DATE ‘2017-02-01’ AND logdate < DATE ‘2017-03-01’ );
alter table measurement_y2017m03 add CHECK ( logdate >= DATE ‘2017-03-01’ AND logdate < DATE ‘2017-04-01’ );
alter table measurement_y2017m04 add CHECK ( logdate >= DATE ‘2017-04-01’ AND logdate < DATE ‘2017-05-01’ );
alter table measurement_y2017m05 add CHECK ( logdate >= DATE ‘2017-05-01’ AND logdate < DATE ‘2017-06-01’ );
alter table measurement_y2017m06 add CHECK ( logdate >= DATE ‘2017-06-01’ AND logdate < DATE ‘2017-07-01’ );
alter table measurement_y2017m07 add CHECK ( logdate >= DATE ‘2017-07-01’ AND logdate < DATE ‘2017-08-01’ );
alter table measurement_y2017m08 add CHECK ( logdate >= DATE ‘2017-08-01’ AND logdate < DATE ‘2017-09-01’ );
alter table measurement_y2017m09 add CHECK ( logdate >= DATE ‘2017-09-01’ AND logdate < DATE ‘2017-10-01’ );
alter table measurement_y2017m10 add CHECK ( logdate >= DATE ‘2017-10-01’ AND logdate < DATE ‘2017-11-01’ );
alter table measurement_y2017m11 add CHECK ( logdate >= DATE ‘2017-11-01’ AND logdate < DATE ‘2017-12-01’ );
alter table measurement_y2017m12 add CHECK ( logdate >= DATE ‘2017-12-01’ AND logdate < DATE ‘2018-01-01’ );
4，对于每个分区，在关键字字段上创建一个索引，以及其它你想创建的索引。关键字字段索引并非严格必需的，但是在大多数情况下它是很有帮助的。
create index measurement_y2017m01_logdate on measurement_y2017m01 (logdate);
create index measurement_y2017m02_logdate on measurement_y2017m02 (logdate);
create index measurement_y2017m03_logdate on measurement_y2017m03 (logdate);
create index measurement_y2017m04_logdate on measurement_y2017m04 (logdate);
create index measurement_y2017m05_logdate on measurement_y2017m05 (logdate);
create index measurement_y2017m06_logdate on measurement_y2017m06 (logdate);
create index measurement_y2017m07_logdate on measurement_y2017m07 (logdate);
create index measurement_y2017m08_logdate on measurement_y2017m08 (logdate);
create index measurement_y2017m09_logdate on measurement_y2017m09 (logdate);
create index measurement_y2017m10_logdate on measurement_y2017m10 (logdate);
create index measurement_y2017m11_logdate on measurement_y2017m11 (logdate);
create index measurement_y2017m12_logdate on measurement_y2017m12 (logdate);
5，另外，定义一个规则或者触发器，来重定向数据插入主表到适当的分区。
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.logdate >= DATE ‘2017-01-01’ AND
NEW.logdate < DATE ‘2017-02-01’ ) THEN
INSERT INTO measurement_y2017m01 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE ‘2017-02-01’ AND
NEW.logdate < DATE ‘2017-03-01’ ) THEN
INSERT INTO measurement_y2017m02 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE ‘2017-03-01’ AND
NEW.logdate < DATE ‘2017-04-01’ ) THEN
INSERT INTO measurement_y2017m03 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE ‘2017-04-01’ AND
NEW.logdate < DATE ‘2017-05-01’ ) THEN
INSERT INTO measurement_y2017m04 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE ‘2017-05-01’ AND
NEW.logdate < DATE ‘2017-06-01’ ) THEN
INSERT INTO measurement_y2017m05 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE ‘2017-06-01’ AND
NEW.logdate < DATE ‘2017-07-01’ ) THEN
INSERT INTO measurement_y2017m06 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE ‘2017-07-01’ AND
NEW.logdate < DATE ‘2017-08-01’ ) THEN
INSERT INTO measurement_y2017m07 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE ‘2017-08-01’ AND
NEW.logdate < DATE ‘2017-09-01’ ) THEN
INSERT INTO measurement_y2017m08 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE ‘2017-09-01’ AND
NEW.logdate < DATE ‘2017-10-01’ ) THEN
INSERT INTO measurement_y2017m09 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE ‘2017-10-01’ AND
NEW.logdate < DATE ‘2017-11-01’ ) THEN
INSERT INTO measurement_y2017m10 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE ‘2017-11-01’ AND
NEW.logdate < DATE ‘2017-12-01’ ) THEN
INSERT INTO measurement_y2017m11 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE ‘2017-12-01’ AND
NEW.logdate < DATE ‘2018-01-01’ ) THEN
INSERT INTO measurement_y2017m12 VALUES (NEW.*);
ELSE
RAISE EXCEPTION ‘Date out of range. Fix the measurement_insert_trigger() function!’;
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
create trigger insert_measurement_trigger
before insert on measurement
for each row execute procedure measurement_insert_trigger();
注意，每一个IF测试必须匹配其分区的 CHECK约束。
6，确保postgresql.conf里的配置参数constraint_exclusion是打开的。没有这个参数，查询不会按照需要进行优化。
约束排除
约束排除是一种查询优化技巧，它改进了用上述方法定义的表分区的性能。比如：
SET constraint_exclusion = on;
SELECT count(*) FROM measurement WHERE logdate >= DATE ‘2017-11-22’;
如果没有约束排除，上面的查询会扫描measurement表中的每一个分区。打开了约束排除之后，规划器将检查每个分区的约束然后试图证明该分区不需要被扫描 (因为它不能包含任何符合WHERE子句条件的数据行)。如果规划器可以证明这个，它就把该分区从查询规划里排除出去。
postgres=# set constraint_exclusion = off;
SET
postgres=# explain select count(*) from measurement where logdate >= ‘2017-11-22’;
QUERY PLAN
Aggregate (cost=338.27…338.28 rows=1 width=0)
-> Append (cost=0.00…319.76 rows=7405 width=0)
-> Seq Scan on measurement (cost=0.00…0.00 rows=1 width=0)
Filter: (logdate >= ‘2017-11-22’::date)
-> Bitmap Heap Scan on measurement_y2017m01 (cost=8.93…26.65 rows=617 width=0)
Recheck Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Index Scan on measurement_y2017m01_logdate (cost=0.00…8.78 rows=617 width=0)
Index Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Heap Scan on measurement_y2017m02 (cost=8.93…26.65 rows=617 width=0)
Recheck Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Index Scan on measurement_y2017m02_logdate (cost=0.00…8.78 rows=617 width=0)
Index Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Heap Scan on measurement_y2017m03 (cost=8.93…26.65 rows=617 width=0)
Recheck Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Index Scan on measurement_y2017m03_logdate (cost=0.00…8.78 rows=617 width=0)
Index Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Heap Scan on measurement_y2017m04 (cost=8.93…26.65 rows=617 width=0)
Recheck Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Index Scan on measurement_y2017m04_logdate (cost=0.00…8.78 rows=617 width=0)
Index Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Heap Scan on measurement_y2017m05 (cost=8.93…26.65 rows=617 width=0)
Recheck Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Index Scan on measurement_y2017m05_logdate (cost=0.00…8.78 rows=617 width=0)
Index Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Heap Scan on measurement_y2017m06 (cost=8.93…26.65 rows=617 width=0)
Recheck Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Index Scan on measurement_y2017m06_logdate (cost=0.00…8.78 rows=617 width=0)
Index Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Heap Scan on measurement_y2017m07 (cost=8.93…26.65 rows=617 width=0)
Recheck Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Index Scan on measurement_y2017m07_logdate (cost=0.00…8.78 rows=617 width=0)
Index Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Heap Scan on measurement_y2017m08 (cost=8.93…26.65 rows=617 width=0)
Recheck Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Index Scan on measurement_y2017m08_logdate (cost=0.00…8.78 rows=617 width=0)
Index Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Heap Scan on measurement_y2017m09 (cost=8.93…26.65 rows=617 width=0)
Recheck Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Index Scan on measurement_y2017m09_logdate (cost=0.00…8.78 rows=617 width=0)
Index Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Heap Scan on measurement_y2017m10 (cost=8.93…26.65 rows=617 width=0)
Recheck Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Index Scan on measurement_y2017m10_logdate (cost=0.00…8.78 rows=617 width=0)
Index Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Heap Scan on measurement_y2017m11 (cost=8.93…26.65 rows=617 width=0)
Recheck Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Index Scan on measurement_y2017m11_logdate (cost=0.00…8.78 rows=617 width=0)
Index Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Heap Scan on measurement_y2017m12 (cost=8.93…26.65 rows=617 width=0)
Recheck Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Index Scan on measurement_y2017m12_logdate (cost=0.00…8.78 rows=617 width=0)
Index Cond: (logdate >= ‘2017-11-22’::date)
(52 rows)
使用EXPLAIN命令显示一个规划在constraint_exclusion 打开和关闭情况下的不同。
postgres=# set constraint_exclusion = on;
SET
postgres=# explain select count(*) from measurement where logdate >= ‘2017-11-22’;
QUERY PLAN
Aggregate (cost=56.38…56.39 rows=1 width=0)
-> Append (cost=0.00…53.29 rows=1235 width=0)
-> Seq Scan on measurement (cost=0.00…0.00 rows=1 width=0)
Filter: (logdate >= ‘2017-11-22’::date)
-> Bitmap Heap Scan on measurement_y2017m11 (cost=8.93…26.65 rows=617 width=0)
Recheck Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Index Scan on measurement_y2017m11_logdate (cost=0.00…8.78 rows=617 width=0)
Index Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Heap Scan on measurement_y2017m12 (cost=8.93…26.65 rows=617 width=0)
Recheck Cond: (logdate >= ‘2017-11-22’::date)
-> Bitmap Index Scan on measurement_y2017m12_logdate (cost=0.00…8.78 rows=617 width=0)
Index Cond: (logdate >= ‘2017-11-22’::date)
(12 rows)
请注意，约束排除只由CHECK约束驱动，而不会由索引驱动。因此，在关键字字段上定义索引是没有必要的。
传统分区表原理
传统上使用表继承来实现PostgreSQL中的分区。每个分区必须创建为具有CHECK CONSTRAINT的子表，定义触发器或RULE来重定向数据插入主表到适当的分区，由于查询和更新涉及约束的检查、插入则涉及触发器或规则重写，导致分区功能性能较差。
pg_pathman
pg_pathman 是一款PostgreSQL分区工具，pg_pathman模块提供了优化的分区机制和功能来管理分区。pg_pathman现在稳定的版本是1.2.1，只支持PostgreSQL 9.5以及以上的版本。
特性
HASH和RANGE分区方案
自动和手动分区管理
支持整数，浮点数，日期等类型，包括域名
分区表（JOIN，子选择等）的有效查询计划
RuntimeAppend＆RuntimeMergeAppend自定义计划节点在运行时选择分区
PartitionFilter：INSERT触发器的高效插入式替换
自动分区创建新的INSERTED数据（仅适用于RANGE分区）
改进了能够将行直接插入分区的COPY FROM \ TO语句
UPDATE触发生成开箱即用（也将替换为自定义节点）
分区创建事件处理的用户定义回调
非阻塞并发表分区
FDW支持（外部表）
各种GUC切换和可配置设置
安装
wget https://api.pgxn.org/dist/pg_pathman/1.2.1/pg_pathman-1.2.1.zip
unzip pg_pathman-1.2.1.zip
cd pg_pathman-1.2.1
make USE_PGXS=1
make USE_PGXS=1 install
cd $PGDATA
vi postgresql.conf
shared_preload_libraries = ‘pg_pathman’
pg_ctl restart -m fast
psql
postgres=# create extension pg_pathman;
CREATE EXTENSION
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
------------±--------±-----------±-----------------------------
pg_pathman | 1.2 | public | Partitioning tool
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
视图和表
pg_pathman 使用函数来维护分区表，并且创建了一些视图，可以查看分区表的状态。
分区表的定义则存在一张表中，定义数据缓存在内存中。，其中range使用binary search查找对应的分区，hash使用hash search查找对应的分区，相比查询时通过约束过滤更加高效。
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------±------------------------------±------±---------
public | pathman_concurrent_part_tasks | view | postgres
public | pathman_config | table | postgres
public | pathman_config_params | table | postgres
public | pathman_partition_list | view | postgres
(4 rows)
pathman_config — main config storage
CREATE TABLE IF NOT EXISTS pathman_config (
partrel REGCLASS NOT NULL PRIMARY KEY, --主表OID
attname TEXT NOT NULL, --分区列名
parttype INTEGER NOT NULL, --分区类型（RANGE OR HASH）
range_interval TEXT); --RANGE分区的间隔
pathman_config_params — optional parameters
CREATE TABLE IF NOT EXISTS pathman_config_params (
partrel REGCLASS NOT NULL PRIMARY KEY, --主表OID
enable_parent BOOLEAN NOT NULL DEFAULT TRUE, --是否在优化器中过滤主表
auto BOOLEAN NOT NULL DEFAULT TRUE, --insert时是否自动扩展分区
init_callback REGPROCEDURE NOT NULL DEFAULT 0,–create partition时的回调函数
spawn_using_bgw BOOLEAN NOT NULL DEFAULT FALSE);
pathman_concurrent_part_tasks — currently running partitioning workers
– helper SRF function
CREATE OR REPLACE FUNCTION show_concurrent_part_tasks()
RETURNS TABLE (
userid REGROLE,
pid INT,
dbid OID,
relid REGCLASS,
processed INT,
status TEXT)
AS ‘pg_pathman’, ‘show_concurrent_part_tasks_internal’
LANGUAGE C STRICT;
CREATE OR REPLACE VIEW pathman_concurrent_part_tasks
AS SELECT * FROM show_concurrent_part_tasks();
pathman_partition_list — list of all existing partitions
– helper SRF function
CREATE OR REPLACE FUNCTION show_partition_list()
RETURNS TABLE (
parent REGCLASS,
partition REGCLASS,
parttype INT4,
partattr TEXT,
range_min TEXT,
range_max TEXT)
AS ‘pg_pathman’, ‘show_partition_list_internal’
LANGUAGE C STRICT;
CREATE OR REPLACE VIEW pathman_partition_list
AS SELECT * FROM show_partition_list();
pg_pathman 原理
pg_pathman模块提供了很多函数功能来管理分区，方便快捷创建管理分区。pg_pathman与传统的继承分区表做法有一个不同的地方，分区的定义存放在一张元数据表中，表的信息会cache在内存中，同时使用HOOK来实现RELATION的替换，所以效率非常高。
pg_pathman 用到的hook如下
-
pg_pathman uses ProcessUtility_hook hook to handle COPY queries for partitioned tables.
-
RuntimeAppend (overrides Append plan node)
-
RuntimeMergeAppend (overrides MergeAppend plan node)
-
PartitionFilter (drop-in replacement for INSERT triggers)
https://wiki.postgresql.org/wiki/CustomScanAPI
提供的函数
创建分区表
HASH 分区
语法：
create_hash_partitions(relation REGCLASS,
attribute TEXT,
partitions_count INTEGER,
partition_data BOOLEAN DEFAULT TRUE,
partition_names TEXT[] DEFAULT NULL,
tablespaces TEXT[] DEFAULT NULL)
参数： relation REGCLASS – 主表OID
attribute TEXT – 分区列名
partitions_count INTEGER – 创建多少个分区
partition_data BOOLEAN DEFAULT TRUE – 是否将数据从主表迁移到分区
RANGE 分区
RANGE分区创建方式有两种
(1)指定开始值，间隔值，分区个数
语法：
create_range_partitions(relation REGCLASS,
attribute TEXT,
start_value ANYELEMENT,
p_interval ANYELEMENT,
p_count INTEGER DEFAULT NULL
partition_data BOOLEAN DEFAULT TRUE)
参数： relation REGCLAS – 主表OID
attribute TEXT – 分区列名
start_value ANYELEMENT – 开始值
p_interval ANYELEMENT – 间隔；任意类型，适合任意类型的分区表
p_count INTEGER DEFAULT NULL – 分多少个区
语法：
create_range_partitions(relation REGCLASS,
attribute TEXT,
start_value ANYELEMENT,
p_interval INTERVAL,
p_count INTEGER DEFAULT NULL,
partition_data BOOLEAN DEFAULT TRUE)
参数： relation REGCLASS – 主表OID
attribute TEXT – 分区列名
start_value ANYELEMENT – 开始值
p_interval INTERVAL – 间隔；intel类型，用于时间分区表
p_count INTEGER DEFAULT NULL – 分多少个区
partition_data BOOLEAN DEFAULT TRUE – 是否将数据从主表迁移到分区
(2)指定开始值，结束值，间隔值
语法：
create_partitions_from_range(relation REGCLASS,
attribute TEXT,
start_value ANYELEMENT,
end_value ANYELEMENT,
p_interval ANYELEMENT,
partition_data BOOLEAN DEFAULT TRUE)
参数： relation REGCLASS – 主表OID
attribute TEXT – 分区列名
start_value ANYELEMENT – 开始值
end_value ANYELEMENT – 结束值
p_interval ANYELEMENT – 间隔；任意类型，适合任意类型的分区表
partition_data BOOLEAN DEFAULT TRUE – 是否将数据从主表迁移到分区
语法：
create_partitions_from_range(relation REGCLASS,
attribute TEXT,
start_value ANYELEMENT,
end_value ANYELEMENT,
p_interval INTERVAL,
partition_data BOOLEAN DEFAULT TRUE)
参数： relation REGCLASS – 主表OID
attribute TEXT – 分区列名
start_value ANYELEMENT – 开始值
end_value ANYELEMENT – 结束值
p_interval INTERVAL – 间隔；interval 类型，用于时间分区表
partition_data BOOLEAN DEFAULT TRUE – 是否将数据从主表迁移到分区
数据迁移
功能：
如果创建分区表时，未将主表数据迁移到分区，那么可以使用非堵塞式的迁移接口，将数据迁移到分区。
语法：
partition_table_concurrently(relation REGCLASS,
batch_size INTEGER DEFAULT 1000,
sleep_time FLOAT8 DEFAULT 1.0)
参数： relation REGCLASS, – 主表OID
batch_size INTEGER DEFAULT 1000, – 一个事务批量迁移多少数据
sleep_time FLOAT8 DEFAULT 1.0) – 获得行锁失败时，休眠多久再次获取，重试60次退出任务
分裂合并分区
说明：仅支持范围分区，合并分区时指定两个需要合并分区，必须为相邻分区
语法：
split_range_partition(partition REGCLASS,
split_value ANYELEMENT,
partition_name TEXT DEFAULT NULL)
参数： partition REGCLASS – 分区oid
split_value ANYELEMENT – 分裂值
partition_name TEXT DEFAULT NULL – 分裂后新增的分区表名
语法：
merge_range_partitions(partition1 REGCLASS, partition2 REGCLASS)
绑定分区
功能：将已有的表，绑定到已有的某个分区主表。已有的表与主表要保持一致的结构
语法：
attach_range_partition(relation REGCLASS,
partition REGCLASS,
start_value ANYELEMENT,
end_value ANYELEMENT)
参数： relation REGCLASS – 主表OID
partition REGCLASS – 分区表OID
start_value ANYELEMENT – 起始值
end_value ANYELEMENT – 结束值
解绑分区
功能：将分区从主表的继承关系中删除, 不删数据，删除继承关系，删除约束
语法：
detach_range_partition(partition REGCLASS)
参数： partition REGCLASS – 指定分区名，转换为普通表
删除分区
语法：
drop_range_partition(partition TEXT,
delete_data BOOLEAN DEFAULT TRUE)
参数： partition TEXT, – 分区名称
delete_data BOOLEAN DEFAULT TRUE) – 是否删除分区数据，如果false，表示分区数据迁移到主表
其他参数
功能：禁用主表
语法：
set_enable_parent(relation REGCLASS, value BOOLEAN)
功能：启用/禁用自动扩展分区（仅适用于RANGE分区）。默认情况下启用
语法：
set_auto(relation REGCLASS, value BOOLEAN)
说明：不建议开启自动扩展分区，如果插入范围阔度很大会插入很多分区
示例（范围分区）：
创建一张表：
CREATE TABLE journal (
id SERIAL,
dt TIMESTAMP NOT NULL,
level INTEGER,
msg TEXT);
– similar index will also be created for each partition
CREATE INDEX ON journal(dt);
– generate some data
INSERT INTO journal (dt, level, msg)
SELECT g, random() * 6, md5(g::text)
FROM generate_series(‘2015-01-01’::date, ‘2015-12-31’::date, ‘1 minute’) as g;
使用create_range_partitions（）函数创建分区，使每个分区将包含一天的数据：
SELECT create_range_partitions(‘journal’, ‘dt’, ‘2015-01-01’::date, ‘1 day’::interval);
它将创建365个分区，并将数据从父级移动到分区。
添加新分区：
– add new partition with specified range
SELECT add_range_partition(‘journal’, ‘2016-01-01’::date, ‘2016-01-07’::date);
– append new partition with default range
SELECT append_range_partition(‘journal’);
第一个创建一个指定范围的分区。第二个创建具有默认间隔的分区，并将其附加到分区列表。也可以将现有表作为分区附加。
CREATE FOREIGN TABLE journal_archive (
id INTEGER NOT NULL,
dt TIMESTAMP NOT NULL,
level INTEGER,
msg TEXT)
SERVER archive_server;
SELECT attach_range_partition(‘journal’, ‘journal_archive’, ‘2014-01-01’::date, ‘2015-01-01’::date);
重要提示：附加表的定义必须与现有分区表之一相匹配，包括已删除的列。
要合并到相邻的分区，使用merge_range_partitions（）函数：
SELECT merge_range_partitions(‘journal_archive’, ‘journal_1’);
要按值拆分分区，使用split_range_partition（）函数：
SELECT split_range_partition（‘journal_366’，‘2016-01-03’:: date）;
要分离分区，使用detach_range_partition（）函数：
SELECT detach_range_partition（‘journal_archive’）;
pg_pathman 与 传统分区表 性能对比
20分区/2000w数据量批量插入性能对比。
表结构如下：
postgres=# \d test_pg_part_pathman
Table “public.test_pg_part_pathman”
Column | Type | Modifiers
----------±----------------------------±----------
id | integer | not null
info | text |
crt_time | timestamp without time zone |
Indexes:
“test_pg_part_pathman_pkey” PRIMARY KEY, btree (id)
Number of child tables: 20 (Use \d+ to list them.)
测试结果：
单行记录性能对比：
结果立竿见影，pg_pathman的性能远远高于传统分区表。
相关链接：
https://github.com/postgrespro/pg_pathman
http://www.tuicool.com/sites/nq67bey
https://yq.aliyun.com/articles/62314
PostgreSQL 10
PostgreSQL 9.6 Beta1 已经集成了内置分区表功能，支持range和list分区，内置分区直接使用插入更新删除接口处理，节省了parser和optimize的过程，比触发器转成SQL更高效。
功能（9.6 Beta1）
创建主表
CREATE TABLE table_name ( … ) PARTITION BY RANGE(column1, column2, … );
CREATE TABLE table_name ( … ) PARTITION BY LIST(column1, column2, … );
创建分区
CREATE TABLE partition_name PARTITION OF table_name FOR
VALUES START (value1, value2, …)/UNBOUNDED EXCLUSIVE/INCLUSIVE
END (values1, values2, …)/UNBOUNDED EXCLUSIVE/INCLUSIVE;
CREATE TABLE partition_name PARTITION OF table_name FOR
VALUES IN (value1, value2, …);
说明：
UNBOUNDED关键字表示无限大或无限小。
EXCLUSIVE 表示约束范围不包括上下限值，INCLUSIVE包括。默认START是INCLUSIVE，END是EXCLUSIVE。
内置分区也是在继承基础上实现的。
绑定分区
ALTER TABLE table_name ATTACH PARTITION table_name1 FOR
VALUES START (value1, value2, …)/UNBOUNDED EXCLUSIVE/INCLUSIVE
END (values1, values2, …)/UNBOUNDED EXCLUSIVE/INCLUSIVE VALIDATE/NO VALIDATE;
ALTER TABLE table_name ATTACH PARTITION table_name1 FOR
VALUES IN (value1, value2, …) VALIDATE/NO VALIDATE;
解绑分区
ALTER TABLE table_name DETACH PARTITION table_name1;
删除分区
ALTER TABLE table_name DETACH PARTITION table_name1;
DROP TABLE table_name1;
说明：
内置分区不能直接删除分区，需要先DETACH，再以普通表删除。
分区支持对比
PostgreSQL 10支持range,list分区表，同时hash分区处于POC阶段（同时还有一些需要改进的地方，例如优化器部分）。
以下是分区类型的不同支持：
分区类型的不同支持
RANGE LIST HASH
传统分区表 支持 支持
pg_pathman 支持 支持
9.6 Beta1 支持 支持
Postgres 10.0 支持 支持 支持
PostgreSQL 10 支持更多的类型的分区，还将支持外部表作为分区，10的分区功能很值得期待…
COPY 500W 记录的性能对比（pg 9.6 Beta1）
表结构：
CREATE TABLE test
(
id int,
info text,
crt_time timestamp
) PARTITION BY RANGE (id);
测试结果：
Pg_pathman 与内置分区（9.6 Beta1）性能对比
下面测试他们的插入速度，10分区/100W数据量。
表结构如下：
create table tb_test
(
id int not null,
firstname text,
lastname text,
corp text,
createtime timestamp without time zone
) partition by range(id);
测试结果如下：
可以发现pg_pathman与9.6 Bate1内置分区差距不大。毕竟还只是测试版，在pg 10 中性能会更加优越。
相关链接：
https://wiki.postgresql.org/wiki/Table_partitioning?spm=5176.100239.blogcont54456.9.6uwR4m
https://yq.aliyun.com/articles/54456
https://yq.aliyun.com/articles/72307?spm=5176.100240.searchblog.181.44dwYe
https://yq.aliyun.com/articles/72306?spm=5176.100240.searchblog.108.M0bAII
https://yq.aliyun.com/articles/72296?spm=5176.100240.searchblog.147.M0bAII
总结
这里简单介绍了PostgreSQL分区的概念，传统分区表的实现以及约束排除，分区功能扩展pg_pathman的原理，高效的性能以及提供的分区管理功能，并与传统分区表做了性能对比，pg内置分区与传统分区，pg_pathman的性能对比。