开发者日记

外观

从 3.2s 到 80ms：PG 分区表实战让大表查询提速 40 倍 ​

问题背景：10 亿行日志为什么把单表拖垮 ​

行为日志类业务通常具备三个特征：持续高写入、按时间范围查询、历史数据需要周期性归档。当单表累积到 10 亿行后，即使索引设计合理，也会遇到索引树加深、VACUUM 变慢、缓存命中率下降、历史数据删除引发膨胀等问题。一次常见的“查询最近 7 天异常点击”接口，在普通 B-tree 索引下仍要扫描大量索引条目并频繁回表，P95 延迟达到 3.2 秒。

PostgreSQL 12 之后，声明式分区已经可以覆盖绝大多数日志场景。本文使用一张按月 RANGE 分区的行为日志表，目标是：最近一个月查询控制在百毫秒内；按天写入稳定；历史分区可直接 DETACH 归档；通过分区裁剪避免扫描无关月份。

方案对比：单表索引、手工分表与声明式分区 ​

第一种方案是继续使用单表，仅增加时间与业务字段组合索引。它实现最简单，但索引体积持续膨胀，删除三个月前数据只能走 DELETE，容易产生表和索引膨胀。第二种方案是应用层手工分表，例如表名带月份后缀，路由规则写在业务代码中。这种方式短期性能好，但跨月查询、主键生成、ORM 兼容和归档脚本都会变复杂。

第三种方案是 PostgreSQL 原生声明式分区。父表只保存逻辑结构，数据落在各月子表中；查询条件带上分区键后，优化器可以进行分区裁剪；每个分区还能拥有本地索引。对于日志、订单、埋点等时间序列数据，它在可维护性和性能之间最均衡。

业务查询WHERE created_at...父表 event_logRANGE 分区定义2024-01 子分区2024-02 子分区（命中）2024-03 子分区分区裁剪只扫描目标月份

实操步骤一：创建按月 RANGE 分区父表 ​

分区键必须进入主键约束，这是许多团队第一次使用分区表最容易忽略的限制。下面示例可直接在测试库执行，使用 bigserial 风格的生成列与 created_at 组成复合主键。

sql
-- 行为日志父表：按 created_at 做月度 RANGE 分区
CREATE TABLE event_log (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    user_id     bigint NOT NULL,
    event_type  varchar(32) NOT NULL,
    props       jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at  timestamptz NOT NULL,
    CONSTRAINT pk_event_log PRIMARY KEY (created_at, id)
) PARTITION BY RANGE (created_at);

-- 为高频查询创建“父表级索引”，PostgreSQL 会自动在各分区创建本地索引
CREATE INDEX idx_event_log_user_time
    ON event_log (user_id, created_at DESC);

CREATE INDEX idx_event_log_type_time
    ON event_log (event_type, created_at DESC);

这里没有把 id 单独作为主键，因为分区表的唯一约束必须包含分区键。复合主键 (created_at, id) 仍能保证全局唯一性需求：Identity 序列负责生成唯一 id，时间字段负责分区定位。

实操步骤二：创建月度分区并提前建表 ​

可以手动创建几个月分区验证功能：

sql
CREATE TABLE event_log_2024_01
PARTITION OF event_log
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE event_log_2024_02
PARTITION OF event_log
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE event_log_2024_03
PARTITION OF event_log
FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

生产环境建议使用 pg_partman 或定时任务提前创建未来 3 个月分区。纯 SQL 定时函数也能满足简单场景：

sql
CREATE OR REPLACE FUNCTION create_monthly_partition(p_month date)
RETURNS void AS $$
DECLARE
    v_start date := date_trunc('month', p_month)::date;
    v_end   date := (date_trunc('month', p_month) + interval '1 month')::date;
    v_name  text := format('event_log_%s', to_char(v_start, 'YYYY_MM'));
BEGIN
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF event_log FOR VALUES FROM (%L) TO (%L)',
        v_name, v_start, v_end
    );
END;
$$ LANGUAGE plpgsql;

-- 提前创建未来三个月分区
SELECT create_monthly_partition('2024-04-01');
SELECT create_monthly_partition('2024-05-01');
SELECT create_monthly_partition('2024-06-01');

注意边界使用左闭右开区间。2024-02-01 00:00:00 的数据属于 2 月分区，而不会进入 1 月分区。

实操步骤三：验证分区裁剪是否生效 ​

插入测试数据后，使用 EXPLAIN 检查计划中出现的分区数量。

