Mesurer précisément la fragmentation des tables et des index

PostgreSQL uses MVCC (Multi-Version Concurrency Control). When you UPDATE or DELETE a row, the old version is not erased immediately. It becomes a dead tuple that still occupies space until VACUUM marks it reusable.

• Bloat = space occupied by dead tuples plus unfilled free space that the table or index no longer needs.
• Bloat inflates on-disk size, slows sequential scans, and reduces cache efficiency.
• Indexes bloat too: B-tree pages keep pointers to dead heap tuples until cleaned.

Before choosing a fix (VACUUM, VACUUM FULL, pg_repack, or REINDEX), you must first measure how much bloat actually exists. Guessing leads to unnecessary, disruptive maintenance.

The cheapest first signal comes from the statistics collector. pg_stat_user_tables tracks an estimate of live and dead tuples per table, updated by ANALYZE and autovacuum.

• n_live_tup — estimated live rows.
• n_dead_tup — estimated dead rows awaiting cleanup.
• A high n_dead_tup ratio suggests autovacuum is falling behind.

This is an estimate, not a byte-accurate measure, but it costs nothing and is a great triage filter.

There are two families of bloat measurement, each with trade-offs:

• Estimation queries read only catalog statistics (pg_class, pg_statistic). They are fast and lock-free, but approximate — accuracy depends on fresh ANALYZE and column width assumptions.
• pgstattuple physically scans the relation to count exact live/dead bytes. It is precise but I/O-heavy on large tables.

The practical workflow: use cheap estimation to find candidates, then use pgstattuple to confirm the worst ones before committing to remediation.

pgstattuple is a contrib extension shipped with PostgreSQL. It must be enabled per-database before use.

• Enabling it requires superuser or a role with CREATE on the database.
• Its functions require the pg_stat_scan_tables role (or superuser) to run against arbitrary relations.

Once installed, you get pgstattuple(), pgstatindex(), and the lighter pgstattuple_approx().

pgstattuple('relation') performs a full scan and returns one row of byte-level facts about the heap.

• table_len — total relation size in bytes.
• tuple_count / tuple_len — count and total bytes of live tuples.
• dead_tuple_count / dead_tuple_len — dead tuples and their bytes.
• free_space / free_percent — reusable free space.

The key bloat signal is dead_tuple_percent plus free_percent: together they tell you how much of the file is not holding live data.

pgstattuple() reads every page of the relation. On a 500 GB table that is a lot of I/O and can evict useful data from cache.

• It takes only an ACCESS SHARE lock, so it does not block reads or writes — but the I/O load is real.
• For large tables, prefer pgstattuple_approx(), which uses the visibility map to skip all-visible pages and samples the rest.
• approx returns approx_free_percent and dead_tuple_percent close to the exact values at a fraction of the cost.

Rule of thumb: estimate first, run approx on mid-size tables, reserve exact pgstattuple() for the final confirmation on a specific suspect.

Indexes bloat independently of their table. Use pgstatindex() for B-tree indexes to get structural detail.

• avg_leaf_density — percentage of leaf pages filled with useful data. Healthy indexes sit near 90%; values dropping toward 50% signal heavy bloat.
• leaf_fragmentation — how out-of-order leaf pages are; high fragmentation hurts range scans.
• index_size and internal_pages / leaf_pages describe the tree shape.

A low avg_leaf_density is the clearest argument for a REINDEX (ideally REINDEX ... CONCURRENTLY).

When you cannot afford a scan at all, the community bloat estimation query (from check_postgres / pgsql-bloat-estimation) computes expected size from statistics and compares it to actual size.

Its core idea:

• Take the average row width from pg_statistic (the avg_width per column ANALYZE recorded).
• Add per-tuple header and alignment overhead, then divide table size by the expected tuples-per-page.
• The gap between expected pages and actual pages is the estimated bloat.

It is approximate and sensitive to stale stats, but runs in milliseconds across the whole database.

Estimation accuracy collapses when its inputs are wrong. Watch for these traps:

• Stale statistics: if ANALYZE has not run recently, avg_width and row counts are outdated. Run ANALYZE before trusting estimates.
• Wide variable-length columns: highly variable text/jsonb widths make per-row averages unreliable.
• TOAST: large values stored out-of-line live in a separate TOAST table; heap estimates miss that storage entirely.
• Fillfactor: a table built with fillfactor < 100 intentionally leaves free space — that is not bloat.

Always cross-check a surprising estimate with pgstattuple_approx() before acting.

Large column values are pushed to a hidden TOAST table that bloats on its own. A heap may look clean while its TOAST relation is enormous.

• Find the TOAST relation via pg_class.reltoastrelid.
• Run pgstattuple() directly on the TOAST relation OID to measure its dead space.

Tables with frequently-updated jsonb or bytea columns often hide most of their bloat in TOAST.

Once you have accurate figures, map them to a remediation path:

• dead_tuple_percent high, free_percent high: autovacuum is behind — a plain VACUUM (or tuning autovacuum) usually reclaims reusable space without rewriting the file.
• free_percent very high but table won't shrink: the file has trailing free space that VACUUM can't return to the OS — consider pg_repack (online) or VACUUM FULL (locks the table).
• Index avg_leaf_density low: REINDEX CONCURRENTLY.

Set a threshold (e.g. act only above ~20% bloat and a meaningful absolute size) so you don't run disruptive maintenance for trivial gains.

You suspect a 400 GB table is badly bloated and want an accurate bloat figure with the least I/O impact, given autovacuum keeps the visibility map fairly up to date. Which tool fits best?

You now have a layered method to quantify bloat before acting:

• Triage with pg_stat_user_tables (n_dead_tup ratio) — free and instant.
• Estimate across the whole DB with statistics-based bloat queries — fast, but verify with fresh ANALYZE.
• Confirm precisely with pgstattuple(), or pgstattuple_approx() on large tables, reading dead_tuple_percent and free_percent.
• Indexes: use pgstatindex() and watch avg_leaf_density and leaf_fragmentation.
• Don't forget TOAST, and discount intentional fillfactor free space.

Only after the numbers cross a meaningful threshold do you pick VACUUM, pg_repack, VACUUM FULL, or REINDEX — measurement drives the remediation, never the reverse.