PostgreSQL10：重量级新特性-支持分区表
PostgreSQL10 一个重量级新特性是支持分区表，在这之前，PostgreSQL不支持内置分区表，若要实现功能，需通过继承的方式实现，详见
PostgreSQL: 分区表应用二(取模分区) 。
PostgreSQL 内置分区表目前仅支持以下两种形式分区
范围分区（ Range Partitioning ）The table is partitioned into “ ranges ” defined by a key column or set of columns , with no overlap between the ranges of values assigned to different partitions . For example , one might partition by date ranges , or by ranges of identifiers for particular business objects .
列表分区（ List Partitioning ）The table is partitioned by explicitly listing which key values appear in each partition .
分区表语法
CREATE TABLE table_name ( ... )[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) }CREATE TABLE table_namePARTITION OF parent_table [ () ] FOR VALUES partition_bound_spec
一、下面通过一个例子演示下：创建一个范围分区的分区表
--创建父表
create table tmp_log (id serial ,create_time timestamp ( 0 ) without time zone ,remark char ( 1 )) partition by RANGE ( create_time );
CREATE TABLE tmp_log_p2016_befor PARTITION OF tmp_log FOR VALUES FROM ( UNBOUNDED ) TO ( '2016-01-01' );CREATE TABLE tmp_log_p201601 PARTITION OF tmp_log FOR VALUES FROM ( '2016-01-01' ) TO ( '2016-02-01' );CREATE TABLE tmp_log_p201602 PARTITION OF tmp_log FOR VALUES FROM ( '2016-02-01' ) TO ( '2016-03-01' );CREATE TABLE tmp_log_p201603 PARTITION OF tmp_log FOR VALUES FROM ( '2016-03-01' ) TO ( '2016-04-01' );CREATE TABLE tmp_log_p201604 PARTITION OF tmp_log FOR VALUES FROM ( '2016-04-01' ) TO ( '2016-05-01' );CREATE TABLE tmp_log_p201605 PARTITION OF tmp_log FOR VALUES FROM ( '2016-05-01' ) TO ( '2016-06-01' );CREATE TABLE tmp_log_p201606 PARTITION OF tmp_log FOR VALUES FROM ( '2016-06-01' ) TO ( '2016-07-01' );CREATE TABLE tmp_log_p201607 PARTITION OF tmp_log FOR VALUES FROM ( '2016-07-01' ) TO ( '2016-08-01' );CREATE TABLE tmp_log_p201608 PARTITION OF tmp_log FOR VALUES FROM ( '2016-08-01' ) TO ( '2016-09-01' );CREATE TABLE tmp_log_p201609 PARTITION OF tmp_log FOR VALUES FROM ( '2016-09-01' ) TO ( '2016-10-01' );CREATE TABLE tmp_log_p201610 PARTITION OF tmp_log FOR VALUES FROM ( '2016-10-01' ) TO ( '2016-11-01' );CREATE TABLE tmp_log_p201611 PARTITION OF tmp_log FOR VALUES FROM ( '2016-11-01' ) TO ( '2016-12-01' );CREATE TABLE tmp_log_p201612 PARTITION OF tmp_log FOR VALUES FROM ( '2016-12-01' ) TO ( '2017-01-01' );CREATE TABLE tmp_log_p201701 PARTITION OF tmp_log FOR VALUES FROM ( '2017-01-01' ) TO ( '2017-02-01' );CREATE TABLE tmp_log_p201702 PARTITION OF tmp_log FOR VALUES FROM ( '2017-02-01' ) TO ( '2017-03-01' );
--创建索引
create index idx_tmp_log_p2016_befor_ctime on tmp_log_p2016_befor using btree ( create_time );create index idx_tmp_log_p201601_ctime on tmp_log_p201601 using btree ( create_time );create index idx_tmp_log_p201602_ctime on tmp_log_p201602 using btree ( create_time );create index idx_tmp_log_p201603_ctime on tmp_log_p201603 using btree ( create_time );create index idx_tmp_log_p201604_ctime on tmp_log_p201604 using btree ( create_time );create index idx_tmp_log_p201605_ctime on tmp_log_p201605 using btree ( create_time );create index idx_tmp_log_p201606_ctime on tmp_log_p201606 using btree ( create_time );create index idx_tmp_log_p201607_ctime on tmp_log_p201607 using btree ( create_time );create index idx_tmp_log_p201608_ctime on tmp_log_p201608 using btree ( create_time );create index idx_tmp_log_p201609_ctime on tmp_log_p201609 using btree ( create_time );create index idx_tmp_log_p201610_ctime on tmp_log_p201610 using btree ( create_time );create index idx_tmp_log_p201611_ctime on tmp_log_p201611 using btree ( create_time );create index idx_tmp_log_p201612_ctime on tmp_log_p201612 using btree ( create_time );create index idx_tmp_log_p201701_ctime on tmp_log_p201701 using btree ( create_time );create index idx_tmp_log_p201702_ctime on tmp_log_p201702 using btree ( create_time );
备注：主要通过以上三步完成分区表创建，注意 constraint_exclusion 设备成 partition ；
目前分区上的索引、约束、主键需要使用单独的命令创建。
--插入测试数据
francs => insert into tmp_log ( create_time , remark )select generate_series ( '2015-11-01' :: date , '2017-02-28' :: date , '1 hour' ), '1' ;INSERT 0 11641
备注：利用
generate_series 函数生成时间戳测试数据，一天插入24条数据，测试样例数据如下。
--数据插入溢出
francs => insert into tmp_log ( create_time , remark )francs -> select generate_series ( '2015-11-01' :: date , '2018-01-01' :: date , '1 hour' ), '1' ;ERROR : no partition of relation "tmp_log" found for rowDETAIL : Partition key of the failing row contains ( create_time ) = ( 2017 - 03 - 01 00 : 00 : 00 ).
备注：如果插入的数据没有对应的分区，报如上错误。
--查看数据量
francs => select * from tmp_log limit 3 ;id | create_time | remark-------+---------------------+--------24795 | 2016 - 01 - 01 00 : 00 : 00 | 124796 | 2016 - 01 - 01 01 : 00 : 00 | 124797 | 2016 - 01 - 01 02 : 00 : 00 | 1( 3 rows )
francs => select count (*) from tmp_log ;count-------11641( 1 row )
francs => select count (*) from tmp_log_p201601 ;count-------744( 1 row )
francs => select count (*) from tmp_log_p201602 ;count-------696( 1 row )
francs => select count (*) from tmp_log_p201609 ;count-------720( 1 row )
--查看执行计划
francs => explain analyze select * from tmp_log where create_time > '2016-01-01' and create_time < '2016-01-02' ;QUERY PLAN-----------------------------------------------------------------------------------------------------------------------------------------------------------------Append ( cost = 0.15 .. 4.63 rows = 24 width = 14 ) ( actual time = 0.009 .. 0.015 rows = 23 loops = 1 )-> Index Scan using idx_tmp_log_p201601_ctime on tmp_log_p201601 ( cost = 0.15 .. 4.63 rows = 24 width = 14 ) ( actual time = 0.008 .. 0.012 rows = 23 loops = 1 )Index Cond : (( create_time > '2016-01-01 00:00:00' :: timestamp without time zone ) AND ( create_time < '2016-01-02 00:00:00' :: timestamp without time zone ))Planning time : 0.511 msExecution time : 0.037 ms( 5 rows )
备注：从PLAN看出，索引扫描指定分区表 tmp_log_p201601。
二 分区表管理
--分区表管理：断开分区
francs => alter table tmp_log DETACH PARTITION tmp_log_p201702 ;ALTER TABLE
备注：DETACH 操作是指将分区从分区表断开，类似从一列火车中断开一节车厢类似，这个表将转变成普通表，仍然可读写。
--分区表管理：连接分区
francs => alter table tmp_log ATTACH PARTITION tmp_log_p201702 FOR VALUES FROM ( '2017-02-01' ) TO ( '2017-03-01' );ALTER TABLE
备注：ATTACH 操作是指将普通表连接到指定分区表，有一点要注意，ATTACH 和 DETACH 操作过程中，
会在父表、此张分区表上加上 AccessExclusiveLock 排它锁，因此分区表的这两个操作应该在业务低谷时进行，避免影响业务。
--分区表管理：删除分区
francs => drop table tmp_log_p201702 ;DROP TABLE
备注：删除对就分区表即可。
三、参考
5834
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
