死元组堆积避坑：PG autovacuum 分级调优实录

问题背景：更新很快，表却越来越慢

在订单状态、库存扣减、会话令牌、任务队列这类高频更新业务中，PostgreSQL 的 MVCC 机制不会直接覆盖旧行，而是把旧版本标记为死元组，再由 autovacuum 清理。更新量一旦持续超过清理速度，就会出现典型连锁反应：表和索引膨胀、顺序扫描读取更多垃圾页、缓冲池命中率下降、WAL 与 IO 压力升高，最终业务 SQL 的 P95 延迟被拉高。

很多团队第一反应是全局调大 autovacuum，结果低峰期 IO 被打满；也有人直接关闭 autovacuum，几天后表膨胀到数倍。更稳妥的方法是先确认“谁在堆积、为什么追不上”，再按数据库、热点表、长事务、调度资源四个层级处理。

下面是定位与调优的处理链路：

方案对比：全局调参、表单调优与定时补偿

常见方案有三类。第一类是全局调整 autovacuum_vacuum_scale_factor、autovacuum_vacuum_threshold 和 worker 参数，适合大多数表都更新频繁的实例，但容易让小表也频繁清理。第二类是只对热点表设置存储参数，例如把大表的缩放因子从默认 0.2 降到 0.02 或 0.01，这是收益最高、风险最可控的方式。第三类是业务低峰期使用定时任务做补偿，适合任务队列表或突发更新表，但要配合限流，避免与业务高峰争抢 IO。

实践上建议组合使用：用系统视图持续识别 Top 表，只对热点大表做个性化配置；全局参数保持保守；已经严重膨胀的表，再安排重建索引或 pg_repack 类在线整理。

实操步骤一：用系统视图确认死元组堆积

先创建一张高频更新示例表，并制造更新压力。以下 SQL 可直接在测试库执行：

sql

-- 1. 创建示例表，n_tup_upd 会随更新增长
CREATE TABLE IF NOT EXISTS demo_hot_update (
    id bigint PRIMARY KEY,
    status text NOT NULL,
    updated_at timestamptz NOT NULL DEFAULT now()
);

TRUNCATE TABLE demo_hot_update;

INSERT INTO demo_hot_update(id, status)
SELECT g, 'init'
FROM generate_series(1, 100000) AS g;

-- 2. 模拟高频更新：同一行反复变更，旧版本成为死元组
UPDATE demo_hot_update
SET status = 'status-' || ((random() * 100)::int),
    updated_at = now()
WHERE id IN (
    SELECT (random() * 100000)::int + 1
    FROM generate_series(1, 50000)
);

-- 3. 手动触发统计信息收集，测试环境用于立即观察
ANALYZE demo_hot_update;

随后查询 pg_stat_user_tables，重点不是只看死元组绝对值，而是结合表大小判断比例：

sql

SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    CASE WHEN n_live_tup > 0
         THEN round(n_dead_tup::numeric / n_live_tup, 4)
         ELSE 0
    END AS dead_ratio,
    last_vacuum,
    last_autovacuum,
    vacuum_count,
    autovacuum_count,
    n_tup_ins,
    n_tup_upd,
    n_tup_hot_upd
FROM pg_stat_user_tables
WHERE relname = 'demo_hot_update'
ORDER BY n_dead_tup DESC;

如果 n_dead_tup 持续增长、last_autovacuum 很久不推进，或者 autovacuum 刚结束死元组又快速回升，就说明触发阈值或清理速度不匹配业务写入。

还要检查 autovacuum 是否被长事务拖住。旧快照存在时，死元组不能被安全回收：

sql

SELECT
    pid,
    usename,
    state,
    xact_start,
    now() - xact_start AS xact_age,
    wait_event_type,
    wait_event,
    left(query, 120) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND pid <> pg_backend_pid()
ORDER BY xact_start ASC;

实操步骤二：检查触发阈值与 worker 状态

普通表的默认触发条件约为：

text

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × 表估算行数

例如一张 1000 万行的大表，默认阈值是 50 + 0.2 × 1000 万，也就是约 200 万行变更后才触发。对均匀变化的业务这可以接受，但对少数热点行反复更新的表，可能已经产生大量垃圾页，触发却仍然很慢。

查看当前全局参数：

sql

SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN (
    'autovacuum',
    'autovacuum_max_workers',
    'autovacuum_naptime',
    'autovacuum_vacuum_threshold',
    'autovacuum_vacuum_scale_factor',
    'autovacuum_vacuum_cost_limit',
    'autovacuum_vacuum_cost_delay',
    'maintenance_work_mem'
)
ORDER BY name;

如果正在运行 autovacuum，可通过 pg_stat_progress_vacuum 观察阶段。重点关注 heap_blks_scanned、heap_blks_vacuumed 和 dead_tuples：

sql

SELECT
    p.pid,
    now() - a.xact_start AS duration,
    s.relid::regclass AS table_name,
    p.phase,
    p.heap_blks_total,
    p.heap_blks_scanned,
    p.heap_blks_vacuumed,
    p.dead_tuples,
    left(a.query, 120) AS query
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a ON a.pid = p.pid
JOIN pg_stat_user_tables s ON s.relid = p.relid;

