Measure PostgreSQL table and index bloat responsibly

PostgreSQL 18 scope. The SQL in this article was run on PostgreSQL 18.6 against synthetic fixtures.

A large relation is not automatically bloated. It may contain large live values, indexes required by the workload, TOAST storage, or reusable space that supports an update-heavy table. Conversely, a modest-looking dead-tuple estimate does not prove that the physical layout is efficient.

“Bloat” is most useful as a hypothesis about avoidable storage and work, not as a single universal percentage. Start by separating what occupies the bytes and what operational problem the team is trying to solve: running out of disk, slow scans, excessive cache footprint, or a growth trend that never stabilizes.

Inventory the physical footprint

Use PostgreSQL 18 and read-only inspection access. Replace the sample relation name with the intended application table.

SELECT
  pg_size_pretty(pg_relation_size('public.event_log')) AS main_fork,
  pg_size_pretty(pg_table_size('public.event_log')) AS table_with_aux,
  pg_size_pretty(pg_indexes_size('public.event_log')) AS indexes,
  pg_size_pretty(pg_total_relation_size('public.event_log')) AS total;

SELECT indexrelid::regclass AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_index
WHERE indrelid = 'public.event_log'::regclass
ORDER BY pg_relation_size(indexrelid) DESC;

The size functions cover different components. Do not add totals that already include one another. Large out-of-line values can make TOAST storage significant, while several wide indexes can outweigh the heap. A table rewrite aimed at heap slack may leave the dominant source of storage untouched.

Capture the same measurements over time and alongside row volume. Growth following a legitimate increase in retained history is different from growth while the live dataset stays constant.

Treat catalog estimates as triage

SELECT relid::regclass AS relation,
       n_live_tup, n_dead_tup,
       n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE relid = 'public.event_log'::regclass;

These counters and estimates help identify a write-heavy table and its maintenance pattern. They do not directly measure all free bytes or prove the exact amount a rewrite would recover. A dead tuple still needed by a snapshot is different from free space already available for reuse.

Catalog-based bloat formulas often estimate expected row width, alignment, headers, and page occupancy. They can be useful ranking tools, but compression, null patterns, wide variable-length values, fillfactor, and stale statistics can make the estimate inaccurate. Label the method and its assumptions instead of presenting a calculated percentage as a physical measurement.

Reference evidence ladder. Size inventory and catalog estimates identify candidates; optional physical inspection adds detail before any costly remediation decision.

Use optional physical inspection deliberately

The pgstattuple extension offers tuple-level inspection. Installing it requires the appropriate privileges and an approved extension policy. Do not assume a monitoring account may install extensions or broaden its own grants. Once installed by an authorized administrator, permitted users can inspect a chosen relation:

-- Requires pgstattuple already installed and EXECUTE privilege.
SELECT * FROM pgstattuple('public.event_log'::regclass);

A full inspection scans the relation and has real I/O cost. Schedule it deliberately on large or busy tables. The output distinguishes live tuple bytes, dead tuple bytes, and free space, but concurrent activity means the observation is not a magical immutable snapshot of the entire storage lifecycle.

An approximate variant can reduce scanning under suitable visibility conditions:

SELECT * FROM pgstattuple_approx('public.event_log'::regclass);

Its fields explicitly distinguish approximation from exact components. Keep that distinction in the report. Choosing the cheaper measurement does not justify labeling every returned value exact.

For a B-tree index, pgstatindex provides a different view:

-- Replace this with a verified B-tree index name.
SELECT * FROM pgstatindex('public.event_log_pkey'::regclass);

Index leaf density and fragmentation describe index structure. They are not interchangeable with the heap's dead-tuple percentage. Other index access methods need their own interpretation; do not apply a B-tree diagnostic function or density rule indiscriminately.

Decide whether free space is waste

An update-heavy table benefits from room for new row versions. A lower fillfactor can intentionally reserve page space and improve the chance of HOT updates. Repacking that table to maximum density can temporarily shrink it while making the next update cycle more expensive.

Ask whether the space is being reused. A relation that grows during a batch and then remains stable through repeated batches may be operating normally. A relation that grows after every identical cycle deserves investigation into retained horizons, vacuum capacity, index maintenance, and row-width changes.

Also compare performance, not only bytes. A larger relation that stays hot in cache and supports an important read path may be acceptable. A rarely used large index can impose write costs with little benefit, but removing it requires checking constraints, query history, and infrequent workloads before any deletion is approved.

Choose remediation by objective

Ordinary vacuum usually makes space reusable rather than returning all of it to the operating system. VACUUM FULL rewrites a table and requires strong locking and additional operational planning. Reindexing targets index structure, not heap layout. Concurrent variants can reduce some blocking but have their own constraints, resource costs, and failure states.

A rewrite needs temporary disk headroom, WAL and replication planning, lock analysis, and a recovery procedure. It can be the right choice when reclaiming disk is necessary, but it is not a harmless response to a dashboard percentage. If the underlying workload immediately recreates the same slack, the apparent improvement may be short-lived.

The review deliverable should list measured component sizes, the estimation method, any physical inspection performed, workload context, and the specific benefit expected from remediation. Keep uncertainty visible. Responsible bloat analysis ends with a justified operational decision, which may be to monitor healthy reuse rather than rewrite a working table.

Sources and review scope

• Database size functions
• pgstattuple extension
• Physical storage layout
• VACUUM
• REINDEX