Postgresql内置分区表的使用总结
Postgresql内置分区表的使用总结
内置分区表简介
作者：yuxiaoyanranxt
在postgresql10之后，引入了内置分区表，用户不需要先在父表上定义insert,update,delete触发器，对父表的DML操作会自动路由到相应分区，相比传统分区表大幅度降低了维护成本，目前内置分区表仅支持范围分区和列表分区。
1.1 创建分区表创建分区表分为两个部分：创建主表和创建分区。
1.1.1 创建主表创建主表时须指定分区方式，可选的分区方式为RANGE范围分区或LIST列表分区，并指定字段或表达式作为分区键
// create table
create table table_name (...)
[partition by {RANGE|LIST }]({column_name |(express)})
1.1.2创建分区
创建分区时必须指定是哪张表的分区，同事指定分区策略partition_bound_spec,如果是范围分区，partition_bound_spec须指定每个分区分区键的取值范围，如果是列表分区，需指定每个分区的分区键值
//create table
create table table_name partition of parent_table[()]
for values partition_bound_spec
1.1.3在分区上创建对应的索引
通常情况下分区键上的索引是必须的，非分区键的索引可根据实际应用场景选择是否创建
1.1.4示例过程--创建父表
create table log_all (id serial,user_id int4,create_time timestamp(0) without time zone)
partition by range(create_time);
--创建四个分区
--注意分区范围不要重叠，不然会报错
create table log_all_his partition of log_all for values from (minvalue) to ('2020-01-01') ;
create table log_all_202001 partition of log_all for values from ('2020-01-01') to ('2020-01-31') ;
create table log_all_202002 partition of log_all for values from ('2017-02-01') to ('2017-02-28') ;
create table log_all_202003 partition of log_all for values from ('2020-03-01') to ('2020-03-31') ;
--创建索引
create index idx_log_all_his_ctime on log_all_his using btree (create_time);
create index idx_log_all_202001_ctime on log_all_his using btree (create_time);
create index idx_log_all_202002_ctime on log_all_his using btree (create_time);
create index idx_log_all_202003_ctime on log_all_his using btree (create_time);
--插入数据
insert into log_par(id,user_id,create_time) values (1,1,'2020-01-01');
insert into log_par(id,user_id,create_time) values (2,2,'2020-02-01');
--该条数据由于不在分区范围，insert操作时会报错
insert into log_par(id,user_id,create_time) values (3,3,'2020-06-01');
--查询数据
select * from log_all;
select * from only log_all;--可以看出log_par表里没有存储任何数据
\dt* log_par*
--添加分区
--删除分区
drop table log_all_his;
--解绑分区
alter table log_all_his detach partition log_all;
--重新绑到父表
alter table log_all attach partition log_all_his
for values from (minvalue) to ('2020-01-01');
关于内置分区表，传统分区表与普通表的性能差别，后续会继续学习记录。
以上内容仅作自我学习记录，如果错误，欢迎指正。
作者：yuxiaoyanranxt
