PostgreSQL 動态分表+Rotate輪詢

工作中，遇到如下業務需求：使用者可以自定義曆史資料的儲存期限，且對過期資料不作保留（即定期删除），該類資料表的數量級在6千萬/年。

前期處理方案：按月分表，由于在項目前期，限制使用者隻能儲存一年的資料量，通過PG的Rule分表，并通過一張中間表，進行新月份資料的錄入更替。簡單Rotate用法，在月份更替零點執行即可，因DDL支援事務，可以對失敗操作進行復原。

使用Rule建立分區表的操作不再累述，因為每年有固定的12個月，舉例，目前5月份資料存放在table_xxx_5表中，那麼去年6月份的資料就存放在table_xxx_5表中，而去年5月份的資料，存放在table_xxx_med表中，以便查詢5月剩餘天數在去年的曆史資料，輪詢資料的方法如下：

1. 開始事務
BEGIN;
SET LOCK_TIMEOUT = '30's;
2. 查詢NextMonth
SELECT EXTRACT(MON FROM NOW()) + 1;
3. 删除table_xxx_med表限制
ALTER TABLE table_xxx_med DROP CONSTRAINT IF EXISTS ck_mon;
4. 删除table_xxx_med資料
TRUNCATE table_xxx_med;
5. 重命名table_xxx_med，以便于将table_xxx_05重名為table_xxx_med
ALTER TABLE table_xxx_med RENAME TO table_xxx_med_temp;
ALTER TABLE table_xxx_05 RENAME TO table_xxx_med;
6. 添加限制，重命名表
ALTER TABLE table_xxx_med_temp ADD CONSTRAINT ck_mon CHECK ( EXTRACT ( MON FROM crt_time ) = 5 );
ALTER TABLE table_xxx_med_temp RENAME TO table_xxx_05;
7. 送出或復原
COMMIT;
ROLLBACK;
           

如果對于資料查詢的實時性要求很高，即不能容忍零點時事務執行存在對業務的Delay時長，那麼可以再添加一張中間表，為下月資料平滑切換提供存儲空間，在任意業務空閑時間點執行即可。

使用者自定義資料儲存期限方案：

在主表上觸發器，每當有資料插入時，判斷是否存在對應年份+月份的分區表，如果存在，則直接插入，如果不存在，則重新建立年份+月份分區表，并建立對應索引；對過期月份的分區表，直接DROP。

不足之處：觸發器是針對EACH ROW的，在資料插入方面的性能不及Rule形式的表分區，但測試得到E3下的插入效率遠大于1K/s，滿足業務場景需求。

具體實作如下：

CREATE OR REPLACE FUNCTION dynamic_create_table_func () RETURNS TRIGGER AS $BODY$
DECLARE
    date_catalog TEXT;
    sql_statement TEXT;
    sql_create TEXT;
    sql_index TEXT;
BEGIN
    SELECT to_char( NEW.dp_crt, 'YY_MM' ) INTO date_catalog;
    sql_statement := 'insert into dynamic_partition_table_' || date_catalog || ' (dp_id, dp_content, dp_crt, part_x) values (nextval(''dynamic_partition_table_dp_id_seq''), $1.dp_content, $1.dp_crt, ' || to_char( NEW.dp_crt, 'YYMM' ) || ')';
    EXECUTE sql_statement USING NEW;
    RETURN NULL;
    EXCEPTION 
    WHEN UNDEFINED_TABLE THEN
    sql_create := 'create table if not exists dynamic_partition_table_' || date_catalog || ' (check(part_x = ' || to_char( NEW.dp_crt, 'YYMM' ) || ')) inherits (dynamic_partition_table);';
    EXECUTE sql_create;
    sql_index := 'create index dynamic_partition_table_' || date_catalog || '_dp_id_idx on dynamic_partition_table_' || date_catalog || '(dp_id);';
    EXECUTE sql_index;
    sql_index := 'create index dynamic_partition_table_' || date_catalog || '_dp_crt_idx on dynamic_partition_table_' || date_catalog || '(dp_crt);';
    EXECUTE sql_index;
    EXECUTE sql_statement USING NEW;
    RETURN NULL;
END;
$BODY$
LANGUAGE'plpgsql';

CREATE TRIGGER dynamic_partition_table_insert_trg BEFORE INSERT ON dynamic_partition_table FOR EACH ROW EXECUTE PROCEDURE dynamic_create_table_func ();
           

建立的表名格式，table_xxx_18_05。

各位前輩，晚輩初學PG不才，如有更優的方案，望不吝明示。