Postgres删除数据后表与索引占用空间未释放问题咨询

PostgreSQL 基于MVCC多版本并发控制机制实现事务隔离，执行DELETE操作时只会将目标行标记为死元组，不会直接把对应磁盘空间返还给操作系统，这是批量删除历史数据后空间占用未下降的核心机制原因。你当前索引占用305GB、业务数据仅占70GB，属于批量删除后引发的严重表与索引膨胀。

先逐一排查以下会导致空间无法回收的异常项，处理完再做空间回收：

• 检查长事务：未提交/未回滚的长事务会持有旧数据快照，导致死元组无法被清理，执行以下SQL排查运行时长超过1分钟的非空闲事务：

SELECT pid, now()-xact_start AS tx_running_time, query 
FROM pg_stat_activity 
WHERE state <> 'idle' AND now()-xact_start > interval '1 minute'
ORDER BY xact_start;

查到异常长事务后，确认业务逻辑可以中断的话，执行SELECT pg_terminate_backend(对应pid);杀掉即可。

• 检查停滞的复制槽：未被消费的复制槽会阻止数据库回收旧版本元组，执行以下SQL排查：

SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal_size
FROM pg_replication_slots;

如果存在active为f、保留WAL体积超过10GB的停滞槽位，确认下游不再使用后直接删除即可。

• 确认实际膨胀分布：执行以下SQL定位具体是哪些表、索引膨胀最严重，避免无意义的全库操作：

-- 查询表膨胀、死元组占比
SELECT 
  schemaname, relname AS table_name, 
  n_live_tup AS live_rows, n_dead_tup AS dead_rows,
  round(n_dead_tup::numeric/(case when n_live_tup =0 then 1 else n_live_tup end)*100,2) AS dead_row_ratio,
  pg_size_pretty(pg_table_size(relid)) AS table_size
FROM pg_stat_user_tables 
ORDER BY pg_table_size(relid) DESC;

-- 查询索引占用、使用频次
SELECT 
  schemaname, relname AS table_name, indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan AS index_used_times
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

对于查询结果中index_used_times长期为0的无用索引，后续可以直接删除，减少空间占用与写入开销。

所有回收操作必须在业务低峰期执行，根据业务可接受的停机时间选对应方案：

• 方案1：在线回收（无锁，不返还空间给OS）
  按膨胀率从高到低逐个对目标表执行VACUUM VERBOSE 表名;，该操作不会阻塞表的正常读写，执行后会将死元组标记为可复用空间，后续新写入数据可以直接使用这部分空间，但不会降低磁盘整体占用。适合暂时没有磁盘空间压力、后续数据会持续写入覆盖复用空间的场景。
• 方案2：全量空间返还（短时间锁表，返还空间给OS）
  对膨胀严重的表执行VACUUM FULL VERBOSE 表名;，该操作会全量重建表和所有关联索引，执行完成后所有死元组占用的空间会全部返还给操作系统，索引膨胀问题会完全解决。注意执行期间会对表加排他锁，阻塞所有读写请求，锁持有时间和表大小正相关，70GB级别的表通常数分钟到十几分钟可以完成，需要提前评估业务影响。
• 方案3：无锁在线重建（无读写阻塞，返还空间给OS）
  如果业务完全不能接受锁表，可以使用pg_repack扩展实现在线重建，效果和VACUUM FULL一致，全程不阻塞正常读写：
  1. 先在目标库创建扩展：CREATE EXTENSION pg_repack;
  2. 在服务器命令行执行重建命令：pg_repack -d 目标库名 -t 要重建的表名 -j 4，其中-j为并行任务数，根据服务器CPU核数调整即可。

所有回收操作完成后，执行ANALYZE 对应表名;更新表统计信息，避免查询优化器因为统计信息过旧生成低效执行计划。

• 确认自动清理进程开启：执行SHOW autovacuum;，返回值必须为on，不要手动关闭自动清理进程。
• 针对存历史数据、频繁做批量删除的大表，调低自动清理触发阈值：

ALTER TABLE 大表名 SET (
  autovacuum_vacuum_scale_factor = 0.05, -- 死元组占比到5%就触发清理，默认值20%对大表来说触发太滞后
  autovacuum_analyze_scale_factor = 0.02
);

• 后续清理历史数据时，不要一次性删除几个月跨度的全量数据，建议按天/周分批删除，每删完一批执行一次轻量VACUUM，避免一次性产生巨量死元组导致严重膨胀。

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