PostgreSQL資料rotate用法介紹

德哥發表於2017-03-24
SQL

標籤

PostgreSQL , 按時間覆蓋歷史資料

背景

在某些業務場景中，資料有冷熱之分，例如業務只關心最近一天、一週或者一個月的資料。對於歷史的資料可以丟棄。

比如某些指標的監控場景，保留一週的監控資料，歷史的都可以丟棄。

如何丟棄歷史資料？或者說如何實現rotate？

1. 使用delete, 刪除7天前的資料。

delete from table where crt_time<=now()-interval `7 day`;

這種方法會帶來額外的開銷，包括寫REDO日誌，垃圾回收等。如果刪除的資料量很大，還需要重新收集統計資訊，甚至收集不及時會導致統計資訊不準確。

另一方面，還可能引入merge join的問題。

《PostgreSQL merge join 評估成本時可能會查詢索引 – 硬解析務必引起注意 – 批量刪除資料後, 未釋放empty索引頁導致mergejoin執行計劃變慢 case》

2. 使用分割槽表，輪詢使用，並且使用truncate清理分割槽。

這種方法可以避免DELETE帶來的問題。

但是使用不當也可能引入新的問題: truncate是DDL操作，rename table也是DDL操作，建議對DDL加上鎖超時，否則DDL等待會堵塞任意其他SQL。

交換表名時，需要一個臨時表名，名字不能被佔用。

下面就以分割槽表為例，講一下資料rotate用法。

例子

以保留一週資料為例，看看第二種方法如何來實施。

得益於PostgreSQL支援DDL事務。

方法

一共9張表，一張主表，8張分割槽表，其中7個對應dow，還有一個對應預設分割槽(交換分割槽)。

dow分割槽使用約束，好處是查詢時可以根據約束直接過濾分割槽。

1. 建立主表

create table test(id int primary key, info text, crt_time timestamp);

2. 建立分割槽

create table test0(like test including all, constraint ck check(extract(dow from crt_time)=0)) inherits(test);  
create table test1(like test including all, constraint ck check(extract(dow from crt_time)=1)) inherits(test);  
create table test2(like test including all, constraint ck check(extract(dow from crt_time)=2)) inherits(test);  
create table test3(like test including all, constraint ck check(extract(dow from crt_time)=3)) inherits(test);  
create table test4(like test including all, constraint ck check(extract(dow from crt_time)=4)) inherits(test);  
create table test5(like test including all, constraint ck check(extract(dow from crt_time)=5)) inherits(test);  
create table test6(like test including all, constraint ck check(extract(dow from crt_time)=6)) inherits(test);  
  
create table test_def(like test including all) inherits(test);

3. SELECT/UPDATE/DELETE資料時，直接操作主表，代入時間條件，可以過濾分割槽

postgres=#  explain select * from test where crt_time=now() and extract(dow from crt_time)=0; 
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Append  (cost=0.00..65.20 rows=3 width=44)
   ->  Seq Scan on test  (cost=0.00..0.00 rows=1 width=44)
         Filter: ((crt_time = now()) AND (date_part(`dow`::text, crt_time) = `0`::double precision))
   ->  Seq Scan on test0  (cost=0.00..32.60 rows=1 width=44)
         Filter: ((crt_time = now()) AND (date_part(`dow`::text, crt_time) = `0`::double precision))
   ->  Seq Scan on test_def  (cost=0.00..32.60 rows=1 width=44)
         Filter: ((crt_time = now()) AND (date_part(`dow`::text, crt_time) = `0`::double precision))
(7 rows)

4. INSERT時，建議程式根據crt_time的dow自動拼接表名,直接對分割槽表進行操作。

如果程式不想對分割槽進行操作，那麼可以使用觸發器或規則。

例子

