一、REINDEX CONCURRENTLY：不停机重建膨胀索引
随着业务 DML 语句持续增长,索引会像表一样发生膨胀。膨胀的索引不仅浪费磁盘空间,还会显著降低查询性能——新构建的索引往往比反复更新的旧索引提供更好的访问效率。
为什么不能直接用 REINDEX？
普通 REINDEX 命令需要 ACCESS EXCLUSIVE 锁,这是最高级别的锁,会阻塞一切业务语句,生产环境中几乎不可接受。
解决方案是使用 REINDEX ... CONCURRENTLY,其锁级别降为 SHARE UPDATE EXCLUSIVE,不阻塞 DML 操作,实现业务无感知的索引重建。
REINDEX CONCURRENTLY 的六个执行阶段
| 阶段 | 操作内容 | 关键说明 |
|---|---|---|
| ① 创建新索引 | 生成临时索引,命名以 idx_ccnew 开头 | 此时新索引为空,尚未包含数据 |
| ② 构建新索引 | 全表扫描,填充新索引内容 | 期间新写入数据同步进入新索引 |
| ③ 验证新索引 | 将构建阶段新插入的数据补充进索引 | 类似 CREATE INDEX CONCURRENTLY 的逻辑 |
| ④ 交换索引 | 新旧索引互换,旧索引标记为 invalid | 业务流量切换至新索引 |
| ⑤ 标记旧索引为 dead | indislive、indisready、indisvalid 均置为 false | 旧索引彻底退出服务 |
| ⑥ 删除旧索引 | 物理清除旧索引占用的磁盘空间 | 完成整个重建流程 |
识别异常残留的无效索引
如果 REINDEX CONCURRENTLY 中途失败,可能留下名称含 _ccnew 的无效索引,需及时清理：
SELECT c.relname AS index_name,
sys_size_pretty(sys_relation_size(c.oid))
FROM sys_index i
JOIN sys_class c ON i.indexrelid = c.oid
WHERE c.relname LIKE '%_ccnew' -- 重建过程产生的临时索引
AND NOT indisvalid -- 处于 INVALID 状态
LIMIT 10;
二、垃圾回收原理：为什么表会膨胀？
KES 基于 MVCC（多版本并发控制）机制,更新和删除操作不会立即物理删除旧版本数据,而是保留为"死亡元组"（dead tuple）。这些死亡元组需要由 VACUUM 机制定期回收,否则就会造成表膨胀。
导致膨胀的八大根因
① 未开启 autovacuum 最直接的原因。没有自动回收机制,死亡元组只会越堆越多。
② autovacuum 触发阈值过高 默认触发条件为：
默认 scale_factor = 0.2,意味着死亡元组达到表总行数的 20% 才触发回收,膨胀已成事实。
③ autovacuum worker 进程不足
默认 autovacuum_max_workers = 3,当需要清理的表超过 3 张时,其余表只能排队等待。
④ 长 SQL 或长事务持有 xmin 这是生产环境最隐蔽、危害最大的原因,详见下一节。
⑤ 开启了 autovacuum_vacuum_cost_delay 基于成本的限速机制会显著拖慢垃圾回收速度,IO 正常的系统不建议开启。
⑥ autovacuum_naptime 设置过长 launcher 进程唤醒间隔过长,垃圾堆积无人处理。
⑦ 大批量删除或更新 单事务删除 / 更新大量数据,事务提交前这些垃圾版本完全无法回收。
⑧ 大量非 HOT 更新导致索引膨胀 B-Tree 索引整页无引用才能被回收,非 HOT 更新会快速撑大索引体积。
并发批量更新的膨胀实测
以下测试将 100 万行数据分 10 个进程持续并发更新,观察膨胀过程：
-- 初始状态
表大小：73 MB 索引大小：21 MB
-- 10 进程并发持续更新后
表大小：335 MB 索引大小：48 MB
autovacuum 日志中可以观察到大量不可回收的死亡元组：
tuples: 0 removed, 2049809 remain, 999991 are dead but not yet removable
tuples: 501373 removed, 2176172 remain, 999991 are dead but not yet removable
根本原因：autovacuum worker 是表级粒度,同一张表同一时间只有一个 worker 在工作。并发更新事务持有的排他锁会阻断回收过程,产生 "not yet removable" 的死亡元组,最终迫使数据库扩展新数据块。
改进方法：将大批量更新切分为多个小事务,缩短单事务持有时间,减少 not yet removable 的发生概率。
三、长事务：阻止 VACUUM 的"隐形拦路虎"
三类典型的长事务场景
KES 中,以下三种情况都会持续持有 backend_xmin,阻止其后产生的垃圾版本被回收：
场景一：打开游标后不关闭
BEGIN;
DECLARE c1 CURSOR FOR SELECT 1 FROM sys_class;
-- 游标不关闭,backend_xmin 持续存在
-- 此期间产生的所有垃圾版本均无法回收
CLOSE c1; -- 关闭后 xmin 才释放
场景二：长时间运行的查询
BEGIN;
SELECT pg_sleep(1000); -- 执行期间 backend_xmin 持续持有
-- 语句取消或结束后 xmin 才释放
场景三：REPEATABLE READ / SERIALIZABLE 隔离级别事务
BEGIN WORK ISOLATION LEVEL REPEATABLE READ;
SELECT 1;
-- backend_xmin 持续到 COMMIT / ROLLBACK
END;
长事务阻止 VACUUM FREEZE 的实测验证
-- 第一个长事务（xid: 1668525）未结束时
VACUUM (FREEZE, VERBOSE) t2;
-- 结果：0 frozen pages,年龄无法下降
-- "oldest xmin: 1668525"
-- 结束第一个事务后,第二个长事务（xid: 1788896）仍在
VACUUM (FREEZE, VERBOSE) t2;
-- 结果：年龄有所下降,但仍无法归零
-- 两个长事务全部结束后
VACUUM (FREEZE, VERBOSE) t2;
-- 结果：age = 0,完全冻结成功 ✅
关键结论：不仅是目标表的长事务会阻止其 VACUUM FREEZE,其他表的长事务同样会阻止所有表的冻结推进。这是生产环境中表年龄居高不下的最常见原因。
监控长事务的实用 SQL
-- 监控持有 xmin 的活跃会话（超过 30 分钟）
SELECT datname, usename, query, xact_start,
now() - xact_start AS xact_duration,
state
FROM sys_stat_activity
WHERE state <> 'idle'
AND (backend_xid IS NOT NULL OR backend_xmin IS NOT NULL)
AND now() - xact_start > INTERVAL '30 min'
ORDER BY xact_start;
-- 监控两阶段提交中未提交的预备事务
SELECT gid, prepared, owner, database, transaction AS xmin
FROM sys_prepared_xacts
ORDER BY age(transaction) DESC;
-- 监控复制槽是否因备库长事务阻塞 xmin 推进
SELECT * FROM sys_replication_slots
ORDER BY age(xmin) DESC;
四、autovacuum 精细化调优：表级参数设置
全局 autovacuum 参数是"一刀切"的,对于高频更新的核心表,表级参数设置是更精准的解法。
表级参数配置示例
-- 开启表级 autovacuum 并设置精细化阈值
ALTER TABLE t2 SET (autovacuum_enabled = true);
ALTER TABLE t2 SET (autovacuum_vacuum_threshold = 1);
ALTER TABLE t2 SET (autovacuum_vacuum_scale_factor = 0); -- 关键：必须同时设置为 0
-- 同理设置 analyze 相关参数
ALTER TABLE t2 SET (autovacuum_analyze_threshold = 1);
ALTER TABLE t2 SET (autovacuum_analyze_scale_factor = 0);
⚠️ 重要提示：如果只设置
autovacuum_vacuum_threshold = 1而不设置autovacuum_vacuum_scale_factor = 0,则触发条件仍会叠加全局的scale_factor = 0.2,导致阈值远高于预期。两个参数必须配合使用。