实操步骤三：按热点表分级设置 autovacuum

对于大表或高频更新表，优先使用表级存储参数，而不是全局一刀切：

sql

-- 死元组达到约 1 万块页或 2% 变更时触发，可按实际更新量调整
ALTER TABLE demo_hot_update SET (
    autovacuum_vacuum_threshold = 10000,
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_analyze_threshold = 5000,
    autovacuum_analyze_scale_factor = 0.01
);

-- 查看表级参数
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'demo_hot_update';

如果业务更新极快，可以把缩放因子继续降到 0.005，但不要一开始就全库设置成极小值。阈值越低，清理越及时，也意味着更多扫描、WAL 和 IO。建议先用 n_tup_upd 的增长速度估算：目标是两次 autovacuum 间隔内产生的死元组不超过表体积的 2%～5%。

对于更新非常集中的表，还应关注 HOT 更新。合适的填充因子可以给同页更新留空间，减少索引膨胀：

sql

-- 对频繁更新且索引较多的表，可在维护窗口执行
ALTER TABLE demo_hot_update SET (fillfactor = 85);
VACUUM (FULL, VERBOSE) demo_hot_update;

注意 VACUUM FULL 会锁表并重写整表，生产环境不要在高峰期直接执行。它适合测试或明确维护窗口；在线业务通常应考虑 pg_repack、逻辑迁移或新建表切换。

实操步骤四：全局资源与定时补偿

当多个热点表同时触发，worker 不够用时，队列会排队。可根据 CPU、磁盘和库数量适度调整：

sql

-- postgresql.auto.conf 示例，修改后 reload 或重启取决于具体参数
ALTER SYSTEM SET autovacuum_max_workers = 5;
ALTER SYSTEM SET autovacuum_naptime = '30s';
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 1500;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';
ALTER SYSTEM SET maintenance_work_mem = '256MB';

SELECT pg_reload_conf();

需要强调，maintenance_work_mem 变大可以提升 vacuum 维护死元组指针的效率，但不是越大越好；cost_limit 提高、cost_delay 降低会让 autovacuum 更激进，可能抢占业务 IO。SSD 与低峰期可更积极，机械盘或共享存储应保守。

对突发写入表，可以在低峰执行补偿性清理，并设置限流：

sql

-- 低峰期手动补偿；VERBOSE 可观察实际回收情况
VACUUM (VERBOSE, ANALYZE) demo_hot_update;

-- 仅当前会话调整限流，避免影响全局配置
SET maintenance_work_mem = '128MB';
SET vacuum_cost_delay = '5ms';
SET vacuum_cost_limit = 800;
VACUUM (VERBOSE) demo_hot_update;

避坑总结：至少先检查这 5 件事

只看 n_dead_tup 就判断异常：小表有几万死元组未必严重，大表有几十万死元组也未必能接受。应计算死元组与活元组、表页数的比例，并观察趋势。修正方法是建立定时快照，比较 n_dead_tup、last_autovacuum 和表大小，而不是截取某一时刻。
全局把 scale_factor 调成 0.001：常见现象是所有表都频繁触发，IO util 升高、业务查询延迟抖动。修正方法是全局保持 0.05～0.1 的较积极值或默认值，只对订单、库存、任务队列等热点大表使用 ALTER TABLE ... SET 做表级配置。
忽略长事务和复制槽：现象是 autovacuum 看似运行了，但死元组不下降，表膨胀继续。原因可能是长时间打开的事务、闲置在事务中的连接、逻辑复制槽或备库回放延迟阻止旧 xmin 前进。应查询 pg_stat_activity.xact_start、pg_replication_slots.xmin/catalog_xmin 和复制延迟，先解除快照阻塞。
高峰期直接执行 VACUUM FULL：典型错误是为了快速收缩表，在生产库直接运行，结果业务写入被 ACCESS EXCLUSIVE 锁阻塞。常规 VACUUM 只标记空间可复用，不一定把空间还给操作系统；已经膨胀的表应使用在线重组工具或维护窗口处理，并提前评估锁、触发器、外键和磁盘剩余空间。
只清表不处理索引：大量更新会同时造成索引膨胀，即使表的死元组被清理，索引访问仍可能变慢。修正方法是结合 pg_stat_user_indexes 的使用情况，对无效或膨胀索引执行 REINDEX INDEX CONCURRENTLY，PostgreSQL 版本支持时也可使用 REINDEX TABLE CONCURRENTLY，并在变更后验证查询计划。

最终建议把 autovacuum 当作持续运行的数据生命周期组件：先监控，再分级；先表级，再全局；先解除长事务阻塞，再提高清理并发。这样既能控制死元组堆积，也不会让清理任务反过来压垮业务。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

死元组堆积避坑：PG autovacuum 分级调优实录 ​

问题背景：更新很快，表却越来越慢 ​

方案对比：全局调参、表单调优与定时补偿 ​

实操步骤一：用系统视图确认死元组堆积 ​

实操步骤二：检查触发阈值与 worker 状态 ​

实操步骤三：按热点表分级设置 autovacuum ​

实操步骤四：全局资源与定时补偿 ​

避坑总结：至少先检查这 5 件事 ​