pgstattuple로 테이블 및 인덱스 bloat 측정

명령어

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;

하는 일

테이블의 live/dead tuple 및 여유 공간 비율(빠른 추정 및 정확한 값)과 회수 가능한 공간을 보여 준다. B-tree 인덱스는 leaf 밀도와 단편화를 보여 준다.

사용 시점

pg_stat_user_tables의 n_dead_tup 추정치로 충분하지 않을 때. pg_repack, VACUUM FULL, REINDEX 결정을 데이터로 뒷받침하기 위해.

주의

pgstattuple과 pgstatindex는 relation 전체를 읽으므로 대형 테이블에서 I/O 부하가 크다(AccessShareLock만 잡는다). 먼저 pgstattuple_approx를 사용한다. 기본적으로 superuser 또는 pg_stat_scan_tables 롤이 필요하다. 어느 정도의 여유 공간은 정상이다(fillfactor, HOT update용 공간). VACUUM은 공간을 재사용 가능하게 하지만 파일을 줄이지 않는다. 회수하려면 pg_repack 또는 VACUUM FULL(ACCESS EXCLUSIVE 락!)이 필요하다. avg_leaf_density가 낮은 인덱스는 REINDEX CONCURRENTLY로 재구축한다.