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で再構築する。