Dead tuples are invisible—until they aren’t. On write-heavy workloads—order and status tables, event streams, telemetry from Houston’s energy corridor—every UPDATE and DELETE leaves an old row version behind. PostgreSQL’s MVCC design is why readers never block writers, but the tradeoff is that stale row versions accumulate until autovacuum sweeps them away. When autovacuum falls behind, tables and indexes bloat, shared_buffers fills with rows no query will ever return, and index scans that used to touch a fraction of a table start dragging megabytes of dead tuples through the I/O path.

The failure mode is familiar: pg_stat_statements looks clean for weeks, then an UPDATE-heavy table doubles in size and a query that used to return in 20 milliseconds now takes 2 seconds. The fix is usually not a cleverer query or a new index. It’s understanding what autovacuum is (and isn’t) doing, and tuning it to your actual write pattern.

What Bloat Actually Costs You

Every updated row creates a dead tuple that remains visible to older transactions until it is removed. Until vacuum runs, that dead tuple still occupies space in the table and in every index that references it. The practical effects:

• Table scans get slower. Sequential scans read pages of dead rows before reaching live data.
• Index lookups get slower. Index bloat means more pages per index scan, and heap fetches may land on pages full of dead tuples that must be skipped.
• Cache efficiency drops. shared_buffers and the OS page cache hold pages that are mostly garbage.
• Vacuum itself gets slower. A bloated table takes longer to vacuum, so the backlog compounds.

First, Find the Bloat

Start with the cumulative stats, which cost nothing to query. Look for tables with a large pile of dead tuples and a last_autovacuum timestamp that is days old:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

A high n_dead_tup with a recent last_autovacuum means vacuum is running but can’t keep up. To measure the physical damage, use pgstattuple (read-only, safe to run on a replica if you have one):

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT dead_tuple_percent, free_percent
FROM pgstattuple('orders');

If dead_tuple_percent or free_percent is in the double digits, that table is carrying real bloat.

Why Autovacuum Falls Behind on Big Tables

Autovacuum triggers when dead tuples exceed autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * reltuples). The problem is the defaults were tuned for the databases of a decade ago:

autovacuum_vacuum_scale_factor = 0.2   -- wait for 20% of the table to be dead
autovacuum_vacuum_threshold     = 50    -- plus 50 rows

On a 100 million-row table, that means autovacuum waits until roughly 20 million dead tuples accumulate before it wakes up—and then it runs under a cost limit designed to avoid disturbing the database, so a multi-gigabyte sweep can stretch for hours while writes keep piling on. See the autovacuum documentation for the full parameter list.

Right-Size Autovacuum for Write-Heavy Workloads

For most OLTP systems, the better starting point is a much smaller scale factor with a higher fixed threshold, so small tables aren’t vacuumed constantly and large tables are vacuumed before bloat builds:

# postgresql.conf
autovacuum_vacuum_scale_factor = 0.05   -- wake up at ~5% dead instead of 20%
autovacuum_vacuum_threshold     = 1000  -- don't nag tiny tables
autovacuum_vacuum_cost_limit    = 2000  -- let each worker work harder

Watch the effect with the pg_stat_progress_vacuum view while a cycle runs. The goal is frequent, short vacuum cycles, not occasional marathon ones.

Hot Tables Deserve Per-Table Overrides

A shipping-status table in logistics, a well-telemetry stream in energy, a claims-status table in healthcare—every Houston shop has a few tables that absorb most of the writes. Global settings are a compromise; these tables need their own rules via ALTER TABLE storage parameters:

ALTER TABLE shipment_status SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold     = 500,
  autovacuum_vacuum_cost_limit    = 2000
);

That keeps the hottest table nearly bloat-free without forcing aggressive vacuuming on the whole cluster.

Don’t Forget Index Bloat

Heavy UPDATE traffic bloats indexes too—especially non-HOT updates that touch indexed columns. An index can be 80% dead entries while the table itself looks fine. Rebuild the worst offenders, or use REINDEX INDEX CONCURRENTLY in PostgreSQL 12+ to avoid taking a lock that blocks writes:

REINDEX INDEX CONCURRENTLY shipment_status_created_at_idx;

When Manual VACUUM Still Makes Sense

Autovacuum should handle steady-state churn, but manual vacuum still has a place: after a bulk load or a mass delete (for example, purging years of expired event data), run VACUUM (VERBOSE) on the affected table and watch it complete. Skip VACUUM FULL in production—it takes an exclusive lock and rewrites the table; pg_repack is the online alternative if you truly need to reclaim space to the OS.

The general guidance on routine vacuuming from the PostgreSQL documentation is worth re-reading whenever a “mystery” slowdown appears: check vacuum first, tune the write-heavy tables, and let autovacuum do the steady work. Nine times out of ten, the index was fine all along—the table just needed a cleanup.