Autovacuum, Bloat, and PostgreSQL Table Care

PostgreSQL never updates a row in place. It writes a new tuple and leaves the old one until VACUUM marks the space reusable. Autovacuum is the worker that does that in the background. When it falls behind, tables bloat, indexes bloat, and seq scans read dead air. The cluster still returns correct answers. It just does it with twice the heap. This post is how to see dead tuples, how to make autovacuum more aggressive on a hot table, and when to run a manual VACUUM on a Lucknow DC01 VPS without taking the app down.

You do not need to vacuum the whole cluster on a schedule if autovacuum is keeping up. You need to measure.

See dead tuples before you feel them

pg_stat_user_tables is the dashboard. n_live_tup versus n_dead_tup, last_autovacuum, last_autoanalyze. A table with 2 million live rows and 800 thousand dead rows is behind. A table with last_autovacuum of yesterday and n_dead_tup near zero is fine.

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / GREATEST(n_live_tup + n_dead_tup, 1), 1) AS dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'app'
ORDER BY n_dead_tup DESC;

dead_pct above 20 on a write-heavy table is a smell. Also watch wraparound. PostgreSQL will eventually refuse writes if transaction IDs are not frozen. age(relfrozenxid) climbing past a billion on a large table means autovacuum is losing that race. That is an emergency of a different kind: VACUUM freeze, not a bloat chat.

SELECT c.relname, age(c.relfrozenxid) AS xid_age, n.nspname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'app' AND c.relkind = 'r'
ORDER BY age(c.relfrozenxid) DESC;

Tune autovacuum on the table that needs it

Cluster-wide autovacuum_vacuum_scale_factor of 0.2 means a table is vacuumed after 20 percent dead tuples. On a 50 million row table that is 10 million dead rows. Too late. Lower the scale factor on that table, raise workers if the VPS has cores to spare, and keep autovacuum on.

ALTER TABLE app.orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 5000,
  autovacuum_analyze_scale_factor = 0.05,
  autovacuum_vacuum_cost_delay = 2
);

# /etc/postgresql/16/main/conf.d/autovacuum.conf
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 10s
maintenance_work_mem = 256MB
log_autovacuum_min_duration = 1s

log_autovacuum_min_duration of 1s writes a log line for vacuums that take more than a second. Read it. If workers are always running and n_dead_tup still climbs, you need more maintenance_work_mem, a lower cost_delay on that table, or fewer concurrent writes per second than the heap can recycle.

VACUUM (VERBOSE, ANALYZE) app.orders; is the manual catch-up. It does not lock out reads and ordinary writes. VACUUM FULL does rewrite the table and takes an access exclusive lock. Skip VACUUM FULL unless you are ready to put the table offline and you have the extra disk for a second copy. On High-Speed SSD the rewrite is fast relative to spinning rust; the lock is still the problem.

pg_repack is the usual online rewrite if you install it later. This post stays with stock PostgreSQL 16: autovacuum, targeted VACUUM, and only then a planned FULL during a window.

Indexes bloat too

Dead heap tuples leave dead index entries until the index is vacuumed. A btree can be more than twice the heap. pg_stat_user_indexes plus a bloat estimate tells you whether REINDEX CONCURRENTLY is worth the write cost.

VACUUM (VERBOSE, ANALYZE) app.orders;
REINDEX INDEX CONCURRENTLY app.orders_customer_created_idx;
ANALYZE app.orders;

REINDEX CONCURRENTLY cannot run in a transaction. It needs extra disk for the new index. After it finishes, the old file is dropped. Watch disk on the data volume first. Filling the data disk during a reindex is how a maintenance job becomes an outage.

Fillfactor on a hot table (hot updates that can land on the same page) is a later knob. Default 100 on tables is fine until HOT updates fail because pages are packed. Check pg_stat_all_tables n_tup_hot_upd versus n_tup_upd. If hot updates are near zero on a table you expected to update in place, a lower fillfactor on a rewrite may help. Measure first.

Autovacuum is not optional. Turning it off because it "caused IO" just delays the IO and adds wraparound risk. On an Intel Xeon Platinum VPS with High-Speed SSD, three workers and a 2 ms cost delay are cheap compared with a seq scan of a bloated heap.

Takeaway

Measure n_dead_tup, lower autovacuum scale on big tables, VACUUM VERBOSE when behind, and treat VACUUM FULL as a locked rewrite. You can stand up an Ubuntu VPS on Netbay in Lucknow DC01 in under 60 seconds and watch autovacuum logs on High-Speed SSD before production bloat lands — netbayhosts.in.