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》

《PostgreSQL 9.5+ 高效分区表实现 - pg_pathman》

《PostgreSQL 触发器 用法详解 2》

《PostgreSQL 触发器 用法详解 1》

digoal’s 大量PostgreSQL文章入口