PostgreSQL 高频 UPDATE 表 bloat 到 1.4TB 拖垮 P99 的 5 天复盘:autovacuum 调参 + pg_repack 在线重建实战

2026 年 5 月一个周三下午,我盯着 Grafana 看 user_events 这个核心表的查询 P99 已经连续 6 周缓慢恶化:第 1 周 80ms,第 3 周 220ms,这周 800ms。每次有人提"是不是该优化下",大家看一眼 EXPLAIN 都觉得"还行",慢查询统计也没有亮眼的"罪犯",最后归因为"业务数据涨了,正常老化"。这周 P99 直接破秒,客服群开始抱怨。我决定不再用"业务数据涨"这种话敷衍自己,把 PostgreSQL 翻个底朝天。

5 天后我们定位到的根因和"数据涨了"完全无关——表的"逻辑数据"只增长了 18%,但物理文件大小膨胀了 6.3 倍。每次查询要扫的页数翻了好几倍,任何 query 计划都没法"快"。罪魁是 PostgreSQL 的 autovacuum 在我们的高频更新模式下完全跟不上死元组(dead tuple)的产生速度,bloat 在背后一周一周累积,直到查询性能彻底崩塌。这篇是完整复盘,涵盖 PG MVCC 的 dead tuple 机制、autovacuum 的工作模型与瓶颈、pg_repack 在线重建大表实战、以及落地的《PostgreSQL bloat 治理纪律》。

服务背景:这个看起来"正常"的高频写入

这套 schema 是 2023 年初设计的,一直跑得"基本稳定"——所谓基本稳定是说"没有崩,但有点慢"。我们做过几次"优化",包括:

• 2024 年中加了 NVMe,IOPS 不是瓶颈
• 2024 年底把 shared_buffers 从 16GB 调到 32GB
• 2025 年初把 work_mem 从 4MB 调到 16MB

每次调完都有改善,但总会重新恶化。我们当时把它归因为"数据涨了",这次决心追根到底。

说"基本稳定"还有一层惯性:团队对 user_events 这种"老服务"会自动降低关注度,默认它"跑了 3 年没出大事就应该没问题",把所有精力放在新业务的新表上。这种"老服务税"在很多团队都存在,代价是问题在你看不到的地方慢慢长大,直到某天集中爆发。这次复盘之后,我们规定核心老表也要每月做一次 schema review 和性能体检,不再"放养"。

事故时间线:从习惯性慢到根因落地的 5 天

第一反应:"加索引、调 work_mem"

说实话,看到 P99 高的第一反应,我和团队大多数人一样,先想到的是"查询本身"——是不是 SQL 写得不够好?是不是缺索引?是不是 work_mem 不够导致排序溢出磁盘?我们花了半天对照各种 query,调了几个看起来"可疑"的 SQL,问题依然如故。

转折是当我做了一次 EXPLAIN (ANALYZE, BUFFERS, VERBOSE):

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM user_events
WHERE user_id = 8472193
ORDER BY event_at DESC
LIMIT 50;

                            QUERY PLAN
-------------------------------------------------------------------------
 Limit  (cost=0.57..12.43 rows=50 width=128) (actual time=425.812..428.910 rows=50 loops=1)
   Buffers: shared hit=3210 read=119820 dirtied=42
   ->  Index Scan Backward using user_events_user_id_event_at_idx on user_events
        (cost=0.57..14823.21 rows=62492 width=128) (actual time=425.810..428.890 rows=50 loops=1)
         Index Cond: (user_id = 8472193)
         Heap Fetches: 119745
         Buffers: shared hit=3210 read=119820 dirtied=42
 Planning Time: 0.31 ms
 Execution Time: 428.954 ms

关键数字:

• Index Scan 正确,索引被用上了
• 但 Buffers read 119820:从磁盘读了 11.9 万个 8KB 页,约 950MB
• Heap Fetches 119745:从索引拿到指针后,要去 heap 表里实际取行,取了 11.9 万次
• 最终只返回 50 行

11.9 万次 heap fetch 才返回 50 行,意味着索引指针指向的位置,绝大多数都不是活的行——它们指向的是已经被删除或更新的死元组。PG 的索引保留了这些死元组的指针,直到 VACUUM 清理它们。这就是经典的 "bloat" 症状。

bloat 的因果链:把"P99 缓慢恶化"翻译成 PG 内部机制

调试到这一步,我把 5 天的发现画成一张因果链图。这种"看起来简单的查询慢"背后,其实是 5 个机制串起来导致的:

这条链有意思的地方是:每一步单看都"还行",任意一步如果断了 bloat 都不会爆发。但 5 个 "还行" 串起来就是灾难。这也是为什么过去 6 周我们没意识到——每次单独看监控和 EXPLAIN,都找不到"这就是问题"的瞬间。

真凶 1:MVCC 的 dead tuple 是怎么累积的

要理解 bloat,得先看 PostgreSQL 的 MVCC 怎么处理 UPDATE 和 DELETE。简化模型:

每次 UPDATE 在 PG 里都是一次"逻辑覆盖 + 物理追加":旧行不会被立即删除,只是被标记"死了",直到 autovacuum 来清理。在我们的高频 UPDATE 场景下,每天 800 万次 UPDATE 等于每天产生 800 万个 dead tuple——如果 autovacuum 没跟上,这些 dead tuple 就在表里堆积,索引指针也都指向它们。

更糟糕的是 PostgreSQL 的 HOT (Heap-Only Tuple) 优化:如果 UPDATE 不涉及索引列、且新行能放进同一个 page,PG 不会更新索引,只在 heap 内部链表跳到新行。HOT 优化挺好,但前提是同一 page 有空闲空间——一旦 page 满了,UPDATE 就会"行迁移"到新 page,索引指针保持指向旧位置,旧位置变成死元组。我们的表 fillfactor 是默认 100(意思是 page 一开始就装满),这种情况下 HOT 优化几乎用不上,每次 UPDATE 都产生 dead tuple + 索引更新。

真凶 2:autovacuum 为什么追不上

autovacuum 是 PG 的自动清理工,理论上它会按一定阈值自动触发,清理 dead tuple。我们的 autovacuum 配置(基本是默认值):

对我们的 user_events 表(8.4 亿行),scale_factor=0.2 意味着必须累积 1.68 亿个 dead tuple 才触发 vacuum。按每天 800 万 UPDATE 产生 800 万 dead tuple 算,差不多 21 天才触发一次 vacuum——这中间已经 bloat 严重了。

这只是触发问题。真正致命的是 cost_limit=200 + cost_delay=2ms 这套组合。PG 的 vacuum 工作有 cost 模型:每读一个 page (vacuum_cost_page_hit) cost 1,每标记一个 dead tuple cost 10,每 dirty 一个 page cost 20。cost 累积达到 200 后,worker 主动 sleep 2ms。对一张 1.4TB 的表来说,完整 vacuum 要扫所有 page,总 cost 是数千万级别,按这个 limit 算单次 vacuum 要跑数小时——而我们的写入量在持续制造新的 dead tuple,vacuum 永远追不上。

更糟的是,autovacuum 在跑的时候,如果有其他 DDL 请求 ACCESS EXCLUSIVE 锁(比如某些 ALTER TABLE),它会主动让位,放弃本次 vacuum。我们一周里有几个定时的 schema 检查任务会触发让位,加剧了"vacuum 启动后从未完成"的状况。

真凶 3:fillfactor 默认值不适合高频 UPDATE

这是我们事后才意识到的设计错误。PG 表创建时默认 fillfactor=100,意思是 page 在写入时尽可能填满。对 INSERT-heavy 表这是好事(节省空间),但对 UPDATE-heavy 表是灾难——page 满了就没法 HOT update,每次 UPDATE 都要行迁移,触发索引更新和 dead tuple 产生。

对 UPDATE-heavy 表,fillfactor 应该设到 70 ~ 85,留出 15-30% 的空闲空间给"未来的 HOT update"。这个参数我们一直用默认,白白损失了 HOT 优化的好处。

修法:三步组合

修法 1:用 pg_repack 在线重建表

当前的 1.4TB 表 bloat 63%,VACUUM FULL 能压缩,但要锁全表(写不可用),完全不可能在线跑。pg_repack 扩展是 PG 生态的杀手锏——它通过创建影子表 + 触发器同步 + 切换的方式,在线重建大表,只在最后切换瞬间需要短暂的 ACCESS EXCLUSIVE 锁(几秒)。

# 安装(可能需要 superuser)
CREATE EXTENSION pg_repack;

# 在 shell 跑(后台进程)
pg_repack -d mydb -t user_events \
    --no-superuser-check \
    --no-order \
    --jobs 4

# 实测对我们 1.4TB 表的耗时:6 小时(主要是磁盘写入)
# 期间业务读写正常进行

跑完后:

注意:pg_repack 要求表有主键或非空 UNIQUE 索引,新表初始 fillfactor 默认与原表一致——我们顺便用 ALTER TABLE ... SET (fillfactor=80) 在 repack 前先改了 fillfactor。

修法 2:autovacuum 表级调参

全局调参影响所有表,有副作用。PG 支持表级 autovacuum 参数,我们只针对高频更新的表单独调:

ALTER TABLE user_events SET (
    -- 触发阈值: 5% 的行变 dead 就触发(而不是默认 20%)
    autovacuum_vacuum_scale_factor = 0.05,
    -- 绝对值阈值: 10 万行死了就触发
    autovacuum_vacuum_threshold = 100000,
    -- 提高 cost limit, 让 vacuum 更激进
    autovacuum_vacuum_cost_limit = 2000,
    -- 减少 cost delay
    autovacuum_vacuum_cost_delay = 10,
    -- fillfactor 留 20% 空间给 HOT update
    fillfactor = 80
);

同时全局调整:

# postgresql.conf
autovacuum_max_workers = 6                     # 从 3 提到 6
autovacuum_naptime = 30s                        # 从 1min 缩到 30s
maintenance_work_mem = 4GB                      # 从 256MB 提到 4GB(影响 vacuum 内部用的内存)
autovacuum_work_mem = 2GB                       # 单独给 autovacuum worker 的 mem 上限

这套配置让 user_events 的 autovacuum 跑得更频繁、更激进。代价是磁盘 I/O 上升约 10%(可接受),但 bloat 累积速度大幅放缓。

修法 3:bloat 监控 + 告警

我们写了一个 cron 每天跑 pgstattuple 扩展,统计核心表 bloat,推到 Prometheus:

-- 安装扩展
CREATE EXTENSION pgstattuple;

-- 查 bloat
SELECT
    schemaname, relname,
    n_live_tup, n_dead_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    pg_size_pretty(pg_total_relation_size(format('%I.%I', schemaname, relname)::regclass)) AS total_size,
    last_autovacuum, last_vacuum
FROM pg_stat_user_tables
WHERE n_live_tup > 1000000
ORDER BY n_dead_tup DESC;

Prometheus 告警:

验证:7 天 + 30 天

修复后跑了一周的指标:

I/O 略升是因为 autovacuum 更频繁,但带来的 P99 改善远超 I/O 开销,完全划算。30 天后 dead_pct 稳定在 5-12%,远低于事故前的 50%+。

决策树:bloat 严重时该用哪种修法

事后我们整理出一张决策树,贴在 DBA 工位旁边。下次任何同学发现 bloat,先按这张图判断动作:

这张图最有用的地方是把"调参 vs 重建"的边界画出来了:dead_pct 30% 是分水岭,低于 30% 调参就够,高于 30% 必须重建才能恢复物理大小。重建的工具选择 (VACUUM FULL vs pg_repack) 再看是否允许停业务和表大小。

横向对比:其他数据库怎么处理同类问题

事后我跨数据库横向看了一遍"高频 UPDATE 怎么避免 bloat",列个表给读者做选型参考:

结论:PG 的 MVCC 是少数采用 "原行变 dead" 策略的存储引擎,这是它优秀并发的代价。MySQL/Oracle 把旧版本放 undo log,不会让主表膨胀;LSM 系数据库则有自己的 compaction 周期。PG 用户必须把 autovacuum 当作"数据库正常运行的核心组件",不能默认配置跑。

5 天里被否决的"看似合理"方案

顺手解决的两个长尾问题

调 autovacuum 过程中,我们顺带挖出来两个之前没注意的问题:

(1) toast 表的 bloat:PG 把超过 2KB 的字段(比如 JSON、长文本)存到 toast 表(表名前缀 pg_toast_)。我们的 user_events 表有个 event_payload 字段平均 4KB,toast 表 dead_pct 居然到了 71%。toast 表的 autovacuum 是独立触发的,我们一直没监控它。修法:把 toast 也接入监控,pg_repack 时同时重建 toast。

(2) index bloat:不只是表会 bloat,索引也会。pg_repack 重建表的时候顺便重建了索引,我们的 (user_id, event_at) 索引从 184GB 降到 62GB。事后我们对所有大索引跑 pgstatindex 扩展,发现 30+ 个索引 dead_pct 都超过 25%,排期定期 REINDEX CONCURRENTLY。

事故后这 3 个月的长期收益

磁盘从 78% 降到 34% 还意外解决了我们 2 个月后要扩容磁盘的预算项,直接省下 6 万/月的存储费用。这种"修一个性能问题顺带砍掉一笔预算"的体验,在我十几年职业生涯里也是头一次。

顺手扫到的其他几张表 bloat 情况

事后我们扫了整个数据库,核心表的 bloat 情况:

结论:所有"UPDATE 频繁"的表都不同程度 bloat,INSERT-only 表完全正常。这个规律我们写进了 schema 设计 checklist。

修法 4:HOT 优化能拯救你多少

把 fillfactor 调到 80 之后,HOT (Heap-Only Tuple) 优化重新生效。HOT 的核心机制是:如果 UPDATE 满足"不修改任何索引列"且"新行能放在当前 page 的空闲空间",PG 不会插入新行的索引指针,而是在 heap page 内部维护一条 t_ctid 链,让 page 内"原地更新"。索引不变,dead tuple 在 page 内可被后续 prune 操作回收。

HOT 对我们 user_events 表的实际效果:

HOT 优化生效后,索引几乎不再 bloat,autovacuum 工作量大幅下降。这个改动只需 ALTER TABLE 一行命令,但收益远超我们预期。如果你的表只调一个参数,fillfactor 是性价比最高的。

修法 5:监控 long-running transaction 防止 vacuum 失效

autovacuum 有一个隐藏的"杀手锏失效"场景:如果系统里存在 long-running transaction(包括 idle in transaction 的连接),autovacuum 不能清理在该事务开启之后产生的 dead tuple(因为它们可能对该事务可见)。我们之前有几个 BI 报表脚本会开 30 分钟+ 的事务跑大查询,这直接导致 autovacuum 在这段时间内对所有表都形同虚设。

-- 找 long-running transaction
SELECT pid, age(clock_timestamp(), xact_start) AS xact_age,
       state, query
FROM pg_stat_activity
WHERE state <> 'idle' AND xact_start IS NOT NULL
ORDER BY xact_start ASC LIMIT 10;

-- 找 idle in transaction(最容易遗忘的杀手)
SELECT pid, age(clock_timestamp(), state_change) AS idle_age, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change ASC LIMIT 10;

我们的修法是两条:

• 把 idle_in_transaction_session_timeout 设到 10 分钟,超过自动 kill
• 把 BI 报表脚本改用只读 replica跑,避免在主库开长事务

这两个改动让 autovacuum 在白天高峰也能正常工作,bloat 累积速度再下一个台阶。

立的《PostgreSQL bloat 治理纪律》

• 所有 UPDATE-heavy 表必须显式设置 fillfactor=80(默认 100 不适合 UPDATE 场景)。
• 大表(行数 > 1000 万)必须用表级 autovacuum 参数:scale_factor 调到 0.05,threshold 调到 10 万,cost_limit 提到 2000。
• maintenance_work_mem 设到 4GB 起步(影响 VACUUM 和 CREATE INDEX 的内存上限,小了 vacuum 慢很多)。
• autovacuum_max_workers 至少 6 个(默认 3 不够,大库容易让位)。
• 必须接入 pgstattuple + bloat 监控,dead_pct > 15% 告警,> 30% 立即处置。
• 定期(每季度)对核心表跑 pg_repack,主动维护物理大小。
• 禁止在生产环境用 VACUUM FULL(锁全表),要重建大表用 pg_repack。
• 核心查询的 EXPLAIN ANALYZE 必须看 Buffers,如果 Buffers read 远大于实际返回行数(比例 > 100),怀疑 bloat。
• 新表设计 review:UPDATE 模式必须明确,UPDATE-heavy 表自动套用上述参数。

这次复盘让我们更新的 PG 工程认知

这件事过去 3 个月,团队 DBA 实践有几个明显升级,值得分享:

(1) "默认配置 = 反优化":PG 的默认 autovacuum 参数是为"小型通用数据库"设计的,任何超过 1 亿行的表都不该用默认。我们把 review 数据库 schema 时的检查项之一改成"是不是用了表级 autovacuum 参数",没改的一律打回。这个习惯让后续 2 个新业务上线时 bloat 问题完全没出现。

(2) "EXPLAIN 必须看 Buffers":之前团队跑 EXPLAIN 都是 EXPLAIN ANALYZE,只看时间。这次开始要求一律加 BUFFERS,因为 Buffers 才能反映"实际读了多少数据",而 dead tuple 导致的"扫了大量页只返回少量行"在时间维度上不一定看得出(数据可能在 cache 里)。我们把这条要求写进了《PG 性能排查 SOP》。

(3) "bloat 是慢性病不是急性病":bloat 不会一夜爆发,它会以"P99 缓慢上涨"的形式表现 4-8 周。这种症状很容易被归因为"业务涨了",但实际上业务涨 + bloat 是叠加效应,且 bloat 的影响往往更大。下次再有人说"我们 P99 慢慢变差",我会第一时间问"dead_pct 多少",而不是先看业务流量。

(4) "pg_repack 是大表运维的必备工具":之前我们对 pg_repack 是"听说过,没用过"的状态。这次实战后,DBA 团队把 pg_repack 列为必装扩展,且每季度对核心表(行数 > 1 亿)主动 repack 一次,把它当作"数据库的体检",不等出问题再修。

(5) "数据库 SLI/SLO 要加 bloat 维度":之前我们的数据库 SLI 是查询延迟、连接数、QPS、磁盘使用。这次加了 4 个新指标:每张大表的 dead_pct、autovacuum 完成时间、autovacuum 成功率、表物理大小周增长率。这 4 个指标比延迟更早预警 bloat 问题。

给读者的几条自查清单

1. 跑这条 SQL 看你的核心表 bloat:

SELECT schemaname, relname, n_live_tup, n_dead_tup,
       ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
WHERE n_live_tup > 100000
ORDER BY n_dead_tup DESC LIMIT 20;

2. 任何 dead_pct > 20% 的表都值得排期处理。dead_pct > 50% 的应该立即处理。
3. 对你最慢的查询跑 EXPLAIN (ANALYZE, BUFFERS),看 Buffers read。如果远大于实际返回的行数对应的 page 数(每 page 8KB,通常含几十行),99% 是 bloat 问题。
4. 检查表的 fillfactor:SELECT relname, reloptions FROM pg_class WHERE relname='your_table'; 如果是默认(NULL)且这表常被 UPDATE,改成 80。
5. 检查 autovacuum 日志(log_autovacuum_min_duration 设到 1000),看哪些 vacuum 跑了多久、有没有被 cancel。
6. 如果有大表 bloat 严重,直接用 pg_repack 重建,不要尝试 VACUUM FULL(锁全表 + 写不可用)。
7. 把 pgstattuple、pg_stat_user_tables 接到 Grafana,看 bloat 趋势——这是 PG 运维最该有的看板之一。

这次事故让我对 PostgreSQL 有了新的敬畏:它的 MVCC 设计是个双刃剑——给你了优秀的并发读写能力,代价是必须有人(autovacuum)持续打扫战场。如果 autovacuum 跟不上,任何 PG 数据库都会缓慢恶化,直到查询性能崩塌。这不是"PG 不行",这是 MVCC 范式的必然成本。

同样的事,如果用 MySQL InnoDB 跑不会出现表膨胀这种症状,但你会换成"undo log 暴涨拖慢查询"和"history list length 失控"的不同症状;如果用 MongoDB,你会遇到"WiredTiger snapshot 持有过久导致 cache 撑爆"。每种存储引擎都有自己版本的"清理压力",PG 把这个压力以 bloat 的形式暴露出来,反而是最容易监控和定位的(只要你知道去看)。

很多团队的 PG 数据库跑几年后突然变慢,大概率都是 bloat 在作祟。这种问题不会"突然爆发",它是渐进的,你的 P99 缓慢爬升,你以为是业务涨——直到某天爬到不能忍。所以建议:把 bloat 监控当成数据库的"血压计",每天看一眼,涨势异常马上干预。

另外说一句,这次的根因诊断之所以拖了 5 天才搞定,很大程度上是因为我们一开始的方向就错了——以为是"业务数据涨",以为是"查询不够好"。如果当时第一时间跑 EXPLAIN BUFFERS 看 Heap Fetches 数字,半小时就能锁定 bloat 方向。希望读完这篇,下次你遇到"查询慢但 EXPLAIN 看起来正常",能第一反应去查 bloat。

最后留一句话给所有还在用默认 autovacuum 参数跑 PG 的团队:你不是没有 bloat,你只是还没意识到。今天就跑那条 pg_stat_user_tables 的 SQL,看一眼你最大那几张表的 dead_pct,如果有任何一张超过 20%,请把这篇文章和监控数据一起发给你的 DBA 和后端 lead——这是省下未来某个周五晚上加班的最便宜投资,也是给业务后续 3 年稳定性最实在的保险。

—— 别看了 · 2026