PostgreSQLrotatetable自動清理排程-約束，觸發器

標籤

PostgreSQL , rotate table , 自動清理 , 觸發器 , insert into on conflict

---

背景

時序場景，這個需求很常見：

1、自動清理過期的歷史資料，並且要保證清理速度夠快，不產生WAL日誌。

要麼DROP,要麼truncate。

2、我們知道PG支援分割槽表，如果是通過分割槽的方法來實現，最好不過了，但是需要不停的建立分割槽，這個目前pg_pathman能夠滿足自動建立分割槽。但是自動刪除分割槽還是不夠自動。

所以怎麼做呢？

一個做法是這樣的：

1、固定一個週期的所有分割槽表（類似list分割槽），比如以小時為結尾的表，只需要24個。以周為單位的表，只需要7個。以分鐘為單位的表，需要60個。

2、自動根據資料插入的時間，觸發，並清理（truncate）舊的資料。

這種方法，寫入程式碼是固定的不需要變更。

資料表是一次性建立的，不需要後續再增加。

例子

1、建立週期內的所有明細分割槽表

以10分鐘為單位，建立6個表，代表每個10分鐘。

create table t1_0 (id int, info text, crt_time timestamp, check (substring(to_char(crt_time,`yyyymmddhh24miss`), 11, 1)=`0`));    
create table t1_1 (id int, info text, crt_time timestamp, check (substring(to_char(crt_time,`yyyymmddhh24miss`), 11, 1)=`1`));    
create table t1_2 (id int, info text, crt_time timestamp, check (substring(to_char(crt_time,`yyyymmddhh24miss`), 11, 1)=`2`));    
create table t1_3 (id int, info text, crt_time timestamp, check (substring(to_char(crt_time,`yyyymmddhh24miss`), 11, 1)=`3`));    
create table t1_4 (id int, info text, crt_time timestamp, check (substring(to_char(crt_time,`yyyymmddhh24miss`), 11, 1)=`4`));    
create table t1_5 (id int, info text, crt_time timestamp, check (substring(to_char(crt_time,`yyyymmddhh24miss`), 11, 1)=`5`));    

2、建立約束表，或者說排程表（因為我們不能每條記錄都觸發一個TRUNCATE事件）

以10分鐘為週期，清除10分鐘前的資料。

create table t_const(crt_time timestamp primary key);  

3、建立分割槽表的觸發器，將資料按週期截斷後，寫入約束表。

create or replace function tg() returns trigger as $$  
declare  
begin  
  insert into t_const values (to_timestamp(substring(to_char(NEW.crt_time,`yyyymmddhh24miss`), 1, 11)||`000`,`yyyymmddhh24miss`)) on conflict (crt_time) do nothing;  
  return null;  
end;  
$$ language plpgsql strict;  
  
create trigger tg1 after insert on t1_0 for each row execute procedure tg();  
create trigger tg1 after insert on t1_1 for each row execute procedure tg();  
create trigger tg1 after insert on t1_2 for each row execute procedure tg();  
create trigger tg1 after insert on t1_3 for each row execute procedure tg();  
create trigger tg1 after insert on t1_4 for each row execute procedure tg();  
create trigger tg1 after insert on t1_5 for each row execute procedure tg();  

4、建立約束表觸發器，觸發清除明細表分割槽的規則。

create or replace function tg_truncate() returns trigger as $$  
declare  
  suffix int := substring(to_char(NEW.crt_time,`yyyymmddhh24miss`), 11, 1)::int;  
begin  
  set lock_timeout = `1s`;  
  
  for i in 0..5 loop  
    if i=suffix then  
      continue;  
    end if;  
     
    if suffix=0 and i=5 then  
      continue;  
    end if;  
     
    if i=suffix-1 then  
      continue;  
    end if;  
      
    execute `truncate t1_`||i;  
  
    raise notice `truncated %`, `t1_`||i;  
  end loop;  
  
    
  return null;  
end;  
$$ language plpgsql strict;  

create trigger tg1 after insert on t_const for each row execute procedure tg_truncate();  

試一試

自動清除

postgres=# insert into t1_0 values (1,now(),`2018-01-02 10:00:40`);  
NOTICE:  truncated t1_1  
NOTICE:  truncated t1_2  
NOTICE:  truncated t1_3  
NOTICE:  truncated t1_4  
INSERT 0 1  

如果後面再寫入同一個區，不會觸發自動清除，符合要求（僅第一條觸發）。

postgres=# insert into t1_0 values (1,now(),`2018-01-02 10:00:40`);  
INSERT 0 1  
postgres=# insert into t1_0 values (1,now(),`2018-01-02 10:01:50`);  
INSERT 0 1  

小結

使用分割槽，實現了資料的分割槽存放。（目前如果使用原生分割槽表的話，寫入會對主表和所有子分割槽加共享鎖，導致無法truncate。建議方法：使用pg_pathman分割槽，或者直接寫子分割槽。）

《分割槽表鎖粒度差異 – pg_pathman VS native partition table》

使用TRUNCATE，使得清理資料時不會產生WAL日誌。

使用觸發器，實現了自動的資料清理。

參考

《PostgreSQL 資料rotate用法介紹 – 按時間覆蓋歷史資料》

《PostgreSQL APP海量FEED LOG實時質量統計CASE(含percentile_disc) – 含rotate 分割槽表》