PostgreSQL内置分区表

PostgreSQL重量级新特性是内置分区表，用户不需要预先在父表上定义INSERT、 DELETE、 UPDATE触发器，对父表的DML操作会自动路由到相应分区，相比传统分区表大幅度降低了维护成本

创建分区表

创建分区表的主要语法包含两部分：创建主表和创建分区。
创建主表时须指定分区方式，可选的分区方式为RANGE范围分区或LIST列表分区，并指定字段或表达式作为分区键。
创建分区时必须指定是哪张表的分区，同时指定分区策略partition_bound_ spec，如果是范围分区，partition_bound_spec须指定每个分区分区键的取值范围，如果是列表分区partition_ bound_ spec，需指定每个分区的分区键值。

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

CREATE TABLE log_par( 
id serial , 
user_id int4 , 
create_time timestamp(0) without time zone 
) PARTITION BY RANGE(create_time); 

2 创建分区，创建分区时须指定分区表的父表和分区键的取值范围，注意分区键的范围不要有重叠，否则会报错。

表log_par指定了分区策略为范围分区，分区键为create_time宇段。
创建分区，并设置分区的分区键取值范围

create table log_par_his partition of log_par for values from (MINVALUE) to ('2020-01-01'); 
create table log_par_202001 partition of log_par for values from ('2020-01-01') to (' 2020-02-01'); 
create table log_par_202002 partition of log_par for values from ('2020-02-01') to (' 2020-03-01');
create table log_par_202003 partition of log_par for values from ('2020-03-01') to (' 2020-04-01');
create table log_par_202004 partition of log_par for values from ('2020-04-01') to (' 2020-05-01');
create table log_par_202005 partition of log_par for values from ('2020-05-01') to (' 2020-06-01');
create table log_par_202006 partition of log_par for values from ('2020-06-01') to (' 2020-07-01');
create table log_par_202007 partition of log_par for values from ('2020-07-01') to (' 2020-08-01');
create table log_par_202008 partition of log_par for values from ('2020-08-01') to (' 2020-09-01');
create table log_par_202009 partition of log_par for values from ('2020-09-01') to (' 2020-10-01');
create table log_par_202010 partition of log_par for values from ('2020-10-01') to (' 2020-11-01');
create table log_par_202011 partition of log_par for values from ('2020-11-01') to (' 2020-12-01');
create table log_par_202012 partition of log_par for values from ('2020-12-01') to (' 2021-01-01');

/*MINVALUE原为UNBOUNDED，含义是没有边界，该常量在本版中无效*/

3 在分区上创建相应索引

通常情况下分区键上的索引是必须的，非分区键的索引可根据实际应用场景选择是否创建。

CREATE INDEX idx_log_par_his_ctime ON log_par_his USING btree(create_time);
CREATE INDEX idx_log_par_202001_ctime ON log_par_202001 USING btree(create_time); 
CREATE INDEX idx_log_par_202002_ctime ON log_par_202002 USING btree(create_time);
CREATE INDEX idx_log_par_202003_ctime ON log_par_202003 USING btree(create_time);
CREATE INDEX idx_log_par_202004_ctime ON log_par_202004 USING btree(create_time);
CREATE INDEX idx_log_par_202005_ctime ON log_par_202005 USING btree(create_time);
CREATE INDEX idx_log_par_202006_ctime ON log_par_202006 USING btree(create_time);
CREATE INDEX idx_log_par_202007_ctime ON log_par_202007 USING btree(create_time);
CREATE INDEX idx_log_par_202008_ctime ON log_par_202008 USING btree(create_time);
CREATE INDEX idx_log_par_202009_ctime ON log_par_202009 USING btree(create_time);
CREATE INDEX idx_log_par_202010_ctime ON log_par_202010 USING btree(create_time);
CREATE INDEX idx_log_par_202011_ctime ON log_par_202011 USING btree(create_time);
CREATE INDEX idx_log_par_202012_ctime ON log_par_202012 USING btree(create_time);

向分区表插入数据

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

查看分区

\dt+ log_par*
                        List of relations
 Schema |      Name      | Type  | Owner |  Size   | Description 
--------+----------------+-------+-------+---------+-------------
 public | log_par        | table | postgres  | 0 bytes | 
 public | log_par_202001 | table | postgres  | 2016 kB | 
 public | log_par_202002 | table | postgres  | 1920 kB | 
 public | log_par_202003 | table | postgres  | 2016 kB | 
 public | log_par_202004 | table | postgres  | 1984 kB | 
 public | log_par_202005 | table | postgres  | 2016 kB | 
 public | log_par_202006 | table | postgres  | 1984 kB | 
 public | log_par_202007 | table | postgres  | 2016 kB | 
 public | log_par_202008 | table | postgres  | 2016 kB | 
 public | log_par_202009 | table | postgres  | 1984 kB | 
 public | log_par_202010 | table | postgres  | 2016 kB | 
 public | log_par_202011 | table | postgres  | 1984 kB | 
 public | log_par_202012 | table | postgres  | 1984 kB | 
 public | log_par_his    | table | postgres  | 2016 kB | 

添加分区

给log_par增加一个分区

create table log_par_202101 partition of log_par for values from ('2021-01-01')to ('2021-02-01'); 

给分区创建索引

CREATE INDEX idx_log_par_20210_ctime ON log_par_202101 USING btree(create_time) ; 

删除分区

DROP分区方式删除

DROP TABLE log_par_202101; 

DROP方式直接将分区和分区数据删除，删除前需确认分区数据是否需要备份，避免数据丢失；

解绑分区

ALTER TABLE log_par DETACH PARTITION log_par_202101; 

解绑分区只是将分区和父表间的关系断开，分区和分区数据依然保留，这种方式比较稳妥，如果后续需要恢复这个分区，通过连接分区方式恢复分区即可

ALTER TABLE log_par ATTACH PARTITION log_par_202101 FOR VALUES FROM ('2021-01-01') TO ('2021-02-01');

连接分区时需要指定分区上的约束。

内置分区相比传统分区，通过简化管理、提升查询性能、支持自动分区裁剪以及优化分区操作，提供了更高的可维护性和更好的事务支持，尤其适合处理大数据量和复杂查询的场景。