PostgreSQL 資料rotate用法介紹

postgresql , 按時間覆寫曆史資料

在某些業務場景中，資料有冷熱之分，例如業務隻關心最近一天、一周或者一個月的資料。對于曆史的資料可以丢棄。

比如某些名額的監控場景，保留一周的監控資料，曆史的都可以丢棄。

如何丢棄曆史資料？或者說如何實作rotate？

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

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

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

<a href="https://github.com/digoal/blog/blob/master/201702/20170221_03.md">《postgresql merge join 評估成本時可能會查詢索引 - 硬解析務必引起注意 - 批量删除資料後, 未釋放empty索引頁導緻mergejoin執行計劃變慢 case》</a>

2. 使用分區表，輪詢使用，并且使用truncate清理分區。

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

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

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

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

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

得益于postgresql支援ddl事務。

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

dow分區使用限制，好處是查詢時可以根據限制直接過濾分區。

1. 建立主表

2. 建立分區

3. select/update/delete資料時，直接操作主表，代入時間條件，可以過濾分區

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

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

例子

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

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. 鎖逾時

2. 事務失敗注意復原

3. 中間表名必須不存在

4. 限制名統一

使用delete的方法清除曆史資料，會帶來額外的開銷，包括寫redo日志，垃圾回收等。如果删除的資料量很大，還需要重新收集統計資訊，甚至收集不及時會導緻統計資訊不準确。

因為postgresql支援ddl封裝在事務中，是以也可以使用分區表，輪詢使用，并且使用truncate清理分區。

祝使用愉快。