1 前言
当数据库中单表的数据量过大时，需要考虑分区来提高性能。对于PostgreSQL数据库的用户而言，如果想在PostgreSQL 9.x 或者更早的版本中使用分区表，你需要用触发器（Trigger）或者规则（Rule），加上检查约束（Check）和继承（Inheritance）机制去实现它。而PostgreSQL 在 10.0版本引入了声明式（declarative）分区。相比于基于“触发器+继承+检查约束” （以下简称基于触发器的方案）和基于“规则+继承+检查约束”的分区方案，声明式分区的在性能有了很大的提升。
（援引自平安科技工程师石勇虎的《庖丁解牛之平安vacuum优化之路》）
本文将在第二章中介绍声明式分区表的语法，在第三章分别介绍基于触发器的方案和声明式分区表方案，以及它们的应用。在第五章，介绍将基于触发器的方案升级为声明式分区方案的方法。
2 声明式分区简介
2.1 声明式分区的语法
以PostgreSQL 11 为例，声明式分区表的最常用的分区形式有两种：
范围分区，该表被划分为由键列或列集定义的“范围”，分配给不同分区的值的范围之间没有重叠。
列表分区，表通过明确列出每个分区中出现的键值进行分区。
创建分区表（partitioned table）的语法如下：
CREATE TABLE table_name ( ...)
[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) }
创建分区(partition)的语法如下：
CREATE TABLE table_name PARTITION OF parent_table [ () ] { FOR VALUES partition_bound_spec | DEFAULT }
其中，
partition_bound_spec是一个表达式。对范围分区，它是：
from （范围的下限）to （范围的上限）
其中，分区范围包含下限而不包含上限。
例如， “from（20）to (30)” 表示取值范围是 [20, 30)
对列表分区，它是：
in (value1[, …])
例如：
in (‘a’, ‘b’, ‘c’)
2.2 示例
创建范围分区表的示例如下：
create table tb_log (
id int,
remark varchar(32),
create_time timestamp without time zone
) partition by RANGE(create_time);
CREATE TABLE tb_log_default PARTITION OF tb_log DEFAULT;
CREATE TABLE tb_log_201901 PARTITION OF tb_log FOR VALUES FROM ('2019-01-01') TO ('2019-02-01');
CREATE TABLE tb_log_201902 PARTITION OF tb_log FOR VALUES FROM ('2019-02-01') TO ('2019-03-01');
创建列表分区的示例如下：
create table tb_student (
id int,
name varchar(32),
gender int,
classid int,
) partition by list (classid);
CREATE TABLE tb_student_default PARTITION OF tb_student DEFAULT;
CREATE TABLE tb_student_1 PARTITION OF tb_student FOR VALUES in ('1');
CREATE TABLE tb_ student_2 PARTITION OF tb_student FOR VALUES in ('2');
3 分区表方案及案例
我们以事件表 tb_event 为例，介绍基于“触发器+继承+检查约束”的方案和声明式分区方案及其实现案例。它是按月分区的，分区的字段是 utc_event_time，表示 事件发生时刻的UTC 时间，以毫秒为单位。
3.1 基于触发器的方案
先明确下文所说的几个概念：
父表（parent table）：基于“触发器+继承+检查约束”的分区方法中，被继承的表
子表（child table）：基于“触发器+继承+检查约束”的分区方法中，继承父表的表
现在介绍这种方案的实现案例。
2. 首先，创建tb_event的父表：
create table tb_event
(
event_id varchar(64) default gen_random_uuid() not null,
event_type integer,
event_time timestamp not null,
event_name varchar(128),
person_id varchar(64),
person_name varchar(64),
dept_id varchar(64),
dept_name varchar(1024),
device_id varchar(64),
device_name varchar(64),
door_id varchar(64),
door_name varchar(64),
utc_event_time bigint not null,
event_time_dif varchar(8) not null,
constraint pk_tb_event primary key (event_id)
);
comment on table tb_event is '事件表';
comment on column tb_event.event_id is '主键';
comment on column tb_event.event_type is '事件类型';
comment on column tb_event.event_time is '本地事件产生时间';
comment on column tb_event.event_name is '事件名称';
comment on column tb_event.person_id is '人员ID';
comment on column tb_event.person_name is '人员姓名';
comment on column tb_event.dept_id is '部门ID';
comment on column tb_event.dept_name is '部门名称';
comment on column tb_event.device_id is '产生事件的设备id';
comment on column tb_event.device_name is '产生事件的设备名称';
comment on column tb_event.door_name is '门名称';
comment on column tb_event.door_id is '门id';
comment on column tb_event.utc_event_time is 'UTC平台事件产生的时间';
comment on column tb_event.event_time_dif is '事件产生时差';
2. 创建tb_event上的触发器函数和触发器：
create or replace function func_tri_tb_event() returns trigger as
$$
declare my_tbname varchar(64);
declare my_start_time varchar(64);
declare my_end_time varchar(64);
declare my_start_utc bigint;
declare my_end_utc bigint;
declare sql_str text;
begin
my_tbname = TG_TABLE_NAME || '_' || to_char(to_timestamp(NEW.utc_event_time/1000),'YYYYMM');
sql_str = 'INSERT INTO '||my_tbname ||' SELECT $1.* ';
EXECUTE sql_str USING NEW;
return null;
exception when undefined_table then
begin
my_start_time=to_char(to_timestamp(NEW.utc_event_time/1000),'YYYY-MM')||'-01 00:00:00';
my_end_time=(my_start_time::timestamp(0) + interval '1 month')::text;
my_start_utc=extract(epoch from my_start_time::timestamp with time zone)*1000;
my_end_utc=extract(epoch from my_end_time::timestamp with time zone)*1000;
execute 'create table ' ||my_tbname || ' (constraint ck_' || my_tbname || '_utctm' || ' check (utc_event_time >= ' || my_start_utc || ' and utc_event_time < '|| my_end_utc || ')) INHERITS ('|| TG_TABLE_NAME || ')';
execute 'alter table ' ||my_tbname || ' add constraint pk_'||my_tbname||' primary key (event_id)';
execute 'create index idx_'||my_tbname ||'_utctm on ' || my_tbname || ' (utc_event_time)';
execute 'create index idx_'||my_tbname ||'_deptid on ' || my_tbname || ' (dept_id)';
execute 'create index idx_'||my_tbname ||'_person_name on ' || my_tbname || ' (person_id)';
EXECUTE sql_str USING NEW;
return null;
exception when others then
EXECUTE sql_str USING NEW;
return null;
end;
end;
$$ language plpgsql;
create trigger tri_ins_tb_event BEFORE insert on tb_event for each row EXECUTE PROCEDURE func_tri_tb_event();
这个触发器函数做了这样一些工作：
- 为插入的数据创建子表，如果子表不存在。
- 根据utc_event_time，将数据插入到对应的子表上。
当我们向表中插入数据时，触发器会创建的这样的子表：
tb_event_201911,
tb_event_201920,
……
子表的结构如下，以 tb_event_201911 为例：
CREATE TABLE tb_event_201911
(
event_id character varying(64) NOT NULL DEFAULT gen_random_uuid(),
event_type integer,
event_time timestamp without time zone NOT NULL,
event_name character varying(128),
person_id character varying(64),
person_name character varying(64),
dept_id character varying(64),
dept_name character varying(1024),
device_id character varying(64),
device_name varchar(64),
door_id character varying(64),
door_name character varying(64),
utc_event_time bigint NOT NULL,
event_time_dif character varying(8) NOT NULL,
CONSTRAINT pk_tb_event_201911 PRIMARY KEY (event_id),
CONSTRAINT ck_tb_event_201911_utc_event_time CHECK (utc_event_time >= '1572537600000'::bigint AND utc_event_time < '1575129600000'::bigint)
)INHERITS (tb_event);
CREATE INDEX idx_tb_event_201911_dept_id ON tb_event_201911 (dept_id);
CREATE INDEX idx_tb_event_201911_person_name ON tb_event_201911 (person_id);
CREATE INDEX idx_tb_event_201911_utc_event_time ON tb_event_201911 (utc_event_time);
可以看出，这个子表继承（inherit）父表，并有一个check约束用来限定utc_event_time（即事件发生的utc时间）的取值范围，此外，还有独立的主键、唯一键约束和索引。这样，当程序以utc_event_time为条件在tb_event进行查询，删除或更新操作时，数据库可根据utc_event_time定位到相应的子表上。
这样就实现了将tb_event作为一个分区表。
3.2 声明式分区方案
先明确下文所说的几个概念：
分区表（partitioned table）：声明式分区方法中，被划分为多个子集的表。
分区（partition）：声明式分区方法中，分区表的单独子集。
1. 创建分区表（父表）。执行下面的sql：
create table tb_event
(
event_id varchar(64) default gen_random_uuid() not null,
event_type integer,
event_time timestamp not null,
event_name varchar(128),
person_id varchar(64),
person_name varchar(64),
dept_id varchar(64),
dept_name varchar(1024),
device_id varchar(64),
device_name varchar(64),
door_id varchar(64),
door_name varchar(64),
receive_time timestamp,
utc_event_time bigint not null,
event_time_dif varchar(8) not null
) partition by range (utc_event_time);
comment on table tb_event is '事件表';
comment on column tb_event.event_id is '主键';
comment on column tb_event.event_type is '事件类型';
comment on column tb_event.event_time is '本地事件产生时间';
comment on column tb_event.event_name is '事件名称';
comment on column tb_event.person_id is '人员ID';
comment on column tb_event.person_name is '人员姓名';
comment on column tb_event.dept_id is '部门ID';
comment on column tb_event.dept_name is '部门名称';
comment on column tb_event.device_id is '产生事件的设备id';
comment on column tb_event.device_name is '产生事件的设备名称';
comment on column tb_event.door_name is '门名称';
comment on column tb_event.door_id is '门id';
comment on column tb_event.receive_time is '本地平台接受事件的时间';
comment on column tb_event.utc_event_time is 'UTC平台事件产生的时间';
comment on column tb_event.event_time_dif is '事件产生时差';
CREATE INDEX idx_tb_event_dept_id ON tb_event (dept_id);
CREATE INDEX idx_tb_event_person_name ON tb_event (person_id);
CREATE INDEX idx_tb_event_utc_event_time ON tb_event (utc_event_time);
注意，它没有主键或唯一键。因为在 PostgreSQL中，我们不能在分区表上创建不包含分区字段（即utc_event_time）的主键和唯一键。
2. 创建分区。例如，如果你要为2019年11月创建一个分区，则执行下面的sql：
create table tb_event_201909 partition of tb_event for values from (1572537600000) to (1575129600000);
如果希望创建一个默认分区，存放那些对应分区还没有被创建的数据。则可以执行：
create table tb_event_default partition of tb_event default;
如图，从pgadmin4 中，你可以看到创建了哪些分区：
4 分区表的升级方法
我们已经介绍了这两种分区表设计方案和具体案例。那么，如何将基于触发器的分区表升级为声明式的分区表呢？下面我们将介绍升级的步骤。
0. 在升级之前，最好对数据库进行备份，或者至少备份要升级到的表。以便在错误发生时，可以恢复数据。
1. 删除父表上面的触发器和触发器函数。对于表tb_event，执行下面的命令。
drop trigger if exists tri_ins_tb_event on tb_event cascade ;
drop function if exists func_tri_tb_event() cascade;
2. 创建与原表结构，分区键，默认值，非空约束，描述完全相同的声明式分区表。
对于tb_event，我们创建一个 tb_event_bak，它的分区键是utc_event_time：
create table tb_event_bak
(
like tb_event including DEFAULTS including COMMENTS
) partition by range (utc_event_time);
comment on table tb_event_bak is '事件表';
3. 为这个分区表的创建默认的分区，为该分区加上主键，唯一键等相关约束，但不要添加检查约束。tb_event_bak的默认分区的名称是tb_event_default。操作如下：
create table tb_event_default partition of tb_event_bak default;
alter table tb_event_default add constraint pk_tb_event_default primary key (event_id);
4. 接下来，将原表的每一个子表变成新表的分区。
4.1 取消子表对父表的继承。以 tb_event_201911 为例：
alter table tb_event_201911 no inherit tb_event;
4.2 根据子表上的检查约束条件，确定分区字段的取值范围，将原先的子表作为分区表的分区。
例如，根据检查约束ck_tb_event_201911_utc_event_time 可以看到， tb_event_201911的utc_event_time 的取值范围是 [1572537600000, 1575129600000), 则执行命令：
alter table tb_event_bak attach partition tb_event_201911 for values from ('1572537600000') to ('1575129600000');
4.3 删除子表上原来的检查约束：
ALTER TABLE tb_event_201911 DROP CONSTRAINT ck_tb_event_201911_utc_event_time;
5. 对其他子表，执行上述操作，直到所有的子表都成为新表的分区。
6. 删除原来的父表，并将新分区表的名字改为原父表的名字。
drop table tb_event cascade;
alter table tb_event_bak rename to tb_event;
4.6 可以在分区表上创建索引，这样的好处是之后创建分区时，它分区也会继承这些索引，而无需再次声明。
CREATE INDEX tb_event_dept_id_idx ON tb_event (dept_id);
CREATE INDEX tb_event_event_type_idx ON tb_event (event_type);
CREATE INDEX tb_event_person_name_idx ON tb_event (person_name);
CREATE INDEX tb_event_utc_event_time_idx ON tb_event (utc_event_time);
需要注意，你不能在分区表上创建不包含分区字段的主键和唯一键。像这样的约束是无法创建的：
Alter table tb_event add constraint pk_tb_event primary key (event_id);
上面的命令会报错。因此主键，唯一键约束仍需单独为每个分区创建。
这样就完成了分区表的升级。
5 总结
本文介绍了PostgreSQL 中两种分区的方案，基于触发器的方案和声明式分区表方案及其应用案例。并根据具体案例，着重介绍了将基于触发器的方案升级为声明式分区方案的方法。由于声明式分区的性能更好，因此当程序使用了PostgreSQL 10 以上版本后，应该尽早将分区表升级为声明式分区表。
参考文献
[1] Ashutosh Bapat. Upgrade your partitioning from inheritance to declarative. 2018-06-23
[2] PostgreSQL全球开发小组. PostgreSQL 11.6 文档. 表分区. 2019-11-14
[3] 石勇虎.庖丁解牛之平安vacuum优化之路. PostgreSQL 2018 中国技术大会
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
