PostgreSQL 分區表的動態建立與刪除實作指南

前言

在處理大量時間序列資料時，PostgreSQL 的表分區是提升查詢效能與維護便利性的關鍵技術。本文提供兩個實用的 PL/pgSQL 函數，分別用於批次建立與刪除按日期命名的分區表，並示範如何授權與清理，適合資料倉儲或日誌系統的自動化維護。

核心函數：建立分區表

create_table 函數接受基礎表名稱與欲建立的天數，自動依據當前日期向後產生對應的分區表，並繼承自母表，同時授予 dwetl 角色查詢權限。

CREATE OR REPLACE FUNCTION create_table(table_name character varying, table_num integer)
RETURNS void LANGUAGE plpgsql AS $function$
DECLARE
    v_date      char(8);
    v_tablename varchar(64);
BEGIN
    FOR i IN 0..table_num LOOP
        v_date := to_char(current_date + i, 'YYYYMMDD');
        v_tablename := table_name || '_' || v_date;
        EXECUTE 'CREATE TABLE ' || v_tablename || ' (LIKE ' || table_name || ' INCLUDING ALL) INHERITS (' || table_name || ')';
        EXECUTE 'GRANT SELECT ON ' || v_tablename || ' TO dwetl';
    END LOOP;
END;
$function$;

核心函數：刪除分區表

drop_table 函數同樣依據基礎表名與天數，逐一刪除對應的分區表，適合定期清理過期資料。

CREATE OR REPLACE FUNCTION drop_table(table_name character varying, table_num integer)
RETURNS void LANGUAGE plpgsql AS $function$
DECLARE
    v_date      char(8);
    v_tablename varchar(64);
BEGIN
    FOR i IN 0..table_num LOOP
        v_date := to_char(current_date + i, 'YYYYMMDD');
        v_tablename := table_name || '_' || v_date;
        EXECUTE 'DROP TABLE ' || v_tablename;
    END LOOP;
END;
$function$;

使用範例

-- 建立未來 30 天的分區
SELECT create_table('sales_data', 30);

-- 刪除未來 7 天的分區（視業務需求調整）
SELECT drop_table('sales_data', 7);

-- 移除函數定義（需指定參數型別）
DROP FUNCTION create_table(character varying, integer);
DROP FUNCTION drop_table(character varying, integer);

注意事項

• 母表需預先建立，且分區表將透過 INHERITS 機制繼承結構與約束。
• 授權對象 dwetl 需為資料庫中存在的角色，請依實際環境調整。
• 大量分區操作建議在低峰期執行，並搭配 pg_cron 等排程工具自動化。