文档章节

PostgreSQL 分区表, pg_pathman ,PostgreSQL 10介绍及性能对比

yonj1e
 yonj1e
发布于 2017/03/27 15:23
字数 5231
阅读 1164
收藏 2

简介

在数据库日渐庞大的今天,为了方便对数据库数据的管理,比如按时间,按地区去统计一些数据时,基数过于庞大,多有不便。很多商业数据库都提供分区的概念,按不同的维度去存放数据,便于后期的管理,PostgreSQL也不例外。

  PostgresSQL分区的意思是把逻辑上的一个大表分割成物理上的几块儿。分区不仅能带来访问速度的提升,关键的是,它能带来管理和维护上的方便。

分区的具体好处是:

  1. 改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度
  2. 增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用
  3. 维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可
  4. 均衡I/O:可以把不同的分区映射到不同磁盘以平衡I/O,改善整个系统性能

表分区介绍

数据库表分区把一个大的物理表分成若干个小的物理表,并使得这些小物理表在逻辑上可以被当成一张表来使用。

  1. 主表 / 父表 / Master Table 该表是创建子表的模板。它是一个正常的普通表,但正常情况下它并不储存任何数据。
  2. 子表 / 分区表 / Child Table / Partition Table 这些表继承并属于一个主表。子表中存储所有的数据。主表与分区表属于一对多的关系,也就是说,一个主表包含多个分区表,而一个分区表只从属于一个主表

传统分区表

现在PostgreSQL支持通过表继承来实现表的分区。父表是普通表并且正常情况下并不存储任何数据,它的存在只是为了代表整个数据集。PostgreSQL可实现如下两种表分区

  1. 范围分区 每个分区表包含一个或多个字段组合的一部分,并且每个分区表的范围互不重叠。比如可近日期范围分区
  2.  列表分区 分区表显示列出其所包含的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, ....);

说明:

  1. UNBOUNDED关键字表示无限大或无限小。
  2. EXCLUSIVE 表示约束范围不包括上下限值,INCLUSIVE包括。默认START是INCLUSIVE,END是EXCLUSIVE。
  3. 内置分区也是在继承基础上实现的。

绑定分区

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

© 著作权归作者所有

上一篇: 进制转换函数
下一篇: 博客迁移
yonj1e
粉丝 18
博文 20
码字总数 31358
作品 0
济南
后端工程师
私信 提问
PostgreSQL 9.x, 10, 11 hash分区表 用法举例

标签 PostgreSQL , 分区表 , 优化器 , 分区过滤 , hash 分区 背景 PostgreSQL 10开始内置分区表语法,当时只支持了range,list两种分区,实际上可以通过LIST实现HASH分区。 PostgreSQL 10 ha...

德哥
2018/07/28
0
0
PostgreSQL 自动创建分区实践 - 写入触发器

标签 PostgreSQL , 自动创建分区 , 触发器 , 写入 , 动态创建分区 背景 数据写入时,自动创建分区。 目前pg_pathman这个分区插件,有这个功能,如果你不是用的这个插件,可以考虑一下用触发器...

德哥
2018/06/21
0
0
PostgreSQL 11 preview - Faster partition pruning

标签 PostgreSQL , 分区 , 继承 , constraint_exclusion 背景 目前PG社区版本内置分区表的性能受制于一些BIND、LOCK所有子表等问题,当分区过多时,性能会受到极大的影响。 《分区表锁粒度差...

德哥
2018/05/06
0
0
PostgreSQL pgbench tpcb 海量数据库测试 - 分区表测试优化

标签 PostgreSQL , pgbench , tpcb 背景 pgbench是PG的一款测试工具,内置的测试CASE为tpcb测试。同时支持用户自己写测试CASE。 大量自定义CASE参考 https://github.com/digoal/blog/blob/ma...

德哥
04/14
0
0
PostgreSQL HooK 介绍

标签 PostgreSQL , hook 背景 PostgreSQL 的HOOK机制,结合PostgreSQL的PGinit与PGfini两个初始化函数(加载SO时自动load PGinit(), 退出会话时自动加载PGfini()),使得用户可以在不修改源码...

德哥
2018/06/29
0
0

没有更多内容

加载失败,请刷新页面

加载更多

OSChina 周五乱弹 ——不知道假装开心,装的像么

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 @巴拉迪维 :天黑了 你很忧愁, 你说世界上, 找不到四块五的妞, 行走在凌晨两点的马路上, 你疲倦地拿着半盒黄鹤楼。#今日歌曲推荐# 《四块...

小小编辑
今天
2.4K
18
64.监控平台介绍 安装zabbix 忘记admin密码

19.1 Linux监控平台介绍 19.2 zabbix监控介绍 19.3/19.4/19.6 安装zabbix 19.5 忘记Admin密码如何做 19.1 Linux监控平台介绍: 常见开源监控软件 ~1.cacti、nagios、zabbix、smokeping、ope...

oschina130111
昨天
69
0
当餐饮遇上大数据,嗯真香!

之前去开了一场会,主题是「餐饮领袖新零售峰会」。认真听完了餐饮前辈和新秀们的分享,觉得获益匪浅,把脑子里的核心纪要整理了一下,今天和大家做一个简单的分享,欢迎感兴趣的小伙伴一起交...

数澜科技
昨天
34
0
DNS-over-HTTPS 的下一代是 DNS ON BLOCKCHAIN

本文作者:PETER LAI ,是 Diode 的区块链工程师。在进入软件开发领域之前,他主要是在做工商管理相关工作。Peter Lai 也是一位活跃的开源贡献者。目前,他正在与 Diode 团队一起开发基于区块...

红薯
昨天
148
0
CC攻击带来的危害我们该如何防御?

随着网络的发展带给我们很多的便利,但是同时也带给我们一些网站安全问题,网络攻击就是常见的网站安全问题。其中作为站长最常见的就是CC攻击,CC攻击是网络攻击方式的一种,是一种比较常见的...

云漫网络Ruan
昨天
32
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部