Measuring and reclaiming Postgres table bloat

What table bloat is

Every UPDATE in Postgres writes a new version of the row and marks the old version as dead. Every DELETE marks a row as dead. VACUUM reclaims the space occupied by dead tuples and makes it available for future INSERTs — but it does not return that space to the operating system. Only VACUUM FULL does that, at the cost of an exclusive lock.

The gap between the table's on-disk size and the space occupied by live tuples is called bloat. Bloat grows when:

• Autovacuum cannot keep up with the write rate (see our autovacuum guide).
• A long-running transaction holds the XID horizon, preventing dead tuple reclamation.
• A bulk DELETE removed a large fraction of the table without a subsequent VACUUM.

Measuring bloat with pg_stat_user_tables

pg_stat_user_tables gives a quick estimate without a full table scan:

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

A dead_pct above 20% on a large table that receives regular writes is worth investigating. Above 50% means VACUUM is either not running or is blocked.

Note: n_dead_tup is itself an estimate maintained by autovacuum. For a precise measurement, use pgstattuple.

Precise measurement with pgstattuple

pgstattuple is a contrib extension available on most managed Postgres services:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
  table_len,
  tuple_count,
  dead_tuple_count,
  dead_tuple_len,
  ROUND(100.0 * dead_tuple_len / NULLIF(table_len, 0), 1) AS dead_pct,
  free_space,
  ROUND(100.0 * free_space / NULLIF(table_len, 0), 1) AS free_pct
FROM pgstattuple('public.orders');

Warning: pgstattuple performs a full sequential scan. On a large table this takes several seconds and increases I/O load. Use it for one-off diagnosis, not as a routine query.

What the columns tell you: - dead_tuple_len — actual bytes consumed by dead tuples. - free_space — pages VACUUM has already cleaned but not yet reused. This is space available for future writes, not waste. - dead_pct — the fraction of the table that is dead tuples. Above 30% is elevated; above 50% needs immediate attention.

VACUUM vs VACUUM FULL

`VACUUM` (non-blocking): Reclaims dead tuple space within the existing table file. The file does not shrink on disk, but freed pages become available for future INSERTs and UPDATEs. Does not acquire an exclusive lock. Safe to run at any time.

VACUUM ANALYZE public.orders;

Use VACUUM for routine cleanup and for tables that need immediate dead tuple reclamation. The ANALYZE part updates planner statistics in the same pass.

`VACUUM FULL` (blocking, returns disk space): Rewrites the entire table into a new file. Acquires ACCESS EXCLUSIVE LOCK — all reads and writes are blocked for the duration. On a 50 GB table this can take 20–60 minutes.

VACUUM FULL public.orders;  -- blocks all access to this table

Use VACUUM FULL only when: 1. The table has severe bloat and you need to reclaim disk space immediately. 2. You have a maintenance window where the table can be offline. 3. Non-blocking VACUUM will not reclaim enough space (e.g., the XID horizon is stuck and dead tuples are not reclaimable).

For most cases, regular VACUUM is the right tool. VACUUM FULL is a scalpel for specific situations, not a maintenance routine.

pg_repack: VACUUM FULL without the exclusive lock

pg_repack rebuilds tables online, similar to VACUUM FULL but without a prolonged exclusive lock:

pg_repack -d postgres://user:pass@host/db -t public.orders

pg_repack works by: 1. Creating a shadow table with the same schema. 2. Copying live rows to the shadow table (no lock on the original). 3. Replicating ongoing writes via a trigger. 4. Swapping the shadow table into place (acquiring a brief exclusive lock only for the swap, typically under 1 second).

pg_repack is available on RDS and Supabase (with some configuration). It is the right choice when you need a VACUUM FULL-equivalent on a table where a multi-minute exclusive lock is unacceptable.

Common bloat scenarios

Tuning autovacuum for high-write tables

The default autovacuum thresholds trigger a vacuum when dead tuples exceed 20% of the table. For a 10-million-row table that means 2 million dead tuples before autovacuum fires. Override per-table:

ALTER TABLE high_traffic_orders
  SET (
    autovacuum_vacuum_scale_factor = 0.01,    -- trigger at 1% dead tuples
    autovacuum_vacuum_cost_delay = 2,          -- reduce throttle (ms)
    autovacuum_analyze_scale_factor = 0.005    -- update stats more frequently
  );

These changes take effect immediately — no restart or reload required.

insightral PG-R03: table bloat detection

insightral's rule PG-R03 fires when a table's dead tuple percentage exceeds 20% (for tables larger than 10 MB). The finding includes the table name, estimated bloat in MB, last autovacuum time, and a recommendation for whether to run VACUUM or VACUUM FULL.

Summary

1. Use the pg_stat_user_tables query to find tables with dead_pct above 20%. Run pgstattuple for a precise measurement on the worst offenders. 2. Run VACUUM ANALYZE table_name immediately — it is non-blocking and reclaims dead tuple space for reuse. 3. Reserve VACUUM FULL (or pg_repack) for situations where you need to return disk space to the OS. 4. Set per-table autovacuum_vacuum_scale_factor = 0.01 on high-write tables so autovacuum triggers before bloat accumulates. 5. After any bulk DELETE, run VACUUM ANALYZE manually — autovacuum may not trigger immediately if the threshold has not been crossed yet.