PostgreSQL表膨胀与索引膨胀：从MVCC原理到VACUUM实战

1. 先看症状一个磁盘告警引发的膨胀排查前一阵子某电商后台的数据库磁盘水位告警一直响个不停。我登录上去一看数据目录已经用掉了 90% 以上再往上走就要撑爆了。按照正常逻辑业务那边刚做了一轮历史数据清理DELETE 掉了几千万条订单明细理论上磁盘应该释放出来一大截可实际情况是空间不仅没降反而在持续上涨。这就是我入坑表膨胀的第一现场。当时我做的第一件事就是按总大小把库里的表排个序看看是哪几个“巨头”在撑场面SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname || . || tablename)) AS total_size FROM pg_tables WHERE schemaname NOT IN (pg_catalog, information_schema) ORDER BY pg_total_relation_size(schemaname || . || tablename) DESC LIMIT 20;结果非常有意思排在榜首的恰恰是刚被清理过的订单明细表。它的pg_total_relation_size比清理前还大数据行数却少了一大半。如果你第一次遇到这种情况很容易觉得 PostgreSQL 是不是出 bug 了或者 DELETE 根本没生效。但实际上这正是 PostgreSQL 与 MySQL 在空间管理上一个非常核心的差异DELETE 之后磁盘空间不会自动还给操作系统。它只是把那些行标记为“已删除”从查询结果里“消失”了物理空间还被表占着。这里我们要明确一个概念表膨胀指的是表实际占用的物理文件大小远超其中有效数据应该占用的空间。膨胀率越高意味着你在为大量已经不可见的数据付着磁盘租同时也意味着查询时要扫描的数据页更多、缓存命中率更低性能跟着一起滑坡。2. 膨胀的本质MVCC 多版本并发控制机制下的“时间旅行者”要理解膨胀绕不开 PostgreSQL 的 MVCC 实现。官方文档管它叫 Multiversion Concurrency Control翻译过来就是多版本并发控制。这套机制的初衷很朴素让读写互不阻塞读事务看到一个一致的快照写事务在原始数据上叠加新版本。我在排查现场翻开了表的元组信息数据库里每一行数据其实都藏着几个系统字段最核心的是xmin和xmax。xmin插入这行版本的事务 ID。xmax删除或更新这行版本的事务 ID。如果为 0说明这个版本是“活着”的。当你执行一条 UPDATE 时PostgreSQL 并不会原地修改数据而是插入一条新版本的行然后把旧版本行的xmax标记为当前事务 ID。DELETE 也是同理只是它不插入新行只修改xmax。这就带来一个连锁反应只要还有事务看不到“老版本”那些老版本就不能被物理清除。比如事务 A 在 09:00 开启了一个长查询事务 B 在 09:05 更新了大量数据。事务 B 产生的新版本对事务 A 是不可见的事务 A 必须依赖旧版本才能保证自己看到的一致性快照。只有当事务 A 结束之后那些旧版本才真正成为“死元组”才有资格被清理。这就解释了为什么很多朋友遇到膨胀时第一反应是跑 VACUUM却发现没什么效果。因为 VACUUM 想清理死元组可要是库里挂着几个“超长事务”或者“空闲事务”它会直接跳过这些被事务引用的版本等于是有心无力。我见过最夸张的一次一个休眠连接的事务开启时间已经超过 12 个小时导致某张核心业务表膨胀到了原有体积的 5 倍而 autovacuum 愣是拿它没辙。类比一下MVCC 像是一个老式图书馆每一本书都有多个修订版读者可以随意翻阅自己进入图书馆时那个时间点的版本。管理员负责把没人再看的旧版本下架但只要还有一个读者待在馆内他的“入馆时间点”之前的旧版本就必须继续保留。如果这个读者一直不走书库就会堆满废纸新书反而没地方放了。所以排查膨胀光看 VACUUM 有没有跑还不够必须先确认有没有长事务挡道SELECT pid, usename, state, xact_start, now() - xact_start AS duration FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start ASC LIMIT 10;如果看到某些会话的xact_start已经是几个小时甚至几天之前那膨胀大概率就卡在这里。把这些会话处理掉再手动执行一轮 VACUUM死元组才会被真正释放。3. 动手量化膨胀从 pgstattuple 到 pageinspect 的实测记录知道了膨胀的原理下一步就是量化它。不能光凭“感觉”说某张表膨胀了得有数据支撑。PostgreSQL 官方为此提供了两个非常好用的扩展pgstattuple和pageinspect。在目标库上先激活CREATE EXTENSION IF NOT EXISTS pgstattuple; CREATE EXTENSION IF NOT EXISTS pageinspect;然后对疑似膨胀的表跑一下SELECT * FROM pgstattuple(orders_detail);你会得到一堆字段其中几个是判断膨胀的关键tuple_len有效元组占用的字节数。dead_tuple_len死元组占用的字节数。free_space页面中空闲空间的字节数。dead_tuple_percent死元组占比。按我个人的经验dead_tuple_percent超过 30% 就需要认真对待了。如果超过 50%说明这张表已经进入严重膨胀状态查询性能会明显劣化。但pgstattuple有个问题它会扫全表在超大表上执行时本身就有一定负载。生产环境我更推荐先用它的近似版本pgstattuple_approx速度更快、锁需求更小用于日常巡检足够了SELECT * FROM pgstattuple_approx(orders_detail);除了表本身TOAST 表也要留意。PostgreSQL 的大字段比如 text、jsonb会被压缩后转移到附属的 TOAST 表里很多时候主表的膨胀率看着不高TOAST 却已经胀成了气球。所以看体积时我习惯用pg_total_relation_size它会把表、索引、TOAST 都算进去分析膨胀时再用pgstattuple分别看表本身和 TOAST 表。如果想把巡检自动化可以直接写一个遍历库内所有表的查询这里提供一个我一直在用的简化版本SELECT schemaname, relname, n_live_tup, n_dead_tup, CASE WHEN n_live_tup 0 THEN round(n_dead_tup::numeric / n_live_tup::numeric * 100, 2) ELSE 0 END AS dead_percent FROM pg_stat_user_tables WHERE n_dead_tup 1000 ORDER BY n_dead_tup DESC;这个查询基于 PostgreSQL 的统计信息不需要加锁也不会产生额外扫描适合作为每日巡检的基线。需要说明的是这里的n_dead_tup是估算值准确率依赖于最近一次 VACUUM 或 ANALYZE 之后的统计更新但作为膨胀预警已经足够灵敏。4. 对症下药VACUUM FULL 与 pg_repack 的实际利弊量化出膨胀后接下来就是治疗。很多新手的第一反应是执行VACUUM但我上文说过普通 VACUUM 只负责清理死元组让这些空间可以被后续插入复用它不会把文件末尾的空白空间还给操作系统。换句话说VACUUM 治的是“内伤”防止继续恶化但表文件本身还是那么大。要想真正把磁盘空间挤出来传统办法是VACUUM FULL。它会重写整张表只保留有效元组把空间压缩到最小然后返回给操作系统。但代价非常直接VACUUM FULL会对表持有ACCESS EXCLUSIVE锁期间所有读写都会被阻塞。我见过有人凌晨两点在核心交易表上执行VACUUM FULL结果业务直接停摆半小时事后被运维和产品两头夹击。所以我的结论是小表可以随意 VACUUM FULL大表要三思而后行。如果表只有几 GB维护窗口内执行完全没问题一旦表超过几十 GB尤其还是 7x24 小时在线的核心表就得换方案。生产环境救命的方案是pg_repack。它做的事情与VACUUM FULL类似但使用了更巧妙的机制先为表创建一份新的物理文件然后基于原有表的数据生成一份新表期间通过触发器记录增量变更最后切换。整个过程只需要短暂持有一把ACCESS EXCLUSIVE锁通常在毫秒到秒级绝大部分时间业务读写不受影响。用pg_repack时的基本操作流程是这样的安装 pg_repack 工具Linux 下一般通过包管理器安装。在目标库创建扩展CREATE EXTENSION IF NOT EXISTS pg_repack;执行重排pg_repack -h 127.0.0.1 -U db_user -d db_name -t orders_detail --no-superuser-check--no-superuser-check这个参数很有用因为很多时候我们只有库的 owner 权限而没有超级用户权限加上它才能顺利执行。不过pg_repack也不是灵丹妙药。它需要额外的磁盘空间来存放新旧两份表如果磁盘已经满了根本跑不起来。另外它重放增量数据时会有一定的 I/O 压力我的经验是尽量放在业务低峰期执行同时用-j 4之类的参数限制并发数避免把磁盘带宽打满。这里我一定要强调一点不要在膨胀已经严重到磁盘快满时才来急救那时候什么方案都束手束脚。表膨胀是一个渐进过程应该在它刚露出苗头时就通过 VACUUM 控制住节奏定期再挑几个指标异常的表用 pg_repack 处理。5. 索引膨胀的特殊性B-tree 页分裂与不合理的扫描路径表膨胀聊得比较多但索引膨胀同样不容忽视而且更容易被忽略。因为pg_total_relation_size显示的是“表 索引 TOAST”的总和很多朋友看到总大小没怎么涨就误以为索引没问题。实际上索引膨胀往往是隐藏的磁盘黑洞。索引膨胀的原理和表膨胀同源但在表现上有自己的特殊之处。以最常用的 B-tree 索引为例当你更新了一个索引键时PostgreSQL 并不会原地修改索引条目而是把旧的索引条目标记为死元组再插入一条新条目。这与表数据的更新逻辑如出一辙。区别在于索引页的利用率更容易受到插入顺序的影响。B-tree 索引在数据按顺序插入时每个页都能填得比较满。一旦插入顺序是随机的索引页会被频繁分裂分裂后往往只有一半的利用率。反复更新、删除、再插入索引页的空洞会越来越多最终形成高度碎片化的索引结构。判断索引膨胀的标准大家在网络上也能查到普遍认为索引的叶子页空洞率超过 30% 就需要处理。我实测下来比较常用的查询是SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelname)) AS index_size, leaf_fragmentation FROM pg_stat_user_indexes JOIN pgstatindex(indexrelname) ON 11 ORDER BY leaf_fragmentation DESC;需要注意pgstatindex一次只能扫一个索引所以在写自动化巡检脚本时建议从pg_stat_user_indexes里动态拼接索引名逐个采集。索引膨胀的治疗方案和表类似同样可以通过REINDEX或pg_repack来处理。REINDEX INDEX会重建索引但它也有锁问题。REINDEX TABLE在锁粒度上比VACUUM FULL好些不过还是需要排他锁。生产环境我更加推荐使用pg_repack时顺带一起把索引重建掉因为它支持在重建表的同时重建所有索引。另外要提一个容易优化却被忽略的点HOT 更新Heap-Only Tuple 更新。当 UPDATE 不涉及任何索引列时PostgreSQL 可以通过 HOT 机制在数据页内部完成更新不需要额外产生新的索引条目这样就能大幅减少索引膨胀。但 HOT 有个前提表上要有足够的空闲空间容纳新版本这又回到了填充因子fillfactor的设置。对更新频繁的表我习惯把填充因子从默认的 100 改到 85 或 90ALTER TABLE orders_detail SET (fillfactor 85);然后在维护窗口做一次 VACUUM FULL 或 pg_repack让页面按新的填充因子重新排布。这样后续的 HOT 更新有地方放新版本索引膨胀和表膨胀都会明显缓解。这个调整有点“以空间换时间”的意味会让每个数据页预留更多空闲空间但在高并发 UPDATE 场景下非常值。6. 防患于未然autovacuum 调优与日常巡检清单膨胀的根源在 MVCC诱因却往往出在 autovacuum 的配置上。PostgreSQL 默认的 autovacuum 参数是为中小规模业务准备的一旦表的数据量上去了就很容易跟不上生产节奏。默认情况下autovacuum 触发 VACUUM 的阈值是vacuum_threshold 50 vacuum_scale_factor 0.2也就是说一张一亿行的表至少要积累“50 一亿 * 0.2% 20万”个死元组才会触发 autovacuum。这个门槛对大表来说实在太低了通常等到 20 万个死元组堆积起来时表已经有明显的膨胀迹象了。我的做法是按表维度覆盖参数而不是全局改死。因为每张表的特点不同更新频率也不同一刀切只会在某些表上过度清理在另一些表上清理不足。对核心高频更新表我会执行ALTER TABLE orders_detail SET (autovacuum_vacuum_scale_factor 0.01); ALTER TABLE orders_detail SET (autovacuum_vacuum_threshold 1000); ALTER TABLE orders_detail SET (autovacuum_vacuum_cost_delay 0);autovacuum_vacuum_cost_delay设置为 0意味着不对 autovacuum 做限流让它能一口气用完 I/O 把死元组清干净。这个参数在高峰期可能要小心但对低峰期运行的定时任务很友好。除了参数调优日常巡检清单也是必不可少的。我把这套检查做成了每天早上自动跑的脚本核心就三件事第一检查长事务。昨天已经提过pg_stat_activity这里要再加一条空闲事务同样危险。一个连接即使状态是idle in transaction它持有的快照也会阻止 VACUUM 清理对应版本。所以脚本里不能只看state active只要是xact_start不为空的会话都要纳入监控。第二检查死元组比例趋势。如果连续三天某张表的n_dead_tup居高不下即使比例还没到恐怖的程度也要提前干预。膨胀是累积的越早处理成本越低。第三检查表大小增长速率。用pg_total_relation_size做历史对比如果一张表的体积一周内增长了超过 30%而业务增量没有那么大基本可以判定膨胀正在发生该安排pg_repack了。最后我还想给一个关于监控频率的建议不要每五分钟跑一次pgstattuple这种重型统计。生产环境更合理的方式是高频用系统视图pg_stat_user_tables做轻量监控发现指标异常后再针对性执行pgstattuple_approx和pgstatindex做二次确认。这样既不会影响业务又能把膨胀问题控制在早期。写在最后的运维心得想了想在靠 PostgreSQL 吃饭的这些年里表膨胀和索引膨胀绝对是我排查过最多的问题之一。它不像崩溃那样剧烈却像慢性病一样持续拖累系统性能和磁盘成本。每次处理完一轮膨胀问题后我都会回头复盘当时如果有更及时的监控、更合理的 autovacuum 参数、更果断的长事务清理事情本可以简单得多。后来我把这些经验沉淀成了几条硬规矩第一核心业务表必须单独设置 autovacuum 参数绝不依赖全局默认值第二长事务监控必须做成长效机制而不是出了问题才去看第三除非表非常小否则不允许在维护窗口之外直接执行 VACUUM FULL一律走 pg_repack。这几条规矩看似简单却在之后帮我规避了无数次潜在的隐患。如果你正准备在处理膨胀问题的路上摸爬滚打希望这篇分享能给你提供一点参照。就我个人经验来说膨胀并不可怕可怕的是不清楚成因就盲目操作。理解 MVCC量化膨胀对症下药再守住巡检防线这套组合拳打下来你的 PostgreSQL 数据库长期都会处于一个比较健康的状态。