PostgreSQL autovacuum and table bloat in high-write production APIs

A checkout API runs smoothly for weeks. Indexes look healthy in the dashboard. Then p99 latency on orders climbs from 40 ms to 800 ms with no deploy and no traffic spike. EXPLAIN still shows an index scan—but BUFFERS reports thousands of heap pages read per query. The table has grown modestly in row count, yet each indexed lookup touches far more storage than the index alone suggests.

In production engagements on PostgreSQL-backed APIs, this pattern often traces to dead tuple accumulation and autovacuum falling behind on high-churn tables. Vacuum is not housekeeping you schedule during maintenance windows; it is part of the read path. When autovacuum cannot reclaim dead rows fast enough, tables and indexes bloat, cache efficiency drops, and the same logical query plan becomes physically expensive.

This article explains how PostgreSQL's MVCC creates dead tuples, how autovacuum decides when to run, what to monitor before users notice, and how to tune hot tables without destabilizing the cluster.

MVCC: why dead tuples exist

PostgreSQL uses multi-version concurrency control (MVCC). An UPDATE does not overwrite the old row in place; it inserts a new row version and marks the old one dead for transactions that started before the update. A DELETE marks rows dead without removing them immediately.

Dead tuples are invisible to new snapshots but still occupy disk pages until VACUUM reclaims space. Until then:

• Index entries may still point at dead heap tuples; the executor must visit the heap to check visibility (index-only scans require the visibility map to prove all tuples on a page are visible to every transaction).
• Sequential scans and index scans read more pages for the same logical row count.
• Bloat grows: free space inside pages is not returned to the OS until VACUUM FULL or similar rewrite operations (ordinary VACUUM marks space reusable within the table file).

Autovacuum is the background worker that runs VACUUM and ANALYZE without operator intervention. When it lags on a table that receives thousands of updates per minute—session flags, inventory counters, notification state—the database looks "fine" in connection graphs while query latency drifts upward.

What autovacuum actually does

Autovacuum workers:

1. Scan eligible tables based on statistics and configuration.
2. Run VACUUM to reclaim dead tuple space and advance the visibility map (enabling index-only scans and reducing heap fetches).
3. Run ANALYZE when needed so the planner has current row counts and correlation stats.
4. Freeze old row versions so transaction ID (XID) wraparound cannot threaten cluster availability (rare in well-operated systems but catastrophic if ignored).

Vacuum is not fully lock-free. It needs a ShareUpdateExclusive lock on the table—usually brief, but it can block CREATE INDEX CONCURRENTLY and some DDL. Heavy vacuum on enormous tables can consume I/O and CPU; tuning is about frequency and cost per table, not enabling autovacuum once globally.

Default thresholds and why they fail hot tables

For each table, autovacuum compares dead tuples against:

threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples

With cluster defaults (threshold = 50, scale_factor = 0.2), a table with 10 million live rows needs two million dead tuples before a vacuum is triggered. On a table with constant small updates—UPDATE notifications SET read = true, UPDATE seats SET status = 'held'—you can accumulate millions of dead rows while autovacuum sleeps, then spend minutes vacuuming under load.

Per-table settings via ALTER TABLE ... SET (...) are the standard fix for known hot paths.

Table bloat vs index bloat

Heap bloat: pages contain free space or dead tuples; scans read more pages than necessary.

Index bloat: indexes retain entries pointing at dead tuples until vacuum cleans them; index pages grow and splits increase. Reindexing (REINDEX CONCURRENTLY) may be required after prolonged lag, but fixing the vacuum cadence is the durable solution—otherwise bloat returns.

Teams often add indexes when queries slow down. If the root cause is bloat, a new index increases write amplification without fixing heap fetches. Always pair index work with vacuum health on the same table.

Monitoring: catch lag before p99 moves

Useful views and columns:

Signal	Source	What it tells you
n_live_tup, n_dead_tup	pg_stat_user_tables	Dead tuple pressure per table
last_autovacuum, last_autoanalyze	pg_stat_user_tables	Whether autovacuum visited recently
autovacuum_count, autoanalyze_count	pg_stat_user_tables	Historical autovacuum activity
age(relfrozenxid)	pg_class / stats	Freeze progress (wraparound risk)
wait_event = IO / BufferIO during vacuum	pg_stat_activity	Vacuum competing with traffic

A practical alert: for tables above a size threshold, fire when

n_dead_tup > max(10000, 0.05 * n_live_tup)

and last_autovacuum is older than your SLO (for example 15 minutes on a hot table). Adjust constants per product.

Correlate with long-running transactions. A transaction open for 30 minutes holds back the horizon vacuum must preserve; dead tuples created after it started cannot be removed until it ends. ORM debug sessions, stuck batch jobs, and "open transaction while calling Stripe" are common culprits in API codebases.

Also watch replication lag on read replicas: vacuum on the primary reclaims dead tuples; replicas apply WAL and may hold snapshots that delay cleanup on the primary if hot standby feedback is misconfigured—another reason vacuum lag is a fleet-wide concern, not only a primary issue.

Tuning strategy for high-write API tables

