PostgreSQL传统分区表

创建传统分区表流程

在PostgreSQL10 以前，分区功能比较复杂，需要以下流程：

1. 创建父表
2. 创建子表
3. 给子表添加约束
4. 给子表添加索引
5. 在父表上定义触发器
6. 启用constraint_exclusion参数，提高性能。

使用传统分区表注意事项

1. 仅支持范围分区和列表分区
2. 必须是现在父表上创建路由函数和触发器
3. 分区索引、约束需要单独命令创建，无法一步到位
4. 父子表可单独定义主键，因此会存在重复的可能。目前不支持全局主键
5. update数据时，使数据移动了分区，虽然可以通过触发器实现，但是会带来管理成本。

1. 创建父表

假设有一张订单明细表，需要按照年份进行分区

create table order_detail (id serial , 
	 					   order_info varchar(20) , 
	 					   order_date timestamp(0) with time zone) ;

2. 创建子表并添加约束

create table order_detail_2015 (check (order_date >= '2015-01-01' and order_date < '2016-01-01') ) inherits(order_detail) ;

create table order_detail_2016 (check (order_date >= '2016-01-01' and order_date < '2017-01-01') ) inherits(order_detail) ;

create table order_detail_2017 (check (order_date >= '2017-01-01' and order_date < '2018-01-01') ) inherits(order_detail) ;

create table order_detail_2018 (check (order_date >= '2018-01-01' and order_date < '2019-01-01') ) inherits(order_detail) ;

create table order_detail_2019 (check (order_date >= '2019-01-01' and order_date < '2020-01-01') ) inherits(order_detail) ;

create table order_detail_2020 (check (order_date >= '2020-01-01' and order_date < '2021-01-01') ) inherits(order_detail) ;

4. 给子表添加索引

create index  ind_order_detail_his  on  order_detail_his   using btree (order_date) ;   
 
create index  ind_order_detail_2015 on  order_detail_2015  using btree (order_date) ;   

create index  ind_order_detail_2016 on  order_detail_2016  using btree (order_date) ;   

create index  ind_order_detail_2017 on  order_detail_2017  using btree (order_date) ;   

create index  ind_order_detail_2018 on  order_detail_2018  using btree (order_date) ;   

create index  ind_order_detail_2019 on  order_detail_2019  using btree (order_date) ;   

create index  ind_order_detail_2020 on  order_detail_2020  using btree (order_date) ;

由于父表不纯粹数据，考科一不用再父表上创建索引

5. 在父表上定义触发器

创建触发器所使用的的规则函数，设置数据插入父表时的路由规则：

create or replace function fun_order_detail()
	returns trigger
as $$
begin
 	if (new.order_date < '2015-01-01' ) then 
 		insert into order_detail_his values (new.*);
 	elsif (new.order_date >= '2015-01-01' and new.order_date < '2016-01-01') then
 	    insert into  order_detail_2015   values (new.*);
 	elsif (new.order_date >= '2016-01-01' and new.order_date < '2017-01-01') then
 	    insert into  order_detail_2016   values (new.*);
 	elsif (new.order_date >= '2017-01-01' and new.order_date < '2018-01-01') then
 	    insert into  order_detail_2017   values (new.*);
 	elsif (new.order_date >= '2018-01-01' and new.order_date < '2019-01-01') then
 	    insert into  order_detail_2018   values (new.*);
 	elsif (new.order_date >= '2019-01-01' and new.order_date < '2020-01-01') then
 	    insert into  order_detail_2019   values (new.*);
 	elsif (new.order_date >= '2020-01-01' and new.order_date < '2021-01-01') then
 	    insert into  order_detail_2020   values (new.*);
 	else
 		raise exception 'order_date out of range.   Fix the fun_order_detail()  function !! ' ;
 	end if ;
 	return null;
 end;
 $$
 LANGUAGE plpgsql;

创建触发器：

create trigger tri_order_detail
	  before insert on order_detail for each row execute procedure fun_order_detail();

6. 启用constraint_exclusion参数

提高性能。

使用分区表

插入数据的时候，只写父表名字即可:

