SQL
KES 数据库运维核心：资源回收与膨胀防治
KES 数据库长期运行易出现表与索引膨胀，需通过 REINDEX CONCURRENTLY 不停机重建索引，并理解 MVCC 机制下垃圾回收原理。重点排查未开启 autovacuum、触发阈值过高、长事务持有 xmin 等导致膨胀的根因。通过监控活跃会话、调整表级 autovacuum 参数及切分大批量操作，可有效防治膨胀，保障系统稳定性。
KES 数据库长期运行易出现表与索引膨胀，需通过 REINDEX CONCURRENTLY 不停机重建索引，并理解 MVCC 机制下垃圾回收原理。重点排查未开启 autovacuum、触发阈值过高、长事务持有 xmin 等导致膨胀的根因。通过监控活跃会话、调整表级 autovacuum 参数及切分大批量操作，可有效防治膨胀，保障系统稳定性。
在数据库长期运行过程中，表膨胀与索引膨胀是 KingbaseES（KES）DBA 最常面对的隐形杀手。它们悄无声息地蚕食磁盘空间、拖慢查询性能，严重时甚至威胁系统稳定性。本文从索引重建、垃圾回收原理、长事务阻断、autovacuum 精细化调优四个维度，系统梳理 KES 资源回收的核心机制与实战方法。
随着业务 DML 语句持续增长，索引会像表一样发生膨胀。膨胀的索引不仅浪费磁盘空间，还会显著降低查询性能——新构建的索引往往比反复更新的旧索引提供更好的访问效率。
普通 REINDEX 命令需要 ACCESS EXCLUSIVE 锁，这是最高级别的锁，会阻塞一切业务语句，生产环境中几乎不可接受。
解决方案是使用 REINDEX ... CONCURRENTLY，其锁级别降为 SHARE UPDATE EXCLUSIVE，不阻塞 DML 操作，实现业务无感知的索引重建。
| 阶段 | 操作内容 | 关键说明 |
|---|---|---|
| ① 创建新索引 | 生成临时索引，命名以 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
LIMIT 10;
KES 基于 MVCC（多版本并发控制）机制，更新和删除操作不会立即物理删除旧版本数据，而是保留为死亡元组（dead tuple）。这些死亡元组需要由 VACUUM 机制定期回收，否则就会造成表膨胀。
① 未开启 autovacuum
最直接的原因。没有自动回收机制，死亡元组只会越堆越多。
② autovacuum 触发阈值过高
默认触发条件为：threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples。默认 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 的发生概率。
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;
-- 第一个长事务（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，其他表的长事务同样会阻止所有表的冻结推进。这是生产环境中表年龄居高不下的最常见原因。
-- 监控持有 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 并设置精细化阈值
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，导致阈值远高于预期。两个参数必须配合使用。
| 参数组合 | 触发条件（10 行表） | 实际效果 |
|---|---|---|
| 仅设置 threshold=1（全局 scale=0.2） | 1+0.2×10=3 条死亡元组 | 更新 3 条才触发 |
| threshold=1 + scale_factor=0 | 1+0×10=1 条死亡元组 | 更新 2 条即触发 |
| threshold=大数 + scale_factor=1 | 永远不满足 | 禁用 autoanalyze |
针对不同场景，以下是经过实测验证的调优建议：
KES 的资源回收体系是一个多层联动的精密机制，任何一个环节的疏漏都可能引发连锁膨胀。
索引膨胀 → REINDEX CONCURRENTLY → 不停机重建
表膨胀 → autovacuum 精细调优 → 及时回收死亡元组
长事务 → 主动监控 + 及时终止 → 解除 xmin 阻断
年龄积累 → VACUUM FREEZE → 防止事务 ID 回卷
作为 DBA，主动监控优于被动响应。建立长事务告警、定期检查膨胀率、合理配置 autovacuum 参数，是保障 KES 数据库长期健康运行的核心运维实践。在生产环境中，与业务团队充分沟通长事务的潜在风险，从应用设计层面规避问题的根源，才是最根本的解决之道。
微信公众号「极客日志」，在微信中扫描左侧二维码关注。展示文案：极客日志 zeeklog
在线格式化和美化您的 SQL 查询（它支持各种 SQL 方言）。 在线工具，SQL 美化和格式化在线工具，online
解析 INSERT 等受限 SQL，导出为 CSV、JSON、XML、YAML、HTML 表格（见页内语法说明）。 在线工具，SQL转CSV/JSON/XML在线工具，online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转，单页多 Tab。 在线工具，CSV 工具包在线工具，online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具，Base64 字符串编码/解码在线工具，online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具，Base64 文件转换器在线工具，online
将 Markdown（GFM）转为 HTML 片段，浏览器内 marked 解析；与 HTML转Markdown 互为补充。 在线工具，Markdown转HTML在线工具，online