PostgreSQL 分区表的原理、实践与 MySQL 对比

PostgreSQL 分区表的原理、实践与 MySQL 对比

Elvis Lv2

记录 AI 调用日志时,我关心两个操作:按时间统计失败率,以及清理超过保留期限的历史记录。每天百万级的写入让这两个操作值得提前设计,但仅凭每天的行数,不能判断单表已经不可用,更不能推导出分区后查询一定更快。

这篇文章围绕 ai_call_record 的时间范围查询和生命周期管理展开。SQL 以 PostgreSQL 17 为目标;MySQL 部分参照 8.4 文档讨论功能约束。文中提供的是机制验证方法,不是两种数据库的性能排名。

先从查询和保留周期决定分区键

对于调用日志,可以先列出访问方式,再决定是否按天分区:

访问方式 按 started_at 分区的意义 还需要处理的问题
统计最近一天各渠道的失败数 查询条件能限定时间范围 单日内的过滤、聚合仍有成本
排查某次调用 如果同时知道时间,能缩小范围 只有 call_id 时仍需考虑多个分区
删除超过保留期的日志 可以按完整时间段管理数据 迟到写入、归档和锁等待需要设计

如果大部分请求都只有 call_id,时间分区未必是查询优化的首选。可以先评估普通表的索引和执行计划,再决定是否为数据清理引入分区。分区粒度也应结合保留周期选择:按天保留一年意味着大约 365 个分区,不能只测试当前两三个分区的情况。

本文选择 UTC 日分区,是为了让存储边界不随客户端时区改变。业务按北京时间统计一天时,应先计算该业务日期对应的 UTC 起止时刻,再把两个时间参数传给 SQL。

表结构:把时间边界写清楚

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE ai_call_record (
id BIGINT GENERATED ALWAYS AS IDENTITY,
call_id UUID NOT NULL,
channel TEXT NOT NULL,
status TEXT NOT NULL,
started_at TIMESTAMPTZ NOT NULL,
PRIMARY KEY (id, started_at)
) PARTITION BY RANGE (started_at);

CREATE TABLE ai_call_record_20251113
PARTITION OF ai_call_record
FOR VALUES FROM ('2025-11-13 00:00:00+00')
TO ('2025-11-14 00:00:00+00');

这里的区间包含下界、不包含上界。因此 2025-11-14 00:00:00+00 必须进入下一天的分区。显式写出 +00 可以避免建分区时依赖会话时区。

复合主键里的 started_at 也有意义:PostgreSQL 分区表上的主键或唯一约束必须包含所有分区键列。PRIMARY KEY (id, started_at) 并不单独约束 id 在整个表中唯一;同样,UNIQUE (call_id, started_at) 也不能保证一个 call_id 只出现一次。需要跨日期幂等时,可以另设以 call_id 为唯一键的登记表,并在同一事务里完成登记和日志写入,但要评估这张表的保留策略及额外写入成本。PostgreSQL 分区约束说明

用一份小数据脚本验证三个行为

下载 完整 SQL 示例,在临时数据库中运行:

1
psql -X -v ON_ERROR_STOP=1 -d partition_lab -f postgres-partition-demo.sql

partition_lab 需要提前创建,连接用户需要有创建 schema 的权限。脚本在事务内创建独立的 partition_demo schema,最后执行 ROLLBACK,不会保留示例表。如果同名 schema 已经存在,脚本会报错停止,不会覆盖它。

脚本创建连续三天的分区,并生成每分钟一条、共 4320 条模拟记录。数据量刻意保持很小,方便检查路由和边界;它不能证明百万级写入的吞吐。

需要观察三个结果:

  1. 用 tableoid::regclass 分组,每个分区预期有 1440 条记录。
  2. 零点那条记录预期位于新一天的分区,脚本会检查这一点。
  3. 向第四天写入时,由于没有对应分区且未设置默认分区,预期写入失败。脚本捕获这个预期错误;如果意外成功,则抛出异常。

这些检查可以暴露“分区建出来了,但边界或覆盖日期不对”的问题。它们不会替代并发写入、迁移和容量测试。

Docker 验证记录

本示例已在 Docker 中的 PostgreSQL 17.10(Debian 17.10-1.pgdg13+1,aarch64)执行通过。该环境的实际结果如下:

检查项 实际结果
模拟数据写入 共写入 4320 条,三个日分区各 1440 条
零点记录路由 2025-11-14 00:00:00+00 进入 ai_call_record_20251114,断言通过
单日时间范围查询 仅扫描 ai_call_record_20251114,返回计数 1440
仅按 call_id 查询 Append 下扫描全部三个分区,在 11 月 14 日分区匹配到一条记录
缺失分区写入 第四天的写入被拒绝,预期异常检查通过
事务回滚 ROLLBACK 完成后,独立连接确认 partition_demo schema 不存在

两条查询的执行计划结构分别为:

1
2
3
4
5
6
7
8
9
单日时间范围:
Aggregate
-> Seq Scan on ai_call_record_20251114

仅按 call_id:
Append
-> Seq Scan on ai_call_record_20251113
-> Seq Scan on ai_call_record_20251114
-> Seq Scan on ai_call_record_20251115

这次运行确认了示例中的路由、裁剪、失败处理和回滚行为。4320 条模拟记录下的单次执行耗时不用于评估生产容量,也不能用于比较 PostgreSQL 与 MySQL 的性能。

用执行计划确认分区裁剪

完整脚本还包含两条 EXPLAIN (ANALYZE, BUFFERS)。第一条明确限定一天:

1
2
3
4
5
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM ai_call_record
WHERE started_at >= TIMESTAMPTZ '2025-11-14 00:00:00+00'
AND started_at < TIMESTAMPTZ '2025-11-15 00:00:00+00';

在默认启用分区裁剪的情况下,应检查计划是否仅访问 ai_call_record_20251114。小数据上看到 Seq Scan 并不意味着裁剪失败:先看扫描的是哪些分区,再看每个分区内部如何访问数据。

第二条查询仅使用 call_id:

1
2
3
4
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM ai_call_record
WHERE call_id = '00000000-0000-0000-0000-000000001500';

它没有时间范围,不能根据 started_at 排除其他分区。为 call_id 建索引可能降低各分区内部的查找成本,但不会自动补出缺失的时间条件。需要分别评估“访问多少个分区”和“每个分区访问多少数据”。PostgreSQL 分区裁剪说明

实际接口最好直接传入起止时刻,使用半开区间查询;不要先把时间列转成日期或字符串,再假定优化器会得到相同的裁剪结果。针对最终 SQL 和实际参数检查执行计划,比根据 SQL 外观猜测可靠。

自动建分区和历史清理都需要失败路径

每日执行一次建分区任务,只覆盖今天和明天,无法应对任务连续失败或者历史数据回补。可以按“允许补写的历史窗口 + 未来覆盖窗口”生成分区清单,并让监控检查覆盖到的最晚时刻,而不只是检查任务是否返回成功。

对于这个日志场景,上线前至少需要明确以下行为:

  • 跨日写入:午夜前后持续写入时,下一天的分区必须已就绪。
  • 迟到记录:日志可能在调用发生数小时甚至数天后到达,要约定接受的窗口。
  • 任务并发:只允许一个管理任务执行同一批 DDL,失败后能重试;不能把 IF NOT EXISTS 当成结构校验。
  • 默认分区:如果使用它接住未覆盖日期的数据,需要监控积压并设计迁移流程,避免它长期隐藏建分区故障。
  • 删除边界:只有分区上界不晚于保留截止时刻,才适合整分区清理;包含仍需保留记录的分区不能直接删除。

删除整个分区可以避免逐行删除的工作,但仍涉及锁和依赖关系,不能承诺“永远秒级且不阻塞”。应在演练环境检查长事务、锁等待、归档结果和恢复步骤,再选择直接删除或先分离分区的维护方式。PostgreSQL 分区维护说明

与 MySQL 对比:先比较约束,再做测量

MySQL 8.4 也有分区裁剪,不能用“PostgreSQL 会裁剪,MySQL 会全表扫描”区分两者。应对照各自最终的表结构、条件表达式和执行计划判断。MySQL 分区裁剪文档

MySQL 的唯一键同样受分区键约束:分区表达式中使用的列必须包含在每个唯一键中。因此,把 call_id 的全局幂等约束搬到按时间分区的表时,两边都需要重新审视数据模型。MySQL 分区键与唯一键限制

这篇文章没有同条件的 PostgreSQL/MySQL 对照压测,因此不提供谁更快、谁更适合所有日志系统的结论。若要比较,至少固定以下输入:

输入 应记录的内容
数据集 总行数、行宽、时间跨度、渠道分布和保留周期
表与索引 分区数量、索引定义、唯一约束及统计信息
查询 时间窗口、返回行数、参数、排序与聚合方式
运行条件 数据库版本、CPU、内存、存储、并发及缓存状态
结果 多轮延迟、吞吐、错误、执行计划及维护期间的锁等待

对 AI 调用日志,我会先验证“带时间范围的排查”和“到期数据清理”是否解决了具体问题,再衡量分区管理增加的成本。如果查询多数没有时间条件,或者现有普通表已经满足需求,就没有必要为了分区而迁移。

  • 标题: PostgreSQL 分区表的原理、实践与 MySQL 对比
  • 作者: Elvis
  • 创建于 : 2025-11-14 10:00:00
  • 更新于 : 2026-10-25 16:00:00
  • 链接: https://qianwj.github.io/2025/11/14/postgres_partition/
  • 版权声明: 本文章采用 CC BY-NC-SA 4.0 进行许可。