1. Lower scale factor on hot tables

For a notifications or inventory table:

ALTER TABLE notifications SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.02
);

This triggers vacuum after roughly 2% dead tuples plus 1,000 rows—much earlier than defaults. Validate on staging: more frequent vacuum increases I/O; tune until dead tuple ratio stays bounded under peak write load.

2. Cap vacuum cost on large tables (optional)

On very large tables, aggressive autovacuum can cause I/O spikes:

ALTER TABLE events SET (
  autovacuum_vacuum_cost_delay = 2,
  autovacuum_vacuum_cost_limit = 1000
);

Higher cost_limit lets vacuum do more work per cycle; cost_delay throttles when the cost budget is exceeded. Balance against interactive latency during vacuum windows.

3. Separate OLTP from batch churn

Reporting jobs that rewrite large partitions or bulk-update millions of rows should use different roles, off-peak schedules, or separate tables/partitions so autovacuum on the interactive path is not fighting a single massive dead tuple wave. When archival boundaries are clear, partition large time-series tables so vacuum and retention can target one partition at a time.

4. Align with connection and transaction discipline

Vacuum cannot reclaim tuples still visible to any open transaction. Enforce:

• Short transactions in HTTP handlers (no external HTTP inside BEGIN).
• idle_in_transaction_session_timeout at the role or database level.
• Statement timeouts on interactive roles (see query timeouts and cancellation).

This is the same class of fix as pool sizing: the database cannot clean up if application code holds snapshots open.

Practical example: health check query and migration snippet

The following SQL is suitable for a metrics exporter or nightly report. It ranks tables by dead tuple pressure and flags stale autovacuum.

SELECT
  schemaname,
  relname AS table_name,
  n_live_tup,
  n_dead_tup,
  round(
    100.0 * n_dead_tup / greatest(n_live_tup + n_dead_tup, 1),
    2
  ) AS dead_pct,
  last_autovacuum,
  last_autoanalyze,
  now() - last_autovacuum AS time_since_autovacuum
FROM pg_stat_user_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n_dead_tup DESC
LIMIT 25;

Pair with a check for transactions blocking vacuum:

SELECT
  pid,
  usename,
  state,
  xact_start,
  now() - xact_start AS xact_age,
  query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
  AND xact_start IS NOT NULL
  AND now() - xact_start > interval '30 seconds'
ORDER BY xact_start;

For a Node.js service that owns schema migrations, apply per-table settings when you identify hot tables in staging load tests:

import type { PoolClient } from "pg";

/** Apply autovacuum tuning for known high-churn tables. */
export async function configureHotTableAutovacuum(client: PoolClient) {
  await client.query(`
    ALTER TABLE notifications SET (
      autovacuum_vacuum_scale_factor = 0.02,
      autovacuum_vacuum_threshold = 1000,
      autovacuum_analyze_scale_factor = 0.02
    )
  `);

  await client.query(`
    ALTER TABLE inventory_seats SET (
      autovacuum_vacuum_scale_factor = 0.05,
      autovacuum_vacuum_threshold = 5000
    )
  `);
}

Run this in a migration, not at request time. Document each override in your schema changelog so operators know why defaults differ.

When dead tuple ratio has been high for days and EXPLAIN (BUFFERS) shows excessive heap reads despite a selective index, plan a low-traffic REINDEX CONCURRENTLY on the affected indexes after vacuum cadence is fixed—not as the first lever.

Common mistakes and pitfalls

1. Raising autovacuum_naptime or disabling autovacuum globally to "reduce load." You trade a small background cost for guaranteed bloat and eventual emergencies.

2. Only monitoring disk size. Table files grow slowly with bloat; latency degrades first. Track n_dead_tup and autovacuum timestamps.

3. Ignoring idle in transaction. One forgotten transaction in a SQL client blocks cleanup for millions of updates.

4. Treating VACUUM FULL as routine. It rewrites the entire table and locks aggressively. Use it for exceptional shrink-after-incident cases, not weekly ops.

5. Adding indexes without vacuum discipline. More indexes mean more entries to vacuum per dead tuple wave.

6. Same autovacuum settings for tiny config tables and multi-million-row event logs. Hot tables need lower scale factors; cold tables should keep defaults to avoid unnecessary work.

7. Bulk loads without COPY + post-load analyze. Mass inserts shift statistics; autovacuum and analyze timing matter for planner quality immediately after load.

Conclusion

Autovacuum is the mechanism that keeps PostgreSQL's MVCC model honest under continuous writes. When it falls behind on API hot paths, symptoms look like "the index stopped working" or "the database needs more CPU"—but the fix is often reclaim dead tuples sooner, shorten transactions, and monitor dead tuple ratio per table.

The payoff is predictable read latency on tables that never stop changing: orders, sessions, notifications, inventory, and audit rows. That is foundational capacity work for scalable, production-ready backends—not a one-time tuning exercise after launch.

For related reading, see connection pooling in Kubernetes, partial and covering indexes, and request deadlines with cancellation. For architecture reviews on PostgreSQL-backed APIs, see about or contact.