PostgreSQL调优Autovacuum后死元组下降但磁盘仍周期性增长咨询

数据老陈

2026-8-28

问题现象

即使提升Autovacuum运行性能降低了死元组（dead tuple）数量，磁盘占用仍周期性增长，且每日新增插入数据量小于清理的死元组量。

环境信息

操作系统：CentOS 7
数据库版本：PostgreSQL 10.7
服务器配置：128G内存、600G SSD、16核CPU
业务特征：每日新增插入数据超4000万条；因周期性更新操作，日常约存在1.2亿个死元组；数据保留周期1个月，每周执行一次批量删除；存量有效数据约12亿条。

已执行操作

周期性巡检磁盘占用与死元组状态，查询磁盘占用Top3表的SQL如下：

SELECT nspname || '.' || relname AS "relation",
    pg_total_relation_size(C.oid) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 3;

查询死元组占比的SQL如下：

SELECT relname, n_live_tup, n_dead_tup,
       n_dead_tup / (n_live_tup::float) as ratio
FROM pg_stat_user_tables
WHERE n_live_tup > 0
  AND n_dead_tup > 1000
ORDER BY ratio DESC;

Autovacuum参数优化：默认配置下Autovacuum执行耗时超3天，通过如下参数调整将单表Autovacuum耗时压缩至30分钟内：

ALTER SYSTEM SET maintenance_work_mem ='1GB';
select pg_reload_conf();
alter table pm_reporthour set (autovacuum_vacuum_cost_limit = 1000);
ALTER TABLE PM_REPORTHOUR SET (autovacuum_vacuum_cost_delay =0);

排查维度

按以下优先级逐层排查：

先定位磁盘空间的实际占用对象：现有巡检SQL排除了索引、TOAST表，统计口径不全。首先登录服务器在PG数据目录下执行du -sh * 定位高占用目录：
- 如果是pg_wal目录占用高：检查WAL归档是否失败、是否存在闲置复制槽阻碍WAL清理、wal_keep_segments参数是否设置过大，每周批量删除会产生大量WAL，归档异常时WAL会持续堆积导致磁盘周期性上涨。
- 如果是base目录占用高：再逐层定位到具体文件，关联到对应的表、索引、TOAST对象，不要仅排查普通用户表，高频更新场景下索引、TOAST表的膨胀占比往往超过30%。
- 如果是log目录占用高：检查数据库日志是否开启了过细的日志级别、日志轮转策略是否失效。
- 如果是pgsql_tmp目录占用高：检查批量删除、关联查询时是否产生大量临时文件未释放。
校验Autovacuum的空间回收有效性：普通Autovacuum不会将空闲空间归还操作系统，仅将死元组占用的页标记为可复用，出现可复用空间不足的常见原因包括：
- 仅对pm_reporthour单表配置了激进的Autovacuum参数，其余存在高频更新、每周批量删除的表仍使用默认配置，大表上Autovacuum触发阈值高、运行限速，死元组无法被及时标记为可复用，新数据只能申请新的磁盘页。
- 存在长事务、未提交的两阶段事务、闲置复制槽持有全局最小可见事务ID，导致Autovacuum无法清理对应事务快照之后生成的死元组，pg_stat_user_tables中的n_dead_tup是统计估值，存在延迟，不能完全代表实际可回收的死元组量。可通过查询pg_stat_activity中运行时长超过1小时的非idle会话、pg_prepared_xacts中残留的准备事务、pg_replication_slots中active=false的复制槽确认。
- maintenance_work_mem设置为1GB，约可存储1.8亿条死元组指针，每周批量删除时如果单轮产生的死元组量超过该阈值，Autovacuum会分多轮执行，无法完成全表死元组标记，也会加剧索引膨胀。
检查批量删除操作的副作用：每周批量删除1/4存量数据时，如果表未做时间分区，删除操作会扫描全表，同时产生大量索引碎片；如果删除操作未批量提交，会持有长时间事务锁，进一步阻塞Autovacuum回收空间。另外注意PostgreSQL 10版本中，Autovacuum默认不会在批量删除后主动做末尾空闲块truncate，如果表的高水位线持续抬升，即使有可复用空间，也会因为空闲块不在文件末尾无法被操作系统回收。
校验空间增长的计算口径：不要仅对比每日新增数据量和清理的死元组量，需要将索引增长、TOAST表增长、WAL空间波动、日志空间占用纳入统计，高频更新场景下索引的膨胀率可达100%以上，是容易被遗漏的磁盘占用项。

内容的提问来源于stack exchange，提问作者chai