如何阻止PostgreSQL自动创建后缀为_ccnew的重复索引？

问题背景 #

在Debian系统的PostgreSQL 16（容器镜像为groonga/pgroonga:3.2.0-debian-16）中，执行REINDEX TABLE或REINDEX TABLE CONCURRENTLY后，数据库自动生成一批后缀为_ccnew（带数字序号）的重复索引。这类索引占用大量存储空间，后续执行重新索引时偶尔会出现_ccnew索引无法重建或不存在的报错；手动删除后再次执行REINDEX仍会重复生成。

原因分析 #

_ccnew后缀的索引是PostgreSQL执行REINDEX CONCURRENTLY时创建的临时过渡索引。正常流程下，并发重建索引完成后，PostgreSQL会将原索引替换为临时索引，随后自动删除临时的_ccnew索引。如果这类索引残留，通常是因为：

• REINDEX CONCURRENTLY过程被异常中断（如数据库连接断开、锁等待超时、事务失败）
• 使用的pgroonga插件与PostgreSQL 16的REINDEX逻辑存在兼容性问题

解决方案 #

1. 排查REINDEX失败的根本原因 #

首先查看PostgreSQL日志，定位reindex过程中的错误信息：

• 执行SQL获取当前日志文件路径：

  SELECT pg_current_logfile();
  

• 查看日志中包含reindex关键词的条目，重点关注锁超时、插件报错、事务终止等信息，这是解决问题的核心。

2. 针对pgroonga插件的兼容处理 #

由于使用了pgroonga插件，可能存在版本兼容问题：

• 检查pgroonga官方版本更新，确认3.2.0版本是否存在REINDEX相关的已知bug，如有则升级到修复后的版本（如更新容器镜像到最新的groonga/pgroonga版本）。
• 如果业务允许，临时禁用pgroonga插件后执行REINDEX，完成后再重新启用：

  -- 禁用插件
  DROP EXTENSION IF EXISTS pgroonga;
  -- 执行非并发REINDEX
  REINDEX TABLE search.ar;
  -- 重新启用插件
  CREATE EXTENSION pgroonga;
  

3. 清理残留索引并正常完成REINDEX #

步骤1：清理所有残留的_ccnew索引

DROP INDEX IF EXISTS search.ar_product_id_idx_ccnew;
DROP INDEX IF EXISTS search.ar_product_id_idx_ccnew1;
DROP INDEX IF EXISTS search.ar_product_id_idx_ccnew2;
DROP INDEX IF EXISTS search.ar_product_id_idx_ccnew3;
DROP INDEX IF EXISTS search.ar_product_id_locale_key_ccnew;

步骤2：执行非并发REINDEX（推荐）

如果业务允许短时间锁表，使用非并发的REINDEX TABLE，该方式不会生成_ccnew临时索引，直接重建原索引：

REINDEX TABLE search.ar;

步骤3：若必须使用并发REINDEX

确保执行过程中数据库连接稳定，无中断；同时监控执行状态：

-- 查看当前正在执行的REINDEX任务
SELECT pid, query FROM pg_stat_activity WHERE query LIKE '%REINDEX%';

若任务卡住，可通过pg_cancel_backend(pid)终止任务后重新执行。

4. 预防措施 #

• 避免频繁执行REINDEX，仅当索引存在明显碎片（可通过pg_stat_user_indexes查看idx_scan、idx_tup_read与idx_tup_fetch的比值，或pg_indexes_size对比索引初始大小）时才操作。
• 调整PostgreSQL的max_locks_per_transaction参数，避免因锁资源不足导致REINDEX失败（修改后需重启数据库）：

  # postgresql.conf中修改
  max_locks_per_transaction = 64  # 根据实际情况调整，默认是64
  

• 定期检查数据库日志，及时发现REINDEX过程中的异常。

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