当前位置: 首页 > 工具软件 > pg_pathman > 使用案例 >

PostgreSQL 分区表, pg_pathman ,PostgreSQL 10介绍及性能对比(转载)

孟成文
2023-12-01

转载自: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如下

  1. pg_pathman uses ProcessUtility_hook hook to handle COPY queries for partitioned tables.

  2. RuntimeAppend (overrides Append plan node)

  3. RuntimeMergeAppend (overrides MergeAppend plan node)

  4. 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的性能对比。

 类似资料: