PostgreSQL 没有提供像 Oracle 里那样比较智能的分区表功能，比如插入时需要指定子表，但是在一定程序上也能构建分区表，平常用得比较多的是两层结构，通过时间进行分区，今天介绍下构建三导分区表的方法，三层结构分区别可参考以下。
一 体系结构
三层结构分区表结构
备注：三层结构分区表包括三层，第一层为父表, 第二层通过某字段取模分区分了多个表，上图分为128个表， 第三层为年月表，根据时间字段分区，这样便实现了三层结构分区。接下来介绍下创建三层结构分区表的具体步骤。
二 创建三层结构分区表
2.1 创建父表 ( 第一层 )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| create table tbl_log(
sky_imsi character varying(50) not null,
xxx character varying(12) ,
xxx character varying(30) not null,
xxx integer ,
xxx character varying ,
xxx character varying(20) ,
trade_time timestamp without time zone not null,
col1 character varying(20),
col2 character varying (30),
col3 character varying (50),
remark character varying(100) ,
CONSTRAINT pk_tbl_log PRIMARY KEY (order_id)
);
create index idx_tbl_log on tbl_log(sky_imsi);
create index idx_tbl_log_ctime on tbl_log using btree (trade_time);
|
2.2 创建第二层表
1
2
3
4
5
| create table tbl_log_128_0 ( like tbl_log including constraints including defaults including indexes ) inherits ( tbl_log );
create table tbl_log_128_1 ( like tbl_log including constraints including defaults including indexes ) inherits ( tbl_log );
create table tbl_log_128_2 ( like tbl_log including constraints including defaults including indexes ) inherits ( tbl_log );
......
create table tbl_log_128_127 ( like tbl_log including constraints including defaults including indexes ) inherits ( tbl_log );
|
备注：上面创建了 128 张第二层表。
2.3 创建年月配置表并初始化配置数据
1
2
3
4
5
6
7
8
9
| create table tmp_date ( table_suff character varying(6) primary key );
insert into tmp_date values ('201112');
insert into tmp_date values ('201201');
insert into tmp_date values ('201202');
insert into tmp_date values ('201203');
insert into tmp_date values ('201204');
insert into tmp_date values ('201205');
insert into tmp_date values ('201206');
insert into tmp_date values ('201207');
|
备注： 这个表为配置表，用来存年月信息，初始化数据时只要输入年月信息即可，这里共配置了8个月的分区，下面的 function 会用到这个配置表来创建第三层表。
2.4 创建第三层子表和约束关系的 function
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
| CREATE OR REPLACE FUNCTION func_create_table()
RETURNS void
LANGUAGE plpgsql
AS $function$
declare
v_next_month char(6);
v_second_tabname varchar(64);
v_third_tabname varchar(64);
rec_date RECORD;
v_date varchar(6);
v_cur_date varchar(64);
v_tmo_date varchar(64);
begin
for i in 0..127 loop
v_second_tabname := 'tbl_log_128_' || i::text;
FOR rec_date in ( select table_suff from tmp_date order by tmp_date ) LOOP
v_date := rec_date.table_suff;
v_third_tabname :=v_second_tabname || '_' || v_date;
v_cur_date := v_date || '01';
v_tmo_date := to_char(v_cur_date::date + interval '1 months','yyyymmdd');
execute 'create table ' || v_third_tabname || '( like ' || v_second_tabname || ' including constraints including indexes including defaults) inherits (' || v_second_tabname || ')' ;
execute 'alter table ' || v_third_tabname || ' add constraint ' || v_third_tabname || E'_check check (((trade_time >='' || v_cur_date || E'') AND (trade_time < '' || v_tmo_date || E'')))';
end loop;
end loop;
end
$function$;
|
2.5 执行函数 func_create_table()
1
| select func_create_table():
|
三 总结
这里只简单介绍了三层结构分构表的初始化过程，平常维护成本还是较高的;通过函数创建分区表和约束关系大大减少了工作量。
关于分区表的查询性能这里不做介绍，之后的博文里会做这方面的分析。
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/237812.html