sql
INSERT INTO event_log(user_id, event_type, props, created_at)
SELECT
    (random() * 1000000)::bigint,
    CASE WHEN random() > 0.5 THEN 'click' ELSE 'view' END,
    jsonb_build_object('page', '/home', 'rank', (random()*10)::int),
    ts
FROM generate_series(
    '2024-01-01'::timestamptz,
    '2024-03-20'::timestamptz,
    interval '10 second'
) AS s(ts);

-- 关键查询：条件中必须直接约束分区键 created_at
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, event_type, created_at
FROM event_log
WHERE user_id = 88888
  AND created_at >= '2024-02-01'
  AND created_at <  '2024-03-01'
ORDER BY created_at DESC
LIMIT 100;

理想计划只会包含 event_log_2024_02，类似下面的形态：

text
Append
  ->  Index Scan using event_log_2024_02_user_id_created_at_idx
        Index Cond: ((user_id = 88888) AND ...)

如果计划里出现大量 event_log_2024_01、event_log_2024_03，说明没有裁剪成功。常见原因是对分区键包了函数、参数类型不一致，或者启用了不支持裁剪的访问方式。

实操步骤四：数据迁移与历史归档 ​

从现有大表迁移时，不建议一次性 INSERT INTO ... SELECT 全部数据，这会产生巨大事务和 WAL。应按月份批量迁移：

sql
-- 每次迁移一个月，控制事务大小和锁持有时间
INSERT INTO event_log (id, user_id, event_type, props, created_at)
SELECT id, user_id, event_type, props, created_at
FROM event_log_old
WHERE created_at >= '2024-02-01'
  AND created_at <  '2024-03-01'
ORDER BY created_at, id;

-- 迁移后用系统视图核对行数
SELECT relname, n_live_tup
FROM pg_stat_user_tables
WHERE relname LIKE 'event_log_2024_%'
ORDER BY relname;

历史数据到期后，不要逐行删除，直接卸载分区：

sql
-- 从父表脱离，但保留物理表，适合先备份再清理
ALTER TABLE event_log DETACH PARTITION event_log_2024_01;

-- 确认归档完成后再删除
DROP TABLE event_log_2024_01;

DETACH 比大规模 DELETE 更适合日志留存策略，基本不会造成父表和其他月份索引膨胀。

实测效果：为什么能从 3.2 秒降到 80 毫秒 ​

调优前，单表组合索引约 210GB，查询最近 7 天数据虽然走了索引，但仍需在庞大的索引结构中定位，并与状态、类型等过滤条件做大量回表判断。按月分区后，当前月份本地索引只有约 18GB，热点数据更容易进入共享缓存；分区裁剪把扫描范围从 10 亿行缩小到约 7000 万行；再配合 (user_id, created_at DESC) 本地索引，查询只访问少量叶子页和堆元组。

在相同应用负载下，典型查询从 3.2 秒下降到 80 毫秒，提升约 40 倍。更重要的收益是维护能力：月度归档从耗时 DELETE 变为元数据操作；VACUUM 可集中处理热点分区；历史数据可以按分区备份或迁移到冷存储。

避坑总结：分区表不是建完就一定快 ​

  1. 查询条件对分区键使用函数，导致裁剪失效。 例如写成 date_trunc('day', created_at) = '2024-02-10'，执行计划可能扫描所有月份。应改为范围条件：created_at >= '2024-02-10' AND created_at < '2024-02-11'。

  2. 主键缺少分区键，建表时报错。 常见错误是 PRIMARY KEY (id)，PostgreSQL 会提示唯一约束必须包含分区键。修正为 PRIMARY KEY (created_at, id)，并让业务查询尽量同时携带时间范围。

  3. 只创建父表、忘记未来分区，写入直接失败。 当插入没有匹配分区的时间点时，会出现 no partition of relation found for row。需要用 pg_partman 或调度任务预建分区，并增加监控告警。

  4. 在父表上创建全局索引的预期不现实。 PostgreSQL 普通分区索引本质上是各分区本地索引，跨很多分区的查询仍会启动多个索引扫描。分区数量应结合留存周期设计，日志场景按月通常比按天更稳妥，避免分区过多导致规划开销上升。

  5. 迁移时一次性灌入全部历史数据。 现象是 WAL 暴增、复制延迟拉长、回滚时间不可接受。应按月份分批提交，迁移前后使用 pg_stat_user_tables 与行数抽样校验，再切换应用读流量。

分区表的核心价值不是“把一张表魔法般变快”，而是把数据的物理组织方式与业务访问模式对齐。只要查询稳定携带时间范围、分区提前创建、索引按月本地化，10 亿行行为日志也能保持稳定且可预测的性能。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。