postgres=# insert into order_detail (id , order_date)
postgres-# values (1, '2015-10-01');
INSERT 0 0
postgres=# 
postgres=# insert into order_detail (id , order_date)
postgres-# values (1, '2020-10-01');
INSERT 0 0
postgres=# 
postgres=# 
postgres=# select * from order_detail;
 id | order_info |       order_date       
----+------------+------------------------
  1 |            | 2015-10-01 00:00:00+08
  1 |            | 2020-10-01 00:00:00+08
(2 rows)

那么数据有没有按照我们的规则，进入到相应的分区中呢？ 我们可以单独查看子表数据。

postgres=# select * from order_detail_2015;
 id | order_info |       order_date       
----+------------+------------------------
  1 |            | 2015-10-01 00:00:00+08
(1 row)

postgres=# 
postgres=# select * from order_detail_2016;
 id | order_info | order_date 
----+------------+------------
(0 rows)

postgres=# select * from order_detail_2020;
 id | order_info |       order_date       
----+------------+------------------------
  1 |            | 2020-10-01 00:00:00+08
(1 row)

管理分区表

添加分区

有两种方法：

• 方法一 ：跟创建分区表的方法一样
  1. create table order_detail_2021 … inherits(order_detail);
  2. create index …
  3. 更新触发器

这种方法直接，在创建分区是就将分区继承到父表了，如果中间步骤有错误，可能对生产系统带来影响。所以推荐方法二。

• 方法二：

1. 创建分区表，但此时仅表结构与父表一样。两张表并无关系
create table order_detail_2021 (like order_detail including all);

2. 为子表添加约束
alter table order_detail_2021 add constraint order_detail_2021_check check (order_date >= '2021-01-01' and order_date < '2022-01-01');

3. 更新触发器

4. 讲子表加入到父表大军中
alter table order_detail_2021 inherit order_detail;

• 检查数据

postgres=# insert into order_detail (id , order_date)
postgres-# values (1, '2021-10-01');
INSERT 0 0
postgres=# 
postgres=# 
postgres=# select * from order_detail_2021;
 id | order_info |       order_date       
----+------------+------------------------
  1 |            | 2021-10-01 00:00:00+08
(1 row)

postgres=# 
postgres=# select * from order_detail;
 id | order_info |       order_date       
----+------------+------------------------
  1 |            | 2015-10-01 00:00:00+08
  1 |            | 2020-10-01 00:00:00+08
  1 |            | 2021-10-01 00:00:00+08

删除分区

方法1. 删除子表

postgres=# drop table order_detail_2021;
DROP TABLE

方法2. 去掉继承关系(推荐）

postgres=# alter table order_detail_2021 no inherit order_detail;

postgres=# \dt+ order*
                            List of relations
 Schema |       Name        | Type  |  Owner   |    Size    | Description 
--------+-------------------+-------+----------+------------+-------------
 public | order_detail      | table | postgres | 0 bytes    | 
 public | order_detail_2015 | table | postgres | 8192 bytes | 
 public | order_detail_2016 | table | postgres | 0 bytes    | 
 public | order_detail_2017 | table | postgres | 0 bytes    | 
 public | order_detail_2018 | table | postgres | 0 bytes    | 
 public | order_detail_2019 | table | postgres | 0 bytes    | 
 public | order_detail_2020 | table | postgres | 8192 bytes | 
(7 rows)

分区表相关查询

查询分区表使用情况

postgres=# \dt+ order*
                            List of relations
 Schema |       Name        | Type  |  Owner   |    Size    | Description 
--------+-------------------+-------+----------+------------+-------------
 public | order_detail      | table | postgres | 0 bytes    | 
 public | order_detail_2015 | table | postgres | 8192 bytes | 
 public | order_detail_2016 | table | postgres | 0 bytes    | 
 public | order_detail_2017 | table | postgres | 0 bytes    | 
 public | order_detail_2018 | table | postgres | 0 bytes    | 
 public | order_detail_2019 | table | postgres | 0 bytes    | 
 public | order_detail_2020 | table | postgres | 8192 bytes | 
(7 rows)