PostgreSQL12重建索引失败生成无效索引致300GB空间占用求修复方案

前置说明 #

所有操作优先在业务低峰期执行，操作前务必做好数据备份，避免误操作导致业务故障。

1. 前置校验与备份 #

• 对目标表执行逻辑备份：

  pg_dump -h [主机地址] -p [端口] -U [用户名] -d [数据库名] -t public.model_recommendation_association -f model_recommendation_association_bak.sql
  

• 检查长事务避免操作阻塞：

  SELECT pid, usename, query, now() - xact_start AS xact_duration 
  FROM pg_stat_activity 
  WHERE state <> 'idle' AND datname = current_database() 
  ORDER BY xact_duration DESC;
  

  存在超过1小时的非必要长事务可先终止，避免后续操作被阻塞。
• 核对无效索引列表，确认和问题描述一致：

  SELECT indexrelname, indisvalid, indexdef 
  FROM pg_index i 
  JOIN pg_class c ON i.indexrelid = c.oid 
  JOIN pg_namespace n ON c.relnamespace = n.oid 
  WHERE n.nspname = 'public' 
    AND (c.relname LIKE 'model_recommendation_association_pkey_new_ccnew%' 
         OR c.relname LIKE 'mod_recomm_ccnew%') 
    AND indisvalid = false;
  

2. 清理所有无效索引 #

无效索引不会被查询优化器调用，仅占用存储空间，可直接删除：

• 批量删除主键类无效索引：

  DROP INDEX IF EXISTS public."model_recommendation_association_pkey_new_ccnew";
  DROP INDEX IF EXISTS public."model_recommendation_association_pkey_new_ccnew1";
  DROP INDEX IF EXISTS public."model_recommendation_association_pkey_new_ccnew2";
  DROP INDEX IF EXISTS public."model_recommendation_association_pkey_new_ccnew3";
  DROP INDEX IF EXISTS public."model_recommendation_association_pkey_new_ccnew4";
  DROP INDEX IF EXISTS public."model_recommendation_association_pkey_new_ccnew5";
  DROP INDEX IF EXISTS public."model_recommendation_association_pkey_new_ccnew6";
  DROP INDEX IF EXISTS public."model_recommendation_association_pkey_new_ccnew7";
  

• 按照上面的命令格式，依次删除所有带INVALID标记的mod_recomm相关无效索引即可。

3. 清理重复有效索引 #

当前存在多个功能完全一致的mod_recomm前缀有效索引（均为btree(recommendation_id)），仅保留最早的mod_recomm即可，其余重复索引全部删除：

注意：删除前可先执行SELECT * FROM pg_stat_user_indexes WHERE relname = 'model_recommendation_association' AND indexrelname LIKE 'mod_recomm_ccnew%'; 确认重复索引近30天无调用记录；如果担心误删影响业务，可先执行ALTER INDEX public."[索引名]" SET (indisvisible = true); 将索引设置为对优化器不可见，观察24小时业务无异常后再删除，出现问题可随时改回indisvisible = false恢复。

• 批量删除重复有效索引：

  DROP INDEX IF EXISTS public."mod_recomm_ccnew";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew1";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew1_ccnew";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew1_ccnew1";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew1_ccnew1_ccnew";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew1_ccnew1_ccnew1";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew1_ccnew2";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew1_ccnew3";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew1_ccnew_ccnew";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew1_ccnew_ccnew1";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew2";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew2_ccnew";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew2_ccnew1";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew2_ccnew1_ccnew";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew2_ccnew1_ccnew1";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew2_ccnew2";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew2_ccnew3";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew2_ccnew_ccnew";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew2_ccnew_ccnew1";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew3";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew3_ccnew";
  DROP INDEX IF EXISTS public."mod_recomm_ccnew3_ccnew1";
  

4. 重建剩余有效索引 #

清理完成后仅剩两个有效索引：主键索引model_recommendation_association_pkey_new、普通索引mod_recomm，使用并发模式重建避免锁表：

-- 重建主键索引
REINDEX INDEX CONCURRENTLY public."model_recommendation_association_pkey_new";
-- 重建普通索引
REINDEX INDEX CONCURRENTLY public."mod_recomm";

注意：并发重建索引不会锁表但执行时间较长，建议在业务低峰期执行；如果重建失败生成带_ccnew后缀的无效索引，直接按步骤2删除即可。

5. 收尾校验与空间回收 #

• 校验索引状态是否正常：

  SELECT indexrelname, indisvalid 
  FROM pg_index i 
  JOIN pg_class c ON i.indexrelid = c.oid 
  JOIN pg_namespace n ON c.relnamespace = n.oid 
  WHERE n.nspname = 'public' 
    AND c.relname IN ('model_recommendation_association_pkey_new', 'mod_recomm');
  

  确认两个索引的indisvalid字段均为t即正常。
• 执行表级空间回收与统计信息更新：

  VACUUM ANALYZE public.model_recommendation_association;
  

• 校验索引总大小确认空间已释放：

  SELECT pg_size_pretty(sum(pg_relation_size(indexrelid))) AS total_index_size 
  FROM pg_index i 
  JOIN pg_class c ON i.indrelid = c.oid 
  JOIN pg_namespace n ON c.relnamespace = n.oid 
  WHERE n.nspname = 'public' AND c.relname = 'model_recommendation_association';
  

6. 后续优化建议 #

• 调整现有清理脚本的reindex逻辑，增加前置校验：如果索引状态为INVALID，先删除无效索引再执行重建，避免反复生成无效索引副本。
• 每周巡检无效、重复索引，及时清理避免空间持续膨胀。
• PostgreSQL 12支持REINDEX TABLE CONCURRENTLY，但执行前必须先清理所有无效索引，否则会直接报错中断。

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