文章目录每日一句正能量1. 背景与问题2. 环境与数据3. 复现过程4. 方案实施在线重整工具pg_repack与 VACUUM FULL 对比5. 结果对比6. 风险与复盘故障排查实战案例VACUUM FULL 锁等待超时1. 故障模拟2. 诊断过程3. 处理步骤4. 最终效果5. 排查思路总结本文围绕 PostgreSQL 高更新表膨胀问题从诊断、复现到治理与复盘给出完整实践路径。每日一句正能量日拱一卒功不唐捐。目标遥不可及那就专注于今天能推进的这一小步。不追求石破天惊只相信细水长流。坚持每天推进一小步积累终会带来改变。1. 背景与问题高频 UPDATE/DELETE 业务容易产生大量失效元组Dead Tuple导致表膨胀、索引膨胀、扫描变慢和磁盘空间持续增长。某订单系统核心业务表每天更新超过千万次磁盘容量持续告警查询响应逐渐变慢因此开展表膨胀诊断和空间治理演练。2. 环境与数据PostgreSQL 16Linux 9数据规模1.6TB高更新表orders诊断SQLSELECTrelname,n_live_tup,n_dead_tupFROMpg_stat_user_tablesORDERBYn_dead_tupDESC;SELECTpg_size_pretty(pg_total_relation_size(orders));目标RTO≤30分钟RPO≤5分钟3. 复现过程故障注入持续批量UPDATE和DELETE。暂停自动VACUUM仅测试环境。观察Dead Tuple增长。记录查询性能和空间变化。监控n_dead_tup表大小查询耗时Autovacuum日志4. 方案实施部署/演练流程评估膨胀比例。执行VACUUM (ANALYZE)。必要时使用VACUUM FULL或在线重整工具。重建膨胀索引。恢复业务并验证。下面是完整的治理流程图从评估膨胀比例到恢复业务验证每个步骤都标注了关键输出或检查点渲染错误:Mermaid 渲染失败: Lexical error on line 9. Unrecognized text. ...、业务健康检查| G[完成]示例sqlVACUUM (AN ----------------------^检查项Dead Tuple下降表空间回收查询恢复主备同步正常业务健康检查在线重整工具pg_repack与 VACUUM FULL 对比当表膨胀严重且业务无法接受长时间阻塞时可选用在线重整工具如 pg_repack替代 VACUUM FULL。两者对比如下对比维度VACUUM FULLpg_repack锁行为全程持有 AccessExclusiveLock阻塞读写仅在创建/交换临时表瞬间短暂加锁业务基本无感空间需求需要与表等量的额外磁盘空间同样需要额外空间但可分批处理执行速度单线程重写大表耗时较长可并行整体更快索引重建需单独 REINDEX自动重建索引适用场景可接受停机窗口、表较小或低峰期7x24 在线业务、大表、无法接受长时间阻塞风险锁等待超时、主备延迟依赖触发器/日志表需预留空间并关注主备延迟pg_repack 基本使用示例# 安装以 CentOS/RHEL 为例yuminstallpg_repack# 对 orders 表在线重整需超级用户或表属主pg_repack-h127.0.0.1-p5432-Upostgres-dpostgres-torders# 若表有主键可加 --no-order 跳过按主键排序减少 IOpg_repack-h127.0.0.1-p5432-Upostgres-dpostgres-torders --no-order提示pg_repack 执行期间会短暂持有排他锁建议仍避开业务最高峰同时确保磁盘有足够空间存放临时表并关注主备延迟。针对 orders 表每天千万级更新的高膨胀场景建议按以下参数调优 Autovacuum让回收更及时、避免膨胀累积参数名当前默认值建议值适用场景说明autovacuum_vacuum_scale_factor0.20.05默认按表大小 20% 触发高更新表会积压大量 Dead Tuple调低后更早触发 VACUUMautovacuum_vacuum_threshold501000与 scale_factor 配合避免小表频繁触发orders 表较大可适当提高阈值autovacuum_naptime60s30s缩短两次自动 VACUUM 的间隔让高更新表更快进入回收队列autovacuum_max_workers36增加并行 worker避免多个大表同时膨胀时排队等待autovacuum_analyze_scale_factor0.10.02更频繁更新统计信息保证查询计划不因数据变化而劣化orders 表配置示例按表级参数覆盖全局默认值ALTERTABLEordersSET(autovacuum_vacuum_scale_factor0.05,autovacuum_vacuum_threshold1000,autovacuum_analyze_scale_factor0.02);上图展示了调优 Autovacuum 参数后Dead Tuple 回收效率与表空间释放的对比效果。5. 结果对比指标治理前治理后表大小860GB610GBDead Tuple1.2亿430万平均查询耗时420ms170msRTO29分钟24分钟RPO5分钟2分钟治理后空间释放明显索引扫描效率恢复业务高峰响应稳定。6. 风险与复盘风险VACUUM FULL需要额外空间并可能阻塞。REINDEX会消耗IO应避开业务高峰。未提前备份和演练可能增加维护风险。复盘建议建立表膨胀定期巡检。结合Autovacuum参数持续优化。每次治理记录RTO、RPO、耗时和空间回收率。建立检查清单备份、主备、空间、业务验证、回滚方案。故障回滚方案若 VACUUM FULL 或 REINDEX 过程中出现异常如锁等待超时、磁盘空间不足、主备延迟过大按以下步骤回滚停止操作立即终止当前 VACUUM FULL / REINDEX 会话避免持续占用 IO 和锁资源。恢复业务确认表锁已释放恢复业务读写若主库受影响优先切换或恢复主备连接。检查主备同步通过 pg_stat_replication 确认主备延迟恢复WAL 发送与接收正常。检查空间释放确认异常中断后表空间未异常增长必要时清理临时文件或 WAL 堆积。记录并复盘记录异常原因、耗时与影响范围更新检查清单和回滚预案。故障排查速查表故障现象可能原因快速诊断命令处理措施VACUUM FULL 锁等待超时业务长事务或高并发读写持有表锁SELECT pid, state, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event_type Lock;终止阻塞会话或等待长事务结束错峰执行 VACUUM FULL磁盘空间不足表膨胀占用大量空间VACUUM FULL 需要额外空间SELECT pg_size_pretty(pg_total_relation_size(orders));先清理 WAL/临时文件释放空间或改用在线重整工具分批处理主备延迟过大大事务或 VACUUM FULL 产生大量 WAL备库回放跟不上SELECT * FROM pg_stat_replication;暂停写入高峰等待备库追平延迟必要时调整 max_wal_senders 或同步策略Dead Tuple 不下降Autovacuum 未及时触发或参数配置过保守SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname orders;调低 autovacuum_vacuum_scale_factor、缩短 naptime必要时手动 VACUUM (ANALYZE)故障排查实战案例VACUUM FULL 锁等待超时下面通过一次完整的故障演练演示从发现锁等待超时到恢复业务的全过程。下面是本次故障排查的完整流程图从故障模拟到恢复验证每个环节都标注了关键诊断命令或检查点会话 A 长事务持有表锁日志canceling statement due to lock timeout检查点找到持有锁的会话 28102检查点确认阻塞来源是测试事务否生产事务检查点无阻塞检查点表空间回收检查点索引扫描效率恢复检查点主备同步正常、业务健康故障模拟业务高峰执行 VACUUM FULL触发锁等待超时诊断pg_stat_activity 定位阻塞源确认阻塞链pg_blocking_pids长事务是否可终止pg_terminate_backend 终止会话与业务方确认等待事务结束确认锁已释放错峰重新执行 VACUUM FULLREINDEX 重建膨胀索引验证表大小、Dead Tuple、查询耗时完成并复盘1. 故障模拟在业务高峰时段对 orders 表执行 VACUUM FULL同时模拟一个长事务持续持有表锁-- 会话 A模拟业务长事务持有 orders 表锁BEGIN;UPDATEordersSETstatusprocessingWHEREorder_id100000;-- 故意不提交模拟长事务-- 会话 B执行 VACUUM FULL等待表锁VACUUMFULLorders;会话 B 长时间无法获取锁最终触发锁等待超时日志片段如下2026-08-31 14:32:18.123 CST [28471] ERROR: canceling statement due to lock timeout 2026-08-31 14:32:18.123 CST [28471] DETAIL: process 28471 still waiting for AccessExclusiveLock on relation orders of database postgres after 30000.123 ms 2026-08-31 14:32:18.123 CST [28471] STATEMENT: VACUUM FULL orders;2. 诊断过程通过 pg_stat_activity 定位阻塞源SELECTpid,state,wait_event_type,wait_event,queryFROMpg_stat_activityWHEREwait_event_typeLock;-- 结果-- pid | state | wait_event_type | wait_event | query-- 28471 | active | Lock | relation | VACUUM FULL orders;-- 28102 | idle in transaction | Lock | relation | UPDATE orders SET status processing WHERE order_id 100000;进一步确认阻塞链SELECTblocked.pidASblocked_pid,blocking.pidASblocking_pid,blocking.queryASblocking_queryFROMpg_stat_activity blockedJOINpg_stat_activity blockingONblocking.pidANY(pg_blocking_pids(blocked.pid))WHEREblocked.queryILIKE%VACUUM FULL%;3. 处理步骤与业务方确认长事务是否可以结束若为测试事务直接终止SELECTpg_terminate_backend(28102);确认锁已释放后错峰重新执行 VACUUM FULLVACUUMFULLorders;重建膨胀索引并验证REINDEXTABLEorders;SELECTpg_size_pretty(pg_total_relation_size(orders));4. 最终效果指标故障前处理后表大小860GB610GBDead Tuple1.2亿430万平均查询耗时420ms170ms锁等待超时无阻塞5. 排查思路总结先定位阻塞源通过 pg_stat_activity 和 pg_blocking_pids 找到持有锁的会话判断是长事务还是高并发读写。区分处理策略测试事务可直接终止生产长事务需与业务方确认避免误杀关键任务。错峰执行VACUUM FULL 和 REINDEX 尽量安排在业务低峰降低锁冲突概率。事后复盘记录锁等待时长、阻塞来源和处理耗时更新检查清单必要时调优 Autovacuum 参数减少膨胀累积。本文结合部署流程、故障注入、诊断SQL、治理前后对比以及RTO/RPO验证总结了高更新表空间治理的实践方法。转载自https://blog.csdn.net/u014727709/article/details/164125404欢迎 点赞✍评论⭐收藏欢迎指正 表膨胀诊断与空间治理——高更新表优化实践
文章目录每日一句正能量1. 背景与问题2. 环境与数据3. 复现过程4. 方案实施在线重整工具pg_repack与 VACUUM FULL 对比5. 结果对比6. 风险与复盘故障排查实战案例VACUUM FULL 锁等待超时1. 故障模拟2. 诊断过程3. 处理步骤4. 最终效果5. 排查思路总结本文围绕 PostgreSQL 高更新表膨胀问题从诊断、复现到治理与复盘给出完整实践路径。每日一句正能量日拱一卒功不唐捐。目标遥不可及那就专注于今天能推进的这一小步。不追求石破天惊只相信细水长流。坚持每天推进一小步积累终会带来改变。1. 背景与问题高频 UPDATE/DELETE 业务容易产生大量失效元组Dead Tuple导致表膨胀、索引膨胀、扫描变慢和磁盘空间持续增长。某订单系统核心业务表每天更新超过千万次磁盘容量持续告警查询响应逐渐变慢因此开展表膨胀诊断和空间治理演练。2. 环境与数据PostgreSQL 16Linux 9数据规模1.6TB高更新表orders诊断SQLSELECTrelname,n_live_tup,n_dead_tupFROMpg_stat_user_tablesORDERBYn_dead_tupDESC;SELECTpg_size_pretty(pg_total_relation_size(orders));目标RTO≤30分钟RPO≤5分钟3. 复现过程故障注入持续批量UPDATE和DELETE。暂停自动VACUUM仅测试环境。观察Dead Tuple增长。记录查询性能和空间变化。监控n_dead_tup表大小查询耗时Autovacuum日志4. 方案实施部署/演练流程评估膨胀比例。执行VACUUM (ANALYZE)。必要时使用VACUUM FULL或在线重整工具。重建膨胀索引。恢复业务并验证。下面是完整的治理流程图从评估膨胀比例到恢复业务验证每个步骤都标注了关键输出或检查点渲染错误:Mermaid 渲染失败: Lexical error on line 9. Unrecognized text. ...、业务健康检查| G[完成]示例sqlVACUUM (AN ----------------------^检查项Dead Tuple下降表空间回收查询恢复主备同步正常业务健康检查在线重整工具pg_repack与 VACUUM FULL 对比当表膨胀严重且业务无法接受长时间阻塞时可选用在线重整工具如 pg_repack替代 VACUUM FULL。两者对比如下对比维度VACUUM FULLpg_repack锁行为全程持有 AccessExclusiveLock阻塞读写仅在创建/交换临时表瞬间短暂加锁业务基本无感空间需求需要与表等量的额外磁盘空间同样需要额外空间但可分批处理执行速度单线程重写大表耗时较长可并行整体更快索引重建需单独 REINDEX自动重建索引适用场景可接受停机窗口、表较小或低峰期7x24 在线业务、大表、无法接受长时间阻塞风险锁等待超时、主备延迟依赖触发器/日志表需预留空间并关注主备延迟pg_repack 基本使用示例# 安装以 CentOS/RHEL 为例yuminstallpg_repack# 对 orders 表在线重整需超级用户或表属主pg_repack-h127.0.0.1-p5432-Upostgres-dpostgres-torders# 若表有主键可加 --no-order 跳过按主键排序减少 IOpg_repack-h127.0.0.1-p5432-Upostgres-dpostgres-torders --no-order提示pg_repack 执行期间会短暂持有排他锁建议仍避开业务最高峰同时确保磁盘有足够空间存放临时表并关注主备延迟。针对 orders 表每天千万级更新的高膨胀场景建议按以下参数调优 Autovacuum让回收更及时、避免膨胀累积参数名当前默认值建议值适用场景说明autovacuum_vacuum_scale_factor0.20.05默认按表大小 20% 触发高更新表会积压大量 Dead Tuple调低后更早触发 VACUUMautovacuum_vacuum_threshold501000与 scale_factor 配合避免小表频繁触发orders 表较大可适当提高阈值autovacuum_naptime60s30s缩短两次自动 VACUUM 的间隔让高更新表更快进入回收队列autovacuum_max_workers36增加并行 worker避免多个大表同时膨胀时排队等待autovacuum_analyze_scale_factor0.10.02更频繁更新统计信息保证查询计划不因数据变化而劣化orders 表配置示例按表级参数覆盖全局默认值ALTERTABLEordersSET(autovacuum_vacuum_scale_factor0.05,autovacuum_vacuum_threshold1000,autovacuum_analyze_scale_factor0.02);上图展示了调优 Autovacuum 参数后Dead Tuple 回收效率与表空间释放的对比效果。5. 结果对比指标治理前治理后表大小860GB610GBDead Tuple1.2亿430万平均查询耗时420ms170msRTO29分钟24分钟RPO5分钟2分钟治理后空间释放明显索引扫描效率恢复业务高峰响应稳定。6. 风险与复盘风险VACUUM FULL需要额外空间并可能阻塞。REINDEX会消耗IO应避开业务高峰。未提前备份和演练可能增加维护风险。复盘建议建立表膨胀定期巡检。结合Autovacuum参数持续优化。每次治理记录RTO、RPO、耗时和空间回收率。建立检查清单备份、主备、空间、业务验证、回滚方案。故障回滚方案若 VACUUM FULL 或 REINDEX 过程中出现异常如锁等待超时、磁盘空间不足、主备延迟过大按以下步骤回滚停止操作立即终止当前 VACUUM FULL / REINDEX 会话避免持续占用 IO 和锁资源。恢复业务确认表锁已释放恢复业务读写若主库受影响优先切换或恢复主备连接。检查主备同步通过 pg_stat_replication 确认主备延迟恢复WAL 发送与接收正常。检查空间释放确认异常中断后表空间未异常增长必要时清理临时文件或 WAL 堆积。记录并复盘记录异常原因、耗时与影响范围更新检查清单和回滚预案。故障排查速查表故障现象可能原因快速诊断命令处理措施VACUUM FULL 锁等待超时业务长事务或高并发读写持有表锁SELECT pid, state, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event_type Lock;终止阻塞会话或等待长事务结束错峰执行 VACUUM FULL磁盘空间不足表膨胀占用大量空间VACUUM FULL 需要额外空间SELECT pg_size_pretty(pg_total_relation_size(orders));先清理 WAL/临时文件释放空间或改用在线重整工具分批处理主备延迟过大大事务或 VACUUM FULL 产生大量 WAL备库回放跟不上SELECT * FROM pg_stat_replication;暂停写入高峰等待备库追平延迟必要时调整 max_wal_senders 或同步策略Dead Tuple 不下降Autovacuum 未及时触发或参数配置过保守SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname orders;调低 autovacuum_vacuum_scale_factor、缩短 naptime必要时手动 VACUUM (ANALYZE)故障排查实战案例VACUUM FULL 锁等待超时下面通过一次完整的故障演练演示从发现锁等待超时到恢复业务的全过程。下面是本次故障排查的完整流程图从故障模拟到恢复验证每个环节都标注了关键诊断命令或检查点会话 A 长事务持有表锁日志canceling statement due to lock timeout检查点找到持有锁的会话 28102检查点确认阻塞来源是测试事务否生产事务检查点无阻塞检查点表空间回收检查点索引扫描效率恢复检查点主备同步正常、业务健康故障模拟业务高峰执行 VACUUM FULL触发锁等待超时诊断pg_stat_activity 定位阻塞源确认阻塞链pg_blocking_pids长事务是否可终止pg_terminate_backend 终止会话与业务方确认等待事务结束确认锁已释放错峰重新执行 VACUUM FULLREINDEX 重建膨胀索引验证表大小、Dead Tuple、查询耗时完成并复盘1. 故障模拟在业务高峰时段对 orders 表执行 VACUUM FULL同时模拟一个长事务持续持有表锁-- 会话 A模拟业务长事务持有 orders 表锁BEGIN;UPDATEordersSETstatusprocessingWHEREorder_id100000;-- 故意不提交模拟长事务-- 会话 B执行 VACUUM FULL等待表锁VACUUMFULLorders;会话 B 长时间无法获取锁最终触发锁等待超时日志片段如下2026-08-31 14:32:18.123 CST [28471] ERROR: canceling statement due to lock timeout 2026-08-31 14:32:18.123 CST [28471] DETAIL: process 28471 still waiting for AccessExclusiveLock on relation orders of database postgres after 30000.123 ms 2026-08-31 14:32:18.123 CST [28471] STATEMENT: VACUUM FULL orders;2. 诊断过程通过 pg_stat_activity 定位阻塞源SELECTpid,state,wait_event_type,wait_event,queryFROMpg_stat_activityWHEREwait_event_typeLock;-- 结果-- pid | state | wait_event_type | wait_event | query-- 28471 | active | Lock | relation | VACUUM FULL orders;-- 28102 | idle in transaction | Lock | relation | UPDATE orders SET status processing WHERE order_id 100000;进一步确认阻塞链SELECTblocked.pidASblocked_pid,blocking.pidASblocking_pid,blocking.queryASblocking_queryFROMpg_stat_activity blockedJOINpg_stat_activity blockingONblocking.pidANY(pg_blocking_pids(blocked.pid))WHEREblocked.queryILIKE%VACUUM FULL%;3. 处理步骤与业务方确认长事务是否可以结束若为测试事务直接终止SELECTpg_terminate_backend(28102);确认锁已释放后错峰重新执行 VACUUM FULLVACUUMFULLorders;重建膨胀索引并验证REINDEXTABLEorders;SELECTpg_size_pretty(pg_total_relation_size(orders));4. 最终效果指标故障前处理后表大小860GB610GBDead Tuple1.2亿430万平均查询耗时420ms170ms锁等待超时无阻塞5. 排查思路总结先定位阻塞源通过 pg_stat_activity 和 pg_blocking_pids 找到持有锁的会话判断是长事务还是高并发读写。区分处理策略测试事务可直接终止生产长事务需与业务方确认避免误杀关键任务。错峰执行VACUUM FULL 和 REINDEX 尽量安排在业务低峰降低锁冲突概率。事后复盘记录锁等待时长、阻塞来源和处理耗时更新检查清单必要时调优 Autovacuum 参数减少膨胀累积。本文结合部署流程、故障注入、诊断SQL、治理前后对比以及RTO/RPO验证总结了高更新表空间治理的实践方法。转载自https://blog.csdn.net/u014727709/article/details/164125404欢迎 点赞✍评论⭐收藏欢迎指正