标签
PostgreSQL , 自动创建分区 , 触发器 , 写入 , 动态创建分区
背景
数据写入时，自动创建分区。
目前pg_pathman这个分区插件，有这个功能，如果你不是用的这个插件，可以考虑一下用触发器写入，并在触发器中实现自动创建分区。
《PostgreSQL 9.6 sharding based on FDW & pg_pathman》
《PostgreSQL 9.5+ 高效分区表实现 - pg_pathman》
例子
1、创建主表，索引
create table test(id int, info text, crt_time timestamp not null );
create index idx_test_1 on test(id);
create index idx_test_2 on test(crt_time);
2、创建写入触发器，在触发器内，根据错误判断是否需要建表
长连接，使用绑定变量来写入，性能比动态SQL更好。
create or replace function ins_tg() returns trigger as $$
declare
-- 分区后缀
suffix text := to_char(NEW.crt_time,'yyyymmdd');
begin
-- 乐观实现，执行绑定变量
execute format('execute p%s(%s, %L, %L)', suffix, NEW.id, NEW.info, NEW.crt_time);
return null;
exception
when others then
-- 自动建分区, 有必要的话，你可以把分区表的分区键约束也在这个QUERY中添加一下。
execute format('create table IF NOT EXISTS test_%s (like test) inherits(test)', suffix);
-- 建绑定变量
execute format('prepare p%s (int,text,timestamp) as insert into test_%s values($1,$2,$3)', suffix, suffix);
-- 执行绑定变量
execute format('execute p%s (%s, %L, %L)', suffix, NEW.id, NEW.info, NEW.crt_time);
return null;
end;
$$ language plpgsql strict;
如果是短连接，则没有必要使用prepare。那么触发器函数如下：
create or replace function ins_tg() returns trigger as $$
declare
-- 分区后缀
suffix text := to_char(NEW.crt_time,'yyyymmdd');
begin
-- 乐观实现，执行动态SQL
execute format('insert into test_%s select $1.*', suffix) using NEW;
return null;
exception
when others then
-- 自动建分区, 有必要的话，你可以把分区表的分区键约束也在这个QUERY中添加一下。
execute format('create table IF NOT EXISTS test_%s (like test) inherits(test)', suffix);
-- 执行动态SQL
execute format('insert into test_%s select $1.*', suffix) using NEW;
return null;
end;
$$ language plpgsql strict;
3、在主表上创建触发器
create trigger tg before insert on test for each row execute procedure ins_tg();
4、创建一个函数，获取随机时间
create or replace function get_rand_ts() returns timestamp as $$
select now()::timestamp + ((1000*random())::int::text||' days')::interval;
$$ language sql strict;
5、插入测试
vi test.sql
insert into test values (1,'test',get_rand_ts());
6、压测
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 120
可以达到22万行/s的写入.
progress: 113.0 s, 221725.4 tps, lat 0.289 ms stddev 0.131
progress: 114.0 s, 222356.0 tps, lat 0.288 ms stddev 0.129
progress: 115.0 s, 222023.3 tps, lat 0.288 ms stddev 0.129
progress: 116.0 s, 221254.7 tps, lat 0.289 ms stddev 0.135
progress: 117.0 s, 222377.2 tps, lat 0.288 ms stddev 0.128
progress: 118.0 s, 221593.5 tps, lat 0.289 ms stddev 0.134
progress: 119.0 s, 221716.1 tps, lat 0.289 ms stddev 0.131
progress: 120.0 s, 221839.3 tps, lat 0.289 ms stddev 0.134
.....
7、测试数据分布均匀
public | test | table | postgres | 8192 bytes |
public | test_20180507 | table | postgres | 1920 kB |
public | test_20180508 | table | postgres | 2064 kB |
public | test_20180509 | table | postgres | 1816 kB |
public | test_20180510 | table | postgres | 1824 kB |
public | test_20180511 | table | postgres | 1800 kB |
public | test_20180512 | table | postgres | 1808 kB |
public | test_20180513 | table | postgres | 1736 kB |
public | test_20180514 | table | postgres | 1784 kB |
public | test_20180515 | table | postgres | 1872 kB |
public | test_20180516 | table | postgres | 1912 kB |
public | test_20180517 | table | postgres | 1584 kB |
public | test_20180518 | table | postgres | 1800 kB |
public | test_20180519 | table | postgres | 1912 kB |
public | test_20180520 | table | postgres | 1768 kB |
public | test_20180521 | table | postgres | 1720 kB |
public | test_20180522 | table | postgres | 1808 kB |
public | test_20180523 | table | postgres | 2056 kB |
public | test_20180524 | table | postgres | 1824 kB |
..................
其他动态分区写法
例如取模，按周，按某个自定义周期等。参考时间函数：
https://www.postgresql.org/docs/10/static/functions-datetime.html
isodow
The day of the week as Monday (1) to Sunday (7)
SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
Result: 7
This is identical to dow except for Sunday. This matches the ISO 8601 day of the week numbering.
其他例子
epochDay := floor(floor(extract(epoch from strSQL::timestamp)/86400)/period);
参考
《PostgreSQL Oracle 兼容性之 - DBMS_SQL(存储过程动态SQL中使用绑定变量)》
《PostgreSQL rotate table 自动清理调度 - 约束，触发器》
《PostgreSQL 9.6 sharding based on FDW & pg_pathman》
