如何在PostgreSQL中删除所有无效索引

无效索引产生原因 #

这类带ccnew后缀的无效索引通常是PostgreSQL使用CONCURRENTLY参数在线创建索引失败的残留对象：如果索引创建过程中出现任务中断、锁冲突等问题导致创建失败，PostgreSQL不会自动清理这些未标记为有效（indisvalid=false）的索引，就会留下这类冗余对象。

批量删除操作步骤 #

步骤1：确认待删除的无效索引范围 #

先执行查询核对要删除的索引，避免误删正常索引：

SELECT 
  n.nspname AS schema_name,
  c.relname AS index_name
FROM pg_index i
JOIN pg_class c ON i.indexrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE i.indisvalid = false;

返回结果和你之前查询到的无效索引列表一致。

步骤2：自动生成批量删除SQL语句 #

执行以下语句直接生成所有删除命令，无需手动拼接：

SELECT 
  format('DROP INDEX CONCURRENTLY %I.%I;', n.nspname, c.relname) AS drop_command
FROM pg_index i
JOIN pg_class c ON i.indexrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE i.indisvalid = false;

这里使用CONCURRENTLY参数是为了避免删除索引时锁表阻塞正常业务读写，如果是测试环境或者允许短暂停服，也可以去掉该参数加快删除速度。

步骤3：执行清理操作 #

• 先逐行核对步骤2生成的所有DROP INDEX命令，确认待删除的索引无误
• 核对完成后执行这些命令即可完成所有无效索引的清理

注意事项 #

• 清理前建议备份数据库schema结构，避免误删导致业务异常
• 不要在业务高峰时段执行索引删除操作，即使使用CONCURRENTLY参数也会产生一定的资源开销
• 后续使用CONCURRENTLY创建索引后，建议及时检查是否有失败的无效索引残留，避免长期占用存储空间

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