为何PostgreSQL VACUUM FULL ANALYZE能提性能而普通VACUUM ANALYZE无效

安全阿岳

2026-10-3

问题解答

一、统计结果中的IO相关信息

从pg_stat_statements的输出中，可以提取到以下核心IO特征：

缓存命中率极低：shared_blks_hit（缓存命中的共享数据块数）为4456728574，shared_blks_read（需要从磁盘读取的共享数据块数）为4838722113，计算可得缓存命中率仅约48%，远低于正常业务90%+的合理水平，说明近一半的数据访问需要直接读取磁盘，是IO瓶颈的核心表现。
读IO耗时占比高：blk_read_time（磁盘块读取总耗时）达15074237秒，占总查询耗时的14%以上，是拖慢查询的主要IO因素；blk_write_time（磁盘块写入总耗时）仅15691秒，写IO压力相对较低。
无额外IO开销：temp_blks_read、temp_blks_written、local_blks_read等字段均为0，说明查询本身没有触发临时文件排序、临时表操作等额外IO，问题完全来自表数据本身的访问效率。
脏块规模：shared_blks_dirtied（查询修改的共享块数）为879809，shared_blks_written（刷回磁盘的脏块数）为326809，脏块规模不大，不是性能瓶颈来源。

表物理碎片化严重：普通VACUUM仅标记死元组可复用，不会整理物理存储，长时间运行后表中会出现大量不连续的碎片化页面，即使通过索引定位到了目标行的位置，也需要访问更多离散的物理页面才能读取到数据，同时离散页面很难被缓存有效命中，直接导致磁盘读请求飙升。而VACUUM FULL会重写整个表的物理存储，消除碎片化，所以执行后性能会立刻恢复。
表填充因子（fillfactor）配置不合理：如果events表使用默认fillfactor=100，更新时元组无法在原页面存储，会触发元组移动，导致HOT更新失效，短时间内产生大量死元组，进一步加快碎片化的累积速度。
autovacuum配置不匹配大表场景：默认autovacuum触发阈值为autovacuum_vacuum_scale_factor=0.2，对于3000万行的大表来说，需要累积600万死元组才会触发自动清理，清理速度远跟不上死元组产生速度，运行1-2周后碎片化就会累积到不可用的程度。
长事务阻塞VACUUM：如果存在长时间未提交的事务，会阻止VACUUM清理事务产生之后的所有死元组，导致死元组持续累积无法被清理。

确认表碎片化程度：执行以下语句查询表的死元组占比和存储膨胀情况：
```
SELECT n_live_tup, n_dead_tup, 
pg_total_relation_size('events')/1024/1024 AS total_size_mb,
pg_relation_size('events')/1024/1024 AS table_size_mb
FROM pg_stat_user_tables WHERE relname = 'events';
```
计算死元组占比、实际存储大小和数据量对应的合理存储大小的差值，确认碎片化程度。
调整大表的autovacuum配置：单独给events表调低自动清理触发阈值，提高清理频率，避免死元组快速累积：
```
ALTER TABLE events SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_threshold = 10000
);
```
调整表填充因子：针对更新频繁的表，将fillfactor调整到80-90，给HOT更新留足页面空间，减少死元组产生：
```
ALTER TABLE events SET (fillfactor = 85);
```
调整后需要执行一次VACUUM FULL生效，后续死元组产生速度会明显下降。
检查shared buffer配置：128GB内存的服务器建议将shared buffer设置为总内存的25%-40%，也就是32GB-50GB左右，提高缓存命中率，减少磁盘读请求。
排查长事务：定期查询pg_stat_activity查看是否有运行超过1小时的未提交事务，优化长事务逻辑，避免阻塞VACUUM清理。

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