DBAtlas · Atlas für Datenbankbetrieb

Tabellen- und Index-Bloat mit pgstattuple messen

PostgreSQLWartung

Befehl

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;

Was es tut

Zeigt die Anteile lebender/toter Tupel und freien Platzes einer Tabelle (schnelle Schätzung und exakt) sowie den rückgewinnbaren Platz; bei B-Tree-Indizes Leaf-Dichte und Fragmentierung.

Wann einsetzen

Wenn die Schätzung n_dead_tup in pg_stat_user_tables nicht ausreicht; um eine Entscheidung für pg_repack, VACUUM FULL oder REINDEX mit Daten zu untermauern.

Vorsicht

pgstattuple und pgstatindex lesen die gesamte Relation und erzeugen bei großen Tabellen viel I/O (sie nehmen nur AccessShareLock). Nutze zuerst pgstattuple_approx. Standardmäßig ist Superuser oder die Rolle pg_stat_scan_tables nötig. Etwas freier Platz ist normal (fillfactor, Raum für HOT-Updates); VACUUM macht ihn wiederverwendbar, verkleinert aber die Datei nicht; zur Rückgewinnung sind pg_repack oder VACUUM FULL (ACCESS-EXCLUSIVE-Sperre!) nötig. Baue einen Index mit niedriger avg_leaf_density mit REINDEX CONCURRENTLY neu auf.

In DBAtlas öffnen →

Verwandte Einträge