PG分区之内置分区

PG10支持范围分区和列表分区
PostgreSQL10创建内置分区表主要分为以下几个步骤:
1）创建父表，指定分区键和分区策略。
2）创建分区，创建分区时须指定分区表的父表和分区键的取值范围，注意分区键的范围不要有重叠，否则会报错。
3）在分区上创建相应索引，通常情况下分区键上的索引是必须的，非分区键的索引可根据实际应用场景选择是否创建。

范围分区,创建分区表

--创建父表，指定分区键和分区策略
CREATE TABLE log_par (
id serial,
user_id int4,
create_time timestamp(0) without time zone
)PARTITION BY RANGE(create_time);

--创建分区，创建分区时须指定分区表的父表和分区键的取值范围，注意分区键的范围不要有重叠，否则会报错。
CREATE TABLE log_par_his PARTITION OF log_par FOR VALUES FROM ('2016-12-01') TO ('2017-01-01');
CREATE TABLE log_par_201701 PARTITION OF log_par FOR VALUES FROM ('2017-01-01') TO ('2017-02-01');
CREATE TABLE log_par_201702 PARTITION OF log_par FOR VALUES FROM ('2017-02-01') TO ('2017-03-01');
CREATE TABLE log_par_201703 PARTITION OF log_par FOR VALUES FROM ('2017-03-01') TO ('2017-04-01');
CREATE TABLE log_par_201704 PARTITION OF log_par FOR VALUES FROM ('2017-04-01') TO ('2017-05-01');
CREATE TABLE log_par_201705 PARTITION OF log_par FOR VALUES FROM ('2017-05-01') TO ('2017-06-01');
CREATE TABLE log_par_201706 PARTITION OF log_par FOR VALUES FROM ('2017-06-01') TO ('2017-07-01');
CREATE TABLE log_par_201707 PARTITION OF log_par FOR VALUES FROM ('2017-07-01') TO ('2017-08-01');
CREATE TABLE log_par_201708 PARTITION OF log_par FOR VALUES FROM ('2017-08-01') TO ('2017-09-01');
CREATE TABLE log_par_201709 PARTITION OF log_par FOR VALUES FROM ('2017-09-01') TO ('2017-10-01');
CREATE TABLE log_par_201710 PARTITION OF log_par FOR VALUES FROM ('2017-10-01') TO ('2017-11-01');
CREATE TABLE log_par_201711 PARTITION OF log_par FOR VALUES FROM ('2017-11-01') TO ('2017-12-01');
CREATE TABLE log_par_201712 PARTITION OF log_par FOR VALUES FROM ('2017-12-01') TO ('2018-01-01');

--在分区上创建相应索引，通常情况下分区键上的索引是必须的，非分区键的索引可根据实际应用场景选择是否创建。
CREATE INDEX idx_log_par_his_ctime ON log_par_his USING btree(create_time);
CREATE INDEX idx_log_par_201701_ctime ON log_par_201701 USING btree(create_time);
CREATE INDEX idx_log_par_201702_ctime ON log_par_201702 USING btree(create_time);
CREATE INDEX idx_log_par_201703_ctime ON log_par_201703 USING btree(create_time);
CREATE INDEX idx_log_par_201704_ctime ON log_par_201704 USING btree(create_time);
CREATE INDEX idx_log_par_201705_ctime ON log_par_201705 USING btree(create_time);
CREATE INDEX idx_log_par_201706_ctime ON log_par_201706 USING btree(create_time);
CREATE INDEX idx_log_par_201707_ctime ON log_par_201707 USING btree(create_time);
CREATE INDEX idx_log_par_201708_ctime ON log_par_201708 USING btree(create_time);
CREATE INDEX idx_log_par_201709_ctime ON log_par_201709 USING btree(create_time);
CREATE INDEX idx_log_par_201710_ctime ON log_par_201710 USING btree(create_time);
CREATE INDEX idx_log_par_201711_ctime ON log_par_201711 USING btree(create_time);
CREATE INDEX idx_log_par_201712_ctime ON log_par_201712 USING btree(create_time);

测试-插入数据

INSERT INTO log_par(user_id,create_time) SELECT round(100000000*random()),generate_series('2016-12-01'::date,'2017-12-01'::date,'1 minute');

select count(*) from log_par;
select count(*) from only log_par;
\dt+ log_par*
\d+ log_par

添加分区

CREATE TABLE log_par_201801 PARTITION OF log_par FOR VALUES FROM ('2018-01-01') TO ('2018-02-01');
CREATE INDEX idx_log_par_201801_ctime ON log_par_201801 USING btree (create_time);

删除分区

方法一：直接删除
drop table log_par_201801;
方法二： 分离
alter table log_par detach partition log_par_201801;

附加到表
alter table log_par attach partition log_par_201801 for values from ('2018-01-01') TO ('2018-02-01');

性能测试

explain analyze select * from log_par where create_time > '2017-01-01' and create_time < '2017-01-02';


CREATE INDEX idx_log_par_his_userid ON log_par_his using btree (user_id);
CREATE INDEX idx_log_par_201701_userid ON log_par_201701 using btree (user_id);
CREATE INDEX idx_log_par_201702_userid ON log_par_201702 using btree (user_id);

--场景一:根据user id检索
SELECT * FROM log WHERE user id=?;
SELECT * FROM log_par WHERE user id=?;


contraint_exclusion参数