PostgreSQL 声明式分区实战：按 created_at 月份拆分日志表

问题场景

日志表按时间持续写入，单表行数很快达到千万级。按 created_at 月份做范围分区，可以让历史数据按分区归档或删除，并让带时间范围的查询只扫描目标分区。下面以 PostgreSQL 14 为例（声明式分区从 10 起支持，默认分区从 11 起支持），给出可直接在测试库执行的最小示例。

1. 创建分区表与局部索引

CREATE TABLE app_log (
    id          bigserial,
    created_at  timestamptz NOT NULL,
    level       text NOT NULL,
    message     text NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE app_log_2025_01 PARTITION OF app_log
    FOR VALUES FROM ('2025-01-01 00:00:00+08') TO ('2025-02-01 00:00:00+08');

CREATE TABLE app_log_2025_02 PARTITION OF app_log
    FOR VALUES FROM ('2025-02-01 00:00:00+08') TO ('2025-03-01 00:00:00+08');

CREATE TABLE app_log_2025_03 PARTITION OF app_log
    FOR VALUES FROM ('2025-03-01 00:00:00+08') TO ('2025-04-01 00:00:00+08');

CREATE TABLE app_log_default PARTITION OF app_log DEFAULT;

主键必须包含分区键 created_at，否则无法在分区表上建立主键。

在父表上建索引，PostgreSQL 会为每个分区建立对应的局部索引，后续新分区也会自动继承：

CREATE INDEX idx_app_log_created_at ON app_log (created_at);
CREATE INDEX idx_app_log_level_created_at ON app_log (level, created_at);

查询时若同时带时间范围和 level，第二个索引更合适。

2. 插入跨月数据

INSERT INTO app_log (created_at, level, message)
SELECT ts,
       CASE WHEN extract(hour FROM ts) = 0 THEN 'ERROR' ELSE 'INFO' END,
       'msg-' || to_char(ts, 'YYYYMMDDHH24MI')
FROM generate_series(
       timestamptz '2025-01-01 00:00:00+08',
       timestamptz '2025-03-01 00:00:00+08' - interval '10 minutes',
       interval '10 minutes'
     ) AS ts;

数据覆盖 1 月到 2 月底，会分别进入 app_log_2025_01 和 app_log_2025_02。

3. 用 EXPLAIN 验证分区裁剪

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM app_log
WHERE created_at >= '2025-02-01 00:00:00+08'
  AND created_at <  '2025-03-01 00:00:00+08'
  AND level = 'ERROR';

执行计划中应只出现 app_log_2025_02，不出现 1 月、3 月和默认分区。如果出现多个分区，先检查：

• 条件是否直接作用在分区键 created_at 上；
• 是否写成 date_trunc('month', created_at) = ...，这种表达式无法裁剪；
• 时间常量是否与分区边界使用同一时区语义。

另外，分区裁剪发生在规划或执行阶段；用参数化查询时，建议确认执行计划中的实际扫描分区。

4. 默认分区与按月维护脚本

默认分区用于兜底，避免因漏建分区导致插入失败；但它不能替代按月建分区。若默认分区中已经存在目标月份的数据，再创建同范围分区会失败，因此脚本要先检查。

CREATE OR REPLACE FUNCTION ensure_month_partition(
    p_schema text,
    p_table  text,
    p_month  date
) RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    v_start   timestamptz;
    v_end     timestamptz;
    v_name    text;
    v_default text;
    v_cnt     bigint;
BEGIN
    v_start := make_timestamptz(
        extract(year  FROM p_month)::int,
        extract(month FROM p_month)::int,
        1, 0, 0, 0, 'Asia/Shanghai'
    );
    v_end := v_start + interval '1 month';
    v_name := p_table || '_' || to_char(p_month, 'YYYYMM');

    IF to_regclass(format('%I.%I', p_schema, v_name)) IS NOT NULL THEN
        RETURN;
    END IF;

    v_default := p_table || '_default';
    IF to_regclass(format('%I.%I', p_schema, v_default)) IS NOT NULL THEN
        EXECUTE format(
            'SELECT count(*) FROM %I.%I WHERE created_at >= $1 AND created_at < $2',
            p_schema, v_default
        ) INTO v_cnt USING v_start, v_end;

        IF v_cnt > 0 THEN
            RAISE EXCEPTION
                '默认分区中仍有 % 条目标月份数据，请先迁移再建分区', v_cnt;
        END IF;
    END IF;

    EXECUTE format(
        'CREATE TABLE %I.%I PARTITION OF %I.%I FOR VALUES FROM (%L) TO (%L)',
        p_schema, v_name, p_schema, p_table, v_start, v_end
    );
END;
$$;

调用方式：

SELECT ensure_month_partition('public', 'app_log', date '2025-04-01');

可按月由定时任务提前 2 到 3 个月调用，例如：

DO $$
BEGIN
    FOR i IN 0..2 LOOP
        PERFORM ensure_month_partition(
            'public', 'app_log',
            (date_trunc('month', now()) + (i || ' month')::interval)::date
        );
    END LOOP;
END $$;

保留策略可先用 ALTER TABLE ... DETACH PARTITION 分离旧分区，确认后再 DROP TABLE，避免直接删除影响查询。

5. 运行方法

将以上 SQL 保存为 partition_demo.sql，在测试库执行：

psql 'host=<host> port=5432 dbname=<db> user=<user>' -v ON_ERROR_STOP=1 -f partition_demo.sql

示例中的库名、账号、时区均为占位值，执行前按实际环境替换。

6. 边界与限制

• 分区键必须进入主键或唯一约束；日志表通常用 (id, created_at)，或改为普通索引。
• 分区数量不宜无限增长，需配合归档和删除；过多分区会增加规划和元数据开销。
• 查询条件必须能裁剪分区；对 created_at 使用函数、隐式类型转换或时区不一致都可能失去裁剪。
• 默认分区会承接所有未命中分区的数据，写入热点和后续建分区检查都需要关注。
• 在父表上建索引会影响所有分区，生产环境应在低峰期执行；建分区本身也会拿锁，建议纳入变更流程。