PostgreSQL 利用动态 SQL 实现自动按月创建表分区

PostgreSQL 利用动态 SQL 实现自动按月创建表分区

PostgreSQL 原生支持基于继承的表分区功能，比如按时间每月创建一个子表。但按照官方文档的传统做法，创建子表、索引以及修改触发器等工作往往需要 DBA 定期手动执行，无法实现自动化运维，维护成本较高。

这里分享一种通过 plpgsql 结合动态 SQL 的方案，将大表分区的运维操作自动化，并且代码可复用。假设有一个主表 tbl_partition，其中包含采集时间字段 gather_time。我们需要根据这个时间，自动将数据记录到对应月份的子表中（如 tbl_partition_201510）。

步骤一：定义主表结构

首先创建父表，定义好基础字段和用于分区的时间字段：

CREATE TABLE tbl_partition (
    id integer,
    name text,
    data numeric,
    gather_time timestamp
);

步骤二：绑定触发器

为主表创建插入触发器，指向我们即将编写的自动化函数。注意，虽然触发器函数通常不带参数，但这里必须传入时间字段的名称，否则函数无法知道依据哪个字段进行分区判断。

CREATE TRIGGER insert_tbl_partition_trigger
BEFORE INSERT ON tbl_partition
FOR EACH ROW
EXECUTE PROCEDURE auto_insert_into_tbl_partition('gather_time');

步骤三：编写自动化函数

这是核心部分。函数会检查目标月份对应的子表是否存在，若不存在则自动创建带检查约束的子表和索引，随后将数据写入。

CREATE OR REPLACE FUNCTION auto_insert_into_tbl_partition(time_column_name text)
RETURNS trigger AS $BODY$
DECLARE
    curMM varchar(6);      -- 'YYYYMM' 格式，用作分区子表后缀
    isExist boolean;       -- 标记分区子表是否已存在
    strSQL text;           -- 动态 SQL 语句
BEGIN
    -- 获取调用时传入的时间字段名
    time_column_name := TG_ARGV[0];

    -- 动态获取当前行中时间字段的值
    EXECUTE format('SELECT %I', time_column_name) INTO curMM USING NEW;
    curMM := to_char(curMM, 'YYYYMM');

    -- 检查对应分区表是否已存在
    EXECUTE format(
        'SELECT EXISTS(SELECT 1 FROM information_schema.tables WHERE table_schema = current_schema() AND table_name = ''%s_%s'')',
        TG_RELNAME, curMM
    ) INTO isExist;

    IF NOT isExist THEN
        -- 创建分区子表，包含 CHECK 约束并继承主表
        EXECUTE format(
            'CREATE TABLE %I_%s (CHECK (%I >= ''%s-01 00:00:00'' AND %I < ''%s-01 00:00:00'')) INHERITS (%I)',
            TG_RELNAME, curMM, time_column_name, curMM, time_column_name, 
            to_char(add_months(to_date(curMM, 'YYYYMM'), 1), 'YYYYMM'),
            TG_RELNAME
        );
        -- 为子表创建索引
        EXECUTE format(
            'CREATE INDEX idx_%s_%s ON %I_%s USING btree (%I)',
            TG_RELNAME, curMM, TG_RELNAME, curMM, time_column_name
        );
    END IF;

    -- 将数据插入到对应的分区子表中
    EXECUTE format('INSERT INTO %I_%s SELECT * FROM NEW;', TG_RELNAME, curMM);

    RETURN NULL;
END
$BODY$
LANGUAGE plpgsql;