create or replace function ins_tg() returns trigger as $$
declare
begin
  case extract(dow from NEW.crt_time)
  when 0 then insert into test0 values (NEW.*);
  when 1 then insert into test1 values (NEW.*);
  when 2 then insert into test2 values (NEW.*);
  when 3 then insert into test3 values (NEW.*);
  when 4 then insert into test4 values (NEW.*);
  when 5 then insert into test5 values (NEW.*);
  when 6 then insert into test6 values (NEW.*);
  end case;
  return null;
end;
$$ language plpgsql strict;

create trigger tg before insert on test for each row execute procedure ins_tg();

insert into test values (1,`test`,now()+interval `1 day`);
insert into test values (1,`test`,now()+interval `2 day`);
insert into test values (1,`test`,now()+interval `3 day`);
insert into test values (1,`test`,now()+interval `4 day`);
insert into test values (1,`test`,now()+interval `5 day`);
insert into test values (1,`test`,now()+interval `6 day`);
insert into test values (1,`test`,now()+interval `7 day`);

postgres=# select tableoid::regclass , * from test;
 tableoid | id | info |          crt_time          
----------+----+------+----------------------------
 test0    |  1 | test | 2017-03-26 14:40:48.066905
 test1    |  1 | test | 2017-03-27 14:40:50.450942
 test2    |  1 | test | 2017-03-28 14:40:52.271922
 test4    |  1 | test | 2017-03-23 14:40:22.551928
 test5    |  1 | test | 2017-03-24 14:40:24.643933
 test6    |  1 | test | 2017-03-25 14:40:28.138913
 test3    |  1 | test | 2017-03-22 14:40:20.586945
(7 rows)

rotate用法(每天在空閒時間點處理一次即可，DDL支援事務，如果事務失敗，可重新發起重試)

在一個事務中完成如下動作

1. 計算明天的dow

2. 清除test_def約束

3. 清除test_def資料

4. test_def重新命名test_def_tmp(一個不存在的表名)

5. 明天的分割槽表，重新命名為test_def

6. test_def_tmp新增約束

7. test_def_tmp重新命名為明天的分割槽

例子

1. 開始事務  
begin;  
  
2. 設定鎖超時  
set lock_timeout = `60s`;  
  
3. 查詢明天的dow  
select extract(dow from current_date+1);  
 date_part   
-----------  
         3  
(1 row)  
  
4. 清除test_def約束  
alter table test_def drop constraint IF EXISTS ck;  

5. 清除test_def資料  
truncate test_def;  
  
6. 重新命名test_def  
alter table test_def rename to test_def_tmp;  
  
7. 重新命名明天的分割槽表  
alter table test3 rename to test_def;  
  
8. test_def_tmp新增約束  
alter table test_def_tmp add constraint ck check(extract(dow from crt_time)=3);  
  
9. test_def_tmp重新命名為明天的分割槽  
alter table test_def_tmp rename to test3;  
   
10. 提交或回滾
commit;
如果失敗，回滾事務。  
rollback;

注意事項:

1. 鎖超時

2. 事務失敗注意回滾

3. 中間表名必須不存在

4. 約束名統一

小結

使用DELETE的方法清除歷史資料，會帶來額外的開銷，包括寫REDO日誌，垃圾回收等。如果刪除的資料量很大，還需要重新收集統計資訊，甚至收集不及時會導致統計資訊不準確。

另一方面，還可能引入merge join的問題。

《PostgreSQL merge join 評估成本時可能會查詢索引 – 硬解析務必引起注意 – 批量刪除資料後, 未釋放empty索引頁導致mergejoin執行計劃變慢 case》

因為PostgreSQL支援DDL封裝在事務中，所以也可以使用分割槽表，輪詢使用，並且使用truncate清理分割槽。

這種方法可以避免DELETE帶來的問題。

但是使用不當也可能引入新的問題: truncate是DDL操作，rename table也是DDL操作，建議對DDL加上鎖超時，否則DDL等待會堵塞任意其他SQL。

交換表名時，需要一個臨時表名，名字不能被佔用。

祝使用愉快。


相關文章