postgresql之内置分区表

pg10目前仅支持内置分区，列表分区

创建内置分区表的几个步骤：

1.创建父表，指定分区键和分区策略

2.创建分区，创建分区时须指定分区表的父表和子表的取值范围，注意分区键的范围不要有重叠

3.在分区上创建对应的索引，通常分区键上创建索引是必须的，非分区键的索引可以根据业务操作

创建内置分区表

指定分区策略为范围分区，分区键为create_time

postgres=# create table log_par (id serial ,user_id int4,create_time timestamp(0) without time zone) partition by range(create_time);
CREATE TABLE

创建分区，并设置分区的分区键范围

postgres=# create table log_par201706 partition of log_par for values from ( '2017-06-01') to ('2017-07-01');
CREATE TABLE
postgres=# create table log_par201707 partition of log_par for values from ( '2017-07-01') to ('2017-08-01');
CREATE TABLE
postgres=# create table log_par201708 partition of log_par for values from ( '2017-08-01') to ('2017-09-01');
CREATE TABLE
postgres=# create table log_par201709 partition of log_par for values from ( '2017-09-01') to ('2017-10-01');
CREATE TABLE
postgres=# create table log_par201710 partition of log_par for values from ( '2017-10-01') to ('2017-11-01');
CREATE TABLE
postgres=# create table log_par201711 partition of log_par for values from ( '2017-11-01') to ('2017-12-01');
CREATE TABLE
postgres=# create table log_par201712 partition of log_par for values from ( '2017-12-01') to ('2018-01-01');
CREATE TABLE

给所有分区创建分区键创建索引

postgres=# create index idx_log_par_201701_ctime on log_par201701 using btree (create_time);
CREATE INDEX
postgres=# create index idx_log_par_201702_ctime on log_par201702 using btree (create_time);
CREATE INDEX
postgres=# create index idx_log_par_201703_ctime on log_par201703 using btree (create_time);
CREATE INDEX

插入数据

postgres=#  insert into log_par(user_id,create_time) select round(100000000*random()),generate_series('2016-12-01'::date,'2017-12-01'::date, '1 minute');
INSERT 0 525601

查看表如下

postgres=# select count(*) from log_par;
 count
--------
 525601
(1 row)

postgres=# select count(*) from only log_par;
 count
-------
     0
(1 row)

添加分区，给log_par增加一个分区

postgres=# create table log_par_201801 partition of log_par for values from ( '2018-01-01') to ('2018-02-01');
CREATE TABLE
创建索引
postgres=# create index idx_log_par_par_201801_ctime on log_par_201801 using btree (create_time);
CREATE INDEX

删除分区2种方法

drop table log_par_201801

另外一种解绑分区

alter table log_par attach partition log_par_201801 for values from ( '2018-01-01') to ('2018-02-01') ;

内置分区表注意事项：

1.往父表插入数据时，数据自动根据分区键路由规则插入到分区

2.分区表索引，约束需使用单独命令创建

3.不支持定义全局主键，在分区表的分区上创建主键还是可以的

4.内置分区表内部实现继承

5.update语句的新记录违反当前分区键的约束就会报错，不支持跨分区

6.性能比普通表有小幅下降