PostgreSQL 的 REINDEX 命令

PostgreSQL 的 REINDEX 命令

REINDEX 是 PostgreSQL 中用于重建索引的重要命令，它可以解决索引损坏、索引膨胀或性能下降等问题。

一 REINDEX 基本语法

-- 重建单个索引
REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA } [ CONCURRENTLY ] name
REINDEX [ ( option [, ...] ) ] { DATABASE | SYSTEM } [ CONCURRENTLY ] [ name ]where option can be one of:CONCURRENTLY [ boolean ]TABLESPACE new_tablespaceVERBOSE [ boolean ]-- 示例
REINDEX INDEX 索引名;
REINDEX TABLE 表名;
REINDEX SCHEMA schema名;
REINDEX DATABASE 数据库名;
REINDEX SYSTEM mydb;  -- 只重建系统目录索引

二 REINDEX 的主要用途

1. 修复损坏的索引：在硬件故障或软件错误后
2. 解决索引膨胀：当索引因大量更新/删除而效率低下时
3. 更改存储参数后：如修改fillfactor后需要重建生效
4. 升级PostgreSQL后：某些版本升级建议重建索引

三 REINDEX 的工作方式

1. 创建索引的新副本
2. 替换旧索引（原子操作）
3. 删除旧索引
4. 与VACUUM FULL不同，REINDEX不会影响表数据

四 REINDEX 与 CONCURRENTLY 选项

PostgreSQL 12+ 支持并发重建索引：

-- 不阻塞读写操作
REINDEX INDEX CONCURRENTLY 索引名;-- 特点：
-- 1. 需要更多时间和资源
-- 2. 可能失败（需手动清理失败的状态）
-- 3. 不会阻止表的DML操作

五 REINDEX 的替代方案

1. DROP + CREATE INDEX：

   -- 传统方法
   DROP INDEX idx_name;
   CREATE INDEX idx_name ON table_name(column);-- 并发版本
   CREATE INDEX CONCURRENTLY idx_new ON table_name(column);
   DROP INDEX idx_old;
   ALTER INDEX idx_new RENAME TO idx_old;
   

2. 使用 pg_repack：

   pg_repack -d dbname --only-indexes -t table_name
   

输出示例：

[pg16@test base]$ pg_repack -d white --only-indexes -t yewu1.t1
INFO: repacking indexes of "yewu1.t1"
INFO: repacking index "yewu1.idx_t1"
INFO: repacking index "yewu1.idx_t1_2"

六 REINDEX 的最佳实践

1. 维护窗口执行：普通REINDEX会锁表
2. 对大表使用CONCURRENTLY：减少业务影响
3. 监控进度：

   SELECT * FROM pg_stat_progress_create_index;
   

4. 重建后分析：

   ANALYZE table_name;
   

七 REINDEX 的注意事项

1. 锁问题：

   • 普通REINDEX获取排他锁
   • CONCURRENTLY模式使用ShareUpdateExclusiveLock

2. 空间需求：

   • 需要额外空间存储新旧两个索引

3. 系统目录索引：

   • 重建系统目录索引需要特殊权限

4. 复制环境：

   • 在主库执行REINDEX会生成大量WAL

更多详细信息请查看官方文档：

https://www.postgresql.org/docs/16/sql-reindex.html

REINDEX 是 PostgreSQL 数据库维护的重要工具，合理使用可以显著提升查询性能并解决索引相关问题。

谨记：心存敬畏，行有所止。