Bloat réel des index B-tree avec pgstatindex
Mesure exacte (pas estimée) de la densité des pages feuilles de chaque index : sous 50 % de densité, l'index a doublé de volume pour rien et mérite un REINDEX CONCURRENTLY.
Cas d'usage
Maintenance trimestrielle : reconstruire uniquement les index réellement fragmentés, pas tout le schéma.
Prérequis
PostgreSQL + extension pgstattuple (index B-tree uniquement)
SQL
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT
i.indexrelid::regclass AS index_name,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS taille,
ROUND(s.avg_leaf_density, 1) AS densite_pct,
ROUND(100 - s.avg_leaf_density, 1) AS bloat_pct,
CASE
WHEN s.avg_leaf_density < 50
THEN 'REINDEX INDEX CONCURRENTLY ' || i.indexrelid::regclass || ';'
ELSE 'OK'
END AS action
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_am am ON am.oid = c.relam AND am.amname = 'btree'
CROSS JOIN LATERAL pgstatindex(i.indexrelid) s
WHERE i.indisvalid
AND pg_relation_size(i.indexrelid) > 64 * 1024 * 1024
ORDER BY bloat_pct DESC
LIMIT 5;Résultat
index_name | taille | densite_pct | bloat_pct | action --------------------------+---------+-------------+-----------+--------------------------------------------------- idx_orders_status | 1840 MB | 31.2 | 68.8 | REINDEX INDEX CONCURRENTLY idx_orders_status; idx_sessions_token | 912 MB | 44.7 | 55.3 | REINDEX INDEX CONCURRENTLY idx_sessions_token; idx_lines_order_product | 2210 MB | 71.8 | 28.2 | OK orders_pkey | 480 MB | 88.4 | 11.6 | OK idx_events_type_date | 3104 MB | 90.1 | 9.9 | OK (5 rows) 2 index sous 50 % de densité : ~1.7 GB récupérables par REINDEX sans verrou
SQLBloatREINDEXpgstattupleMaintenance