Real B-tree index bloat with pgstatindex
An exact (not estimated) measure of each index's leaf-page density: below 50% density, the index has doubled in size for nothing and deserves a REINDEX CONCURRENTLY.
Prerequisites
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;Result
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