SQL PostgreSQL 的 pgstattuple 的 heap bloat vs index bloat 量化诊断实践

pgstattuple扩展需在每个目标数据库单独启用，调用pgstattuple()前须执行create extension pgstattuple；查表膨胀用pgstattuple(‘table_name’)看dead_tuple_count和free_percent，勿误用仅适用于b-tree索引的pgstatindex()。

pgstattuple 扩展没启用，pgstattuple() 函数直接报错

postgresql 默认不带 pgstattuple，调用 pgstattuple() 或 pgstatindex() 会提示 function does not exist。必须先在目标数据库中启用扩展。

• 连接到对应数据库后执行：CREATE EXTENSION pgstattuple;
• 注意不是在 template1 或全局创建，每个需要诊断的库都得单独运行（pg_stat_database 不跨库）
• 9.6+ 版本支持 pgstattuple_approx()，对大表更快但结果略粗略；真实 bloat 评估建议优先用精确版 pgstattuple()

查 heap bloat 用 pgstattuple()，别误用 pgstatindex()

pgstatindex() 只返回索引结构信息（比如 B-tree 的层级、页数），完全不反映堆表（heap）的膨胀程度。真正看表本体是否 bloated，必须查 pgstattuple('table_name') 返回的 dead_tuple_count 和 free_percent。

• 关键字段：重点关注 dead_tuple_count（已 delete/UPDATE 但未 VACUUM 的行数）、free_percent（页内空闲空间占比）
• 典型 bloat 信号：dead_tuple_count > 0 且 free_percent —— 说明有大量死元组堆积，但页内又没足够空闲空间复用，容易触发页分裂和写放大
• 示例：select * FROM pgstattuple('orders');，别漏掉单引号，表名要加引号（尤其含大小写或特殊字符时）

index bloat 要用 pgstatindex() + 手动算，不能只看 avg_leaf_density

pgstatindex() 返回的 avg_leaf_density 是叶子页平均填充率，但它不体现“逻辑碎片”——比如一个索引页里存了 100 个键值对，但其中 80 个是 dead tuple，实际有效密度可能极低。真正反映索引 bloat 的是 bt_page_stats() 配合统计。

• 更准的做法：SELECT * FROM bt_page_stats('idx_orders_user_id') WHERE type = 'l'; 查叶子页，看 live_items vs items
• 简单估算公式：(items - live_items) / items AS bloat_ratio，> 0.3 就值得重索引
• 注意：pgstatindex() 对非 B-tree 索引（如 GiST、gin）不适用，会报错或返回空；只有 B-tree 支持完整统计

VACUUM 后 pgstattuple() 结果没变？可能是 autovacuum 没扫到或 freeze pending

刚手动跑完 VACUUM orders，再查 pgstattuple('orders') 发现 dead_tuple_count 没降——大概率是事务 ID 冻结（xid wraparound）压力下，autovacuum 被阻塞，或者该表被 long-running transaction 持有 snapshot 锁住，导致死元组无法清理。

• 检查是否有长事务：SELECT pid, now() - backend_start, state, query FROM pg_stat_activity WHERE state = 'active' AND now() - backend_start > interval '5 minutes';
• 确认 vacuum 进度：SELECT * FROM pg_stat_progress_vacuum;（9.6+）
• 如果 free_percent 低但 dead_tuple_count 高，且 vacuum 无进展，优先查 xmin 是否卡在某个老事务上（用 pg_locks 和 pg_transactions 关联）

量化 bloat 不是看单个数字，而是比对 dead_tuple_count、free_percent、live_items 三者的组合关系。最容易忽略的是：heap 和 index 的 bloat 成因不同，修复手段也不同——heap 靠 vacuum，index 靠 reindex，混用只会浪费 I/O。