DBAtlas · Atlas d'exploitation des bases de données

Mesurer le bloat des tables et index avec pgstattuple

PostgreSQLMaintenance

Commande

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;

Ce que fait la commande

Affiche les pourcentages de tuples vivants/morts et d'espace libre d'une table (estimation rapide et valeur exacte) et l'espace récupérable ; pour les index B-tree, la densité des feuilles et la fragmentation.

Quand l'utiliser

Lorsque l'estimation n_dead_tup de pg_stat_user_tables ne suffit pas ; pour étayer par des données une décision de pg_repack, VACUUM FULL ou REINDEX.

Attention

pgstattuple et pgstatindex lisent toute la relation et génèrent beaucoup d'E/S sur les grandes tables (ils ne prennent qu'un AccessShareLock). Utilisez d'abord pgstattuple_approx. Par défaut, un superutilisateur ou le rôle pg_stat_scan_tables est requis. Un peu d'espace libre est normal (fillfactor, place pour les HOT updates) ; VACUUM le rend réutilisable mais ne réduit pas le fichier ; le récupérer exige pg_repack ou VACUUM FULL (verrou ACCESS EXCLUSIVE !). Reconstruisez un index à faible avg_leaf_density avec REINDEX CONCURRENTLY.

Ouvrir dans DBAtlas →

Voir aussi