PostgreSQL 自动创建分区实践 - 写入触发器

digoal 2018-05-07

361

作者

digoal

日期

2018-05-07

背景

数据写入时，自动创建分区。

目前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、测试数据分布均匀