注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL10:重量级新特性-支持分区表  

2017-05-21 12:34:52|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
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_name
   PARTITION 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 row
DETAIL:  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 | 1
 24796 | 2016-01-01 01:00:00 | 1
 24797 | 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 ms
 Execution 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

备注:删除对就分区表即可。

三、参考
  评论这张
 
阅读(625)| 评论(1)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017