删除4.9亿行后Postgres 11查询缓慢及Auto Vacuum持续运行求助

核心现状分析 #

你的表public.shares_nexa原数据量约5亿行，清理后剩余约1000万活元组，但n_dead_tup仍高达556993041（约5.57亿）。当前Auto Vacuum持续运行、查询慢的核心原因是：

• 表物理文件未收缩，仍保留原5亿行的存储空间，序列扫描需要遍历大量包含死元组的空页面（执行计划显示读取了322681个磁盘页面，占总扫描页面的99%以上）；
• 索引中堆积大量死条目，导致索引膨胀；
• Postgres 11的Auto Vacuum为单进程，处理超5亿死元组的工作量极大，且会与业务读写争抢IO资源。

问题1：是否应删除并重建索引？此举是否有效？ #

重建索引确实有效，且是当前提升查询性能的关键操作之一，但必须采用在线重建方式避免锁表：

• 大量删除操作后，索引会堆积大量死条目，Auto Vacuum只能标记这些条目为可复用，无法彻底清除索引膨胀。重建索引能直接生成仅包含活元组的新索引，大幅缩小索引体积，减少查询时的索引扫描开销。
• 操作建议：使用Postgres 11支持的REINDEX CONCURRENTLY命令，分批逐个重建索引，避免长时间阻塞业务读写：

REINDEX CONCURRENTLY public.shares_nexa 你的索引名称;

注意：该命令会创建临时索引，完成后替换原索引，期间不会阻塞DML操作，但会占用额外磁盘空间，需确保磁盘有足够余量。

问题2：无法执行Vacuum Full，针对Auto Vacuum持续运行的建议 #

1. 临时调整Auto Vacuum参数，提升清理效率 #

修改postgresql.conf（或用ALTER SYSTEM动态生效，需执行SELECT pg_reload_conf();）：

• 调高maintenance_work_mem：从默认64MB调整到512MB~1GB（根据服务器内存调整，建议不超过总内存的1/4），让Vacuum能一次性处理更多页面，减少IO次数；
• 调高autovacuum_vacuum_cost_limit：从默认200调整到1000~2000，降低Vacuum的IO延迟限制，加快死元组清理速度（需监控服务器IO负载，避免影响业务）；
• 调低autovacuum_vacuum_cost_delay：从默认20ms调整到5ms，减少Vacuum的等待间隔，提升清理效率。

2. 手动分批触发清理，分担Auto Vacuum压力 #

使用带SKIP_LOCKED参数的手动Vacuum，避免锁表，分多次执行逐步清理死元组：

VACUUM (VERBOSE, SKIP_LOCKED) public.shares_nexa;

每次执行后可通过以下SQL监控清理进度：

SELECT 
  relname,
  n_live_tup AS 活元组数量,
  n_dead_tup AS 死元组数量,
  round(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2) AS 死元组占比
FROM pg_stat_user_tables 
WHERE relname = 'shares_nexa';

3. 避免新的死元组堆积 #

确保业务删除操作采用批量方式，避免频繁单条删除；同时检查autovacuum_freeze_max_age参数（默认2亿），防止因事务ID冻结问题触发强制Vacuum，增加额外负载。

额外优化建议：解决序列扫描慢的问题 #

当前简单查询耗时18-19秒，核心是序列扫描需要遍历大量空页面。除了重建索引，还可考虑：

• 等待Auto Vacuum完成所有死元组清理后，Postgres会标记空页面为可复用，后续插入的新数据会逐步填充这些页面，无效扫描的情况会慢慢改善；
• 使用pg_repack工具在线收缩表空间：该工具无需停机锁表，原理是创建新表并导入活数据，交换表名后重建索引，能快速收缩表的物理文件大小，适合当前场景（需提前安装pg_repack扩展）。

关于升级到支持并行Vacuum的新版本 #

你提到的为分区活跃表添加主键、通过AWS DMS升级到Postgres 12+的方案是可行的。Postgres 12及以上版本支持并行Vacuum，可同时处理多个表分区，大幅提升大表的清理效率，从根源上解决Auto Vacuum长时间运行的问题。

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