使用 pgstattuple 测量表和索引膨胀

命令

CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- Fast estimate: skips pages marked all-visible in the visibility map
SELECT pg_size_pretty(table_len) AS table_size,
       round(approx_tuple_percent::numeric, 1) AS live_pct,
       round(dead_tuple_percent::numeric, 1) AS dead_pct,
       round(approx_free_percent::numeric, 1) AS free_pct
FROM pgstattuple_approx('orders');

-- Exact measurement: reads the whole table
SELECT pg_size_pretty(table_len) AS table_size,
       round(dead_tuple_percent::numeric, 1) AS dead_pct,
       round(free_percent::numeric, 1) AS free_pct,
       pg_size_pretty(dead_tuple_len + free_space) AS reclaimable
FROM pgstattuple('orders');

-- B-tree indexes: leaf density (~90 when freshly built; below 50 is bloated)
WITH idx AS MATERIALIZED (
  SELECT i.indexrelid
  FROM pg_index i
  JOIN pg_class c ON c.oid = i.indexrelid
  JOIN pg_am am ON am.oid = c.relam
  WHERE am.amname = 'btree' AND i.indrelid = 'orders'::regclass
)
SELECT idx.indexrelid::regclass AS index_name,
       pg_size_pretty(s.index_size) AS index_size,
       s.avg_leaf_density,
       s.leaf_fragmentation
FROM idx
CROSS JOIN LATERAL pgstatindex(idx.indexrelid) s;

作用

显示表的活元组/死元组和空闲空间百分比（快速估算和精确值）以及可回收空间；对于 B-tree 索引，显示叶子密度和碎片。

适用场景

当 pg_stat_user_tables 中的 n_dead_tup 估算值不够用时；用数据支撑 pg_repack、VACUUM FULL 或 REINDEX 的决策。

注意

pgstattuple 和 pgstatindex 会读取整个关系，在大表上产生大量 I/O（只获取 AccessShareLock）。请先使用 pgstattuple_approx。默认需要超级用户或 pg_stat_scan_tables 角色。一定的空闲空间是正常的（fillfactor、为 HOT 更新预留的空间）；VACUUM 使其可重用但不会缩小文件；回收它需要 pg_repack 或 VACUUM FULL（ACCESS EXCLUSIVE 锁！）。对于 avg_leaf_density 较低的索引，请用 REINDEX CONCURRENTLY 重建。