第九篇：《分区表实战：范围分区、列表分区、哈希分区》-北京尧图网络

在上一篇文章中我们通过 VACUUM 和 Autovacuum 治理了表的“存量垃圾”。但当一个表增长到数亿甚至数十亿行时即使没有膨胀全表扫描、全表索引维护、全表备份本身就会成为不可承受的成本。分区表的核心价值不是“让查询更快”而是“让维护可以分而治之” ——查询只扫描目标分区分区裁剪、旧数据用 DETACH 而非 DELETE 来清理、索引和备份可以按分区独立操作。本文从三种分区策略的选型讲起深入拆解分区裁剪的生效条件、ATTACH 与 DETACH 的锁行为差异这是运维中最容易踩坑的地方以及 PostgreSQL 19 的 MERGE PARTITIONS / SPLIT PARTITION 如何让分区策略“可演进”。最后通过一个按月日志分区表的完整实战帮你掌握分区表的建、用、维全流程。一、三种分区策略选型的核心是“数据怎么进来、怎么出去”PostgreSQL 声明式分区支持三种策略HASH FOR VALUES WITH (MODULUS n, REMAINDER r) 无明显区间/枚举特征均匀分散、无明显范围特征的数据一个经验法则RANGE 覆盖了 90% 以上的生产分区场景尤其是按时间分区。原因在于时间分区同时满足了三个需求分区裁剪查询最近 7 天只扫描 7 个分区、生命周期管理删除 90 天前的数据只需 DROP 或 DETACH 一个分区、以及增量维护只对最新分区建索引和备份。LIST 的适用场景当数据天然按少数枚举值分组、且各组的数据量和访问模式差异较大时。例如多租户 SaaS 按 tenant_id 分区但租户数量不宜过多——几十个租户适合 LIST几千个租户应该考虑 HASH 或按 tenant_id 的哈希值分区。HASH 的适用场景当数据没有自然的时间或枚举维度但需要均匀分散以避免单个分区过大时。HASH 分区的分区裁剪能力弱于 RANGE 和 LISTWHERE 条件通常只能命中一个分区除非查询本身包含取模条件它的主要价值是写入和存储的均匀分布。二、分区裁剪什么情况下“只扫一个分区”什么情况下“全部分区都扫”分区裁剪Partition Pruning是分区表性能收益的核心来源但它有明确的生效条件。2.1 裁剪的两个时机计划期裁剪优化器在生成执行计划时根据 WHERE 子句和分区的 CHECK 约束直接排除不满足条件的分区。这要求 WHERE 条件中的分区键是常量或计划期可确定的值。执行期裁剪PostgreSQL 11 当分区键的值在计划期未知如预编译语句的参数、嵌套循环连接中动态产生的值时裁剪推迟到执行期进行。2.2 裁剪失效的核心条件如果查询的 WHERE 条件中不包含分区键所有分区都会被扫描。这是分区裁剪最根本的限制。-- 分区键created_at-- ✅ 裁剪生效只扫描 2026-10 分区SELECT*FROMlogsWHEREcreated_at2026-10-01ANDcreated_at2026-11-01;-- ❌ 裁剪失效WHERE 中没有 created_at全部 12 个分区都会被扫描SELECT*FROMlogsWHEREuser_id123;另一个常见的裁剪失效场景对分区键使用函数或表达式例如 WHERE DATE(created_at) ‘2026-10-01’——这与 B-tree 索引失效的原理相同分区裁剪也无法识别“函数结果”与“分区边界”之间的关系。验证裁剪是否生效在 EXPLAIN 输出中观察是否出现 Append 节点以及 Append 下挂载的子计划数量。如果只有 1 个子计划说明裁剪成功如果有 12 个子计划说明全部分区都被扫描。三、ATTACH 与 DETACH锁行为是运维的关键分区表的日常运维涉及“添加新分区”和“分离旧分区”。这两个操作的锁行为差异极大选错就会在生产环境锁表。3.1 ATTACH PARTITION没有 CONCURRENTLY 选项ATTACH PARTITION 总是在父表上获取 SHARE UPDATE EXCLUSIVE 锁在新分区上获取 ACCESS EXCLUSIVE 锁。这意味着父表上的读写可以继续SHARE UPDATE EXCLUSIVE 与 ACCESS SHARE 和 ROW EXCLUSIVE 兼容。新分区在 ATTACH 期间不可访问ACCESS EXCLUSIVE 阻塞一切。在添加新分区时这个锁行为通常可以接受——新分区本来就没有数据在读写。但如果新分区是一个已经存在数据的大表例如将历史表挂载到分区父表ATTACH 需要在父表上扫描以验证约束可能持有锁的时间较长。3.2 DETACH PARTITION默认锁全表CONCURRENTLY 是生产必选项这是运维中最容易出问题的地方。默认的 DETACH PARTITION 在父表上获取 ACCESS EXCLUSIVE 锁——阻塞父表上的一切读写。对于一个正在被业务频繁访问的分区父表这会直接导致业务中断。DETACH PARTITION CONCURRENTLYPostgreSQL 14 在父表上只获取 SHARE UPDATE EXCLUSIVE 锁不阻塞业务的读写。它的实现是两阶段协议阶段 A在一个短暂的事务中将目标分区标记为 “detach pending”。这个事务只持有极短的锁。阶段 B在后台完成实际的分离操作不阻塞父表的写入。DETACH CONCURRENTLY 的两个严格限制不能在事务块中执行因为它需要跨多个事务阶段完成。当父表存在 DEFAULT 分区时禁止使用。生产环境的铁律在正在承载业务读写的分区父表上始终使用 DETACH PARTITION CONCURRENTLY。默认的 DETACH 只适合开发环境或确认没有并发写入的表。四、PostgreSQL 19分区策略“可演进”在 PostgreSQL 19 之前分区策略一旦设定就很难调整——分区太大只能手动拆建迁移分区太小太碎也只能忍受。PostgreSQL 19 引入了两个 ALTER TABLE 扩展让分区边界可以在不转储和重载数据的情况下重新调整4.1 MERGE PARTITIONS合并多个分区-- 把 Q1 和 Q2 合并为一个上半年分区ALTERTABLEcustomer_ordersMERGEPARTITIONS(orders_2026_q1,orders_2026_q2)INTOorders_2026_h1;关键机制合并复用现有的分区数据文件不进行逐行拷贝。这意味着即使合并的分区很大操作也是元数据级别的速度极快。4.2 SPLIT PARTITION拆分单个分区-- 把 Q3 分区按月度拆分为三个子分区ALTERTABLEcustomer_orders SPLITPARTITIONorders_2026_q3INTO(PARTITIONorders_2026_07FORVALUESFROM(2026-07-01)TO(2026-08-01),PARTITIONorders_2026_08FORVALUESFROM(2026-08-01)TO(2026-09-01),PARTITIONorders_2026_09FORVALUESFROM(2026-09-01)TO(2026-10-01));适用场景业务增长超预期个别分区变得异常大或者数据留存周期调整小分区太多太碎。合并与拆分的战略价值分区策略不再是“设计时一次决定、运行时不可更改”的刚性约束。你可以从粗粒度的季度分区开始随着数据量增长拆分为月度也可以把历史的小时分区合并为更大的归档分区——分区边界跟着业务节奏演进。五、分区表的索引父表建索引的传播与 CONCURRENTLY 的限制在分区父表上 CREATE INDEX 会自动在所有子分区上创建对应的索引。但这里有一个重要的限制你不能直接在父表上执行 CREATE INDEX CONCURRENTLY 。在生产环境为大分区表添加索引的标准流程是三步-- 第一步在每个分区上分别 CONCURRENTLY 创建索引不阻塞各分区读写CREATEINDEXCONCURRENTLY idx_logs_created_2026_10ONlogs_2026_10(created_at);-- 第二步在父表上创建“仅父表”的索引标记为 invalid不实际建索引CREATEINDEXidx_logs_createdONONLY logs(created_at);-- 第三步将每个分区的索引 attach 到父索引上ALTERINDEXidx_logs_created ATTACHPARTITIONidx_logs_created_2026_10;直接在父表上 CREATE INDEX非 CONCURRENTLY会对整个分区表加锁对大表来说是不可接受的。三步法保证了每一步都不阻塞业务读写是生产环境的标准操作。六、实战按月范围分区日志表场景一个事件日志表每月新增约 5000 万行需要保留 12 个月查询以“最近 N 天”为主。6.1 建表-- 父表按 created_at 范围分区CREATETABLEevent_logs(id BIGSERIAL,event_typeTEXTNOTNULL,payload JSONB,created_at TIMESTAMPTZNOTNULLDEFAULTNOW(),PRIMARYKEY(id,created_at)-- 分区键必须包含在主键中)PARTITIONBYRANGE(created_at);关键约束分区表的主键必须包含分区键。因为分区键决定了行在哪个物理分区中如果不包含分区键数据库无法保证主键在整个分区表中的唯一性。6.2 创建分区CREATETABLEevent_logs_2026_10PARTITIONOFevent_logsFORVALUESFROM(2026-10-01)TO(2026-11-01);CREATETABLEevent_logs_2026_11PARTITIONOFevent_logsFORVALUESFROM(2026-11-01)TO(2026-12-01);6.3 验证分区裁剪EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMevent_logsWHEREcreated_at2026-10-15ANDcreated_at2026-10-16;期望结果Append 节点下只有 event_logs_2026_10 一个子计划。如果出现 12 个子计划说明裁剪没有生效——检查 WHERE 是否引用了分区键、是否对分区键使用了函数。6.4 自动化分区管理pg_partman手动创建每月分区容易遗漏。pg_partman 扩展可以自动预创建未来分区并管理保留策略自动 detach 或 drop 旧分区 SELECTpartman.create_parent(p_parent_table :public.event_logs,p_control :created_at,p_interval :1 month,p_premake :3-- 预创建未来 3 个分区);配合 pg_cron 定时运行 partman.run_maintenance()可以实现零人工干预的分区生命周期管理。6.5 旧分区的清理DETACH CONCURRENTLY DROP-- 生产环境先 CONCURRENTLY 分离不阻塞读写ALTERTABLEevent_logs DETACHPARTITIONevent_logs_2025_10 CONCURRENTLY;-- 分离后分区成为独立表可以安全 DROPDROPTABLEevent_logs_2025_10;DETACH 而非 DELETE 是分区表生命周期管理的核心优势DELETE 需要逐行标记死元组再由 VACUUM 回收成本随行数线性增长DETACH 是元数据操作成本几乎恒定与分区大小无关。七、小结三种分区策略RANGE 覆盖 90% 场景尤其是时间分区LIST 适合少数枚举值分组HASH 适合均匀分散但裁剪能力弱。分区裁剪的硬条件WHERE 中必须包含分区键且不能对分区键使用函数。用 EXPLAIN 的 Append 子计划数量验证裁剪是否生效。ATTACH vs DETACH 的锁行为ATTACH 父表 SHARE UPDATE EXCLUSIVE生产可用默认 DETACH 是 ACCESS EXCLUSIVE阻塞一切读写生产环境必须用 DETACH CONCURRENTLYPG14 但它不能在事务块中执行且父表存在 DEFAULT 分区时禁止使用。PostgreSQL 19 的 MERGE PARTITIONS / SPLIT PARTITION 复用现有数据文件元数据级操作让分区策略可演进不再“一次设定、终身不变”。分区表索引的三步法分区上 CONCURRENTLY 建索引 → 父表 ONLY 建索引 → ATTACH 到父索引。不能直接在父表上 CREATE INDEX CONCURRENTLY 。生命周期管理用 pg_partman 自动预创建和保留管理用 DETACH CONCURRENTLY DROP 替代 DELETE成本从 O(n) 降到 O(1)。

第九篇：《分区表实战：范围分区、列表分区、哈希分区》

相关推荐

模3塌缩与诠释边界：贾子猜想n=3m约束的数论结构、哲学映射与AI可失败性

大模型数据模型选取

CH9433技术解析：一颗芯片搞定CAN总线和9线串口

更多资讯

AURIX看门狗深度解析：从ENDINIT保护到密码访问机制

医学图像分割技术：从U-Net到Transformer的演进与应用

高通QSEQ配置实战：QDART+QPST+NI-VISA三件套严丝合缝部署指南

梯度下降与交叉熵：AI模型训练的核心技术解析

基于AKShare的商品期货基差、库存与仓单数据链路构建

能源管理系统建设方案拆解：从采集、指标到告警工单闭环

今日更新

学生选课系统DFD图绘制指南：边界划分与分层校验实战

JIRA敏捷落地：Scrum角色权限、Sprint与燃尽图避坑指南

SEMI S8人机工程验收实战：从条款拆解到现场实测的完整指南

本周精选

防幻觉智能体架构：电源硬件设计中的大模型编排与工具化实践

OpenGL面试核心考点：渲染管线、矩阵变换与性能排查全解析

全国计算机二级C语言真题PDF高效刷题指南

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？