PostgreSQL 自動建立分區實踐 - 寫入觸發器

标簽

PostgreSQL , 自動建立分區 , 觸發器 , 寫入 , 動态建立分區

https://github.com/digoal/blog/blob/master/201805/20180507_01.md#%E8%83%8C%E6%99%AF 背景

資料寫入時，自動建立分區。

目前pg_pathman這個分區插件，有這個功能，如果你不是用的這個插件，可以考慮一下用觸發器寫入，并在觸發器中實作自動建立分區。

《PostgreSQL 9.6 sharding based on FDW & pg_pathman》 《PostgreSQL 9.5+ 高效分區表實作 - pg_pathman》

https://github.com/digoal/blog/blob/master/201805/20180507_01.md#%E4%BE%8B%E5%AD%90 例子

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://github.com/digoal/blog/blob/master/201805/20180507_01.md#%E5%85%B6%E4%BB%96%E5%8A%A8%E6%80%81%E5%88%86%E5%8C%BA%E5%86%99%E6%B3%95 其他動态分區寫法

例如取模，按周，按某個自定義周期等。參考時間函數：

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);  
           

https://github.com/digoal/blog/blob/master/201805/20180507_01.md#%E5%8F%82%E8%80%83 參考

《PostgreSQL Oracle 相容性之 - DBMS_SQL(存儲過程動态SQL中使用綁定變量)》 《PostgreSQL rotate table 自動清理排程 - 限制，觸發器》 《PostgreSQL 觸發器 用法詳解 2》 《PostgreSQL 觸發器 用法詳解 1》