如何为PostgreSQL表按月动态创建分区？

针对你用Liquibase管理PostgreSQL按月分区表的需求，提供两种动态创建分区的实用方案：

方案一：插入时自动创建分区（无需预创建） #

这种方式完全摆脱手动预创建的繁琐，当数据插入主表时，触发器会自动检查对应月份的分区是否存在，不存在则即时生成。

1. 创建分区检测与生成函数

CREATE OR REPLACE FUNCTION my_schema.create_monthly_partition()
RETURNS TRIGGER AS $$
DECLARE
    partition_table_name TEXT;
    partition_start DATE;
    partition_end DATE;
BEGIN
    -- 生成分区表名（格式：my_table_YYYYMM）
    partition_table_name := 'my_schema.my_table_' || TO_CHAR(NEW.date_column, 'YYYYMM');
    -- 计算分区的起止日期（当月第一天至下月第一天）
    partition_start := DATE_TRUNC('month', NEW.date_column)::DATE;
    partition_end := partition_start + INTERVAL '1 month';

    -- 检查分区是否存在，不存在则创建
    IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'my_schema' AND tablename = split_part(partition_table_name, '.', 2)) THEN
        EXECUTE format('
            CREATE TABLE %I PARTITION OF my_schema.my_table
            FOR VALUES FROM (%L) TO (%L);
            ALTER TABLE %I OWNER TO my_owner;
        ', partition_table_name, partition_start, partition_end, partition_table_name);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 给主表绑定触发器

CREATE TRIGGER trigger_my_table_create_partition
BEFORE INSERT ON my_schema.my_table
FOR EACH ROW EXECUTE FUNCTION my_schema.create_monthly_partition();

3. Liquibase 集成写法
   在changelog文件中用<sql>标签部署上述对象：

<changeSet id="create-partition-function" author="your-name">
    <sql>
        CREATE OR REPLACE FUNCTION my_schema.create_monthly_partition()
        RETURNS TRIGGER AS $$
        DECLARE
            partition_table_name TEXT;
            partition_start DATE;
            partition_end DATE;
        BEGIN
            partition_table_name := 'my_schema.my_table_' || TO_CHAR(NEW.date_column, 'YYYYMM');
            partition_start := DATE_TRUNC('month', NEW.date_column)::DATE;
            partition_end := partition_start + INTERVAL '1 month';

            IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'my_schema' AND tablename = split_part(partition_table_name, '.', 2)) THEN
                EXECUTE format('
                    CREATE TABLE %I PARTITION OF my_schema.my_table
                    FOR VALUES FROM (%L) TO (%L);
                    ALTER TABLE %I OWNER TO my_owner;
                ', partition_table_name, partition_start, partition_end, partition_table_name);
            END IF;
            RETURN NEW;
        END;
        $$ LANGUAGE plpgsql;
    </sql>
</changeSet>

<changeSet id="create-partition-trigger" author="your-name">
    <sql>
        CREATE TRIGGER trigger_my_table_create_partition
        BEFORE INSERT ON my_schema.my_table
        FOR EACH ROW EXECUTE FUNCTION my_schema.create_monthly_partition();
    </sql>
</changeSet>

方案二：Liquibase 预创建未来N个月分区 #

如果希望提前创建未来若干月的分区（避免插入时的函数开销），可以结合PostgreSQL的generate_series动态生成批量创建语句。

比如预创建未来6个月的分区：

<changeSet id="create-future-partitions" author="your-name">
    <sql>
        DO $$
        DECLARE
            rec RECORD;
            partition_name TEXT;
        BEGIN
            FOR rec IN SELECT generate_series(
                DATE_TRUNC('month', CURRENT_DATE)::DATE,
                DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '6 months',
                INTERVAL '1 month'
            ) AS month_start
            LOOP
                partition_name := 'my_schema.my_table_' || TO_CHAR(rec.month_start, 'YYYYMM');
                IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'my_schema' AND tablename = split_part(partition_name, '.', 2)) THEN
                    EXECUTE format('
                        CREATE TABLE %I PARTITION OF my_schema.my_table
                        FOR VALUES FROM (%L) TO (%L);
                        ALTER TABLE %I OWNER TO my_owner;
                    ', partition_name, rec.month_start, rec.month_start + INTERVAL '1 month', partition_name);
                END IF;
            END LOOP;
        END $$;
    </sql>
    <!-- 添加预条件避免重复执行 -->
    <preConditions onFail="MARK_RAN">
        <sqlCheck expectedResult="0">
            SELECT COUNT(*) FROM pg_tables WHERE schemaname='my_schema' AND tablename LIKE 'my_table_' || TO_CHAR(CURRENT_DATE, 'YYYYMM') || '%'
        </sqlCheck>
    </preConditions>
</changeSet>

你可以调整INTERVAL '6 months'设置预创建的月份数量，还能配合Liquibase的调度功能（比如cron定期执行该changeSet），实现自动续建分区。

前置要求 #

• 主表必须是范围分区表，分区键为目标日期字段，示例创建语句：

  CREATE TABLE my_schema.my_table (
      id INT,
      date_column TIMESTAMP,
      -- 其他字段
  ) PARTITION BY RANGE (date_column);
  

• 替换函数中的date_column为你实际的日期字段名。
• 确保Liquibase执行用户拥有创建表、修改表所有者的权限。

内容的提问来源于stack exchange，提问作者intA