PostgreSQL Autovacuum Tuning on a Small VPS: Preventing Bloat Without Starving I/O

Autovacuum is the most misunderstood background process on a small PostgreSQL VPS. Left at defaults sized for large dedicated servers, it either runs too rarely — letting dead tuples bloat tables and slow every query — or fires too aggressively, consuming the limited I/O and CPU a cheap plan can spare. This guide explains what autovacuum actually does under memory pressure, then gives parameter values suited to a 2–4 GB instance serving a production web workload.

Storage IOPS is the ceiling that makes autovacuum tuning matter; a plan with slow random writes will always struggle regardless of settings. Verify the storage path behind your plan in our VPS comparison table first.

Why Defaults Fail on Small Instances

Default autovacuum triggers when a table accumulates roughly 20% dead tuples plus a fixed threshold of 50 rows. On a large table that is fine; on a small, heavily updated table the trigger may never fire until the table is already badly bloated. Meanwhile, the default autovacuum_max_workers of 3 and generous cost_limit settings let vacuum workers compete with your application for the tiny I/O budget of a budget VPS. The result is a database that is simultaneously bloated and I/O-starved.

Measuring Bloat Before You Tune

Never tune blindly. Two queries tell you whether you have a bloat problem and which tables cause it:

-- Dead tuples and last vacuum per table
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;

-- Estimated bloat via the pgstattuple extension
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('your_table');

If n_dead_tup climbs between vacuums and last_autovacuum is stale, autovacuum is not keeping up. That is your signal to lower the scale factor and raise worker throughput, not to run a manual VACUUM FULL that locks the table and rewrites it.

Per-Table Thresholds Beat Global Settings

Global changes affect every table, including ones that never churn. On a small server it is more precise to set aggressive thresholds on the specific high-write tables and leave the rest alone:

ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_threshold = 50,
  autovacuum_analyze_scale_factor = 0.02
);

A 5% scale factor means vacuum runs four times more often than the 20% default on that table, keeping dead tuples low and index scans fast. Reserve this treatment for tables with frequent UPDATE or DELETE activity — session stores, job queues, and log-style tables are typical candidates.

Cost-Based Throttling for a Small I/O Budget

Autovacuum’s cost parameters decide how hard it is allowed to work. On a VPS with limited IOPS, the goal is steady low-intensity work rather than bursts that starve the application. Lower the cost limit and raise the delay so vacuum spreads its work out:

ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 200;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '10ms';
ALTER SYSTEM SET autovacuum_max_workers = 2;
SELECT pg_reload_conf();

Two workers instead of three reduces concurrent I/O contention; the lower cost limit caps how much each worker can spend per cycle; the delay gives the application breathing room between cycles. On NVMe-backed storage you can raise the cost limit back up, since the device handles concurrency better.

Monitoring Whether It Is Working

After a week, re-run the dead-tuple query. You want to see n_dead_tup staying low relative to n_live_tup and last_autovacuum updating regularly. Rising bloat with frequent vacuums means the cost limit is still too high for your storage; bloat with rare vacuums means the thresholds are still too loose. Adjust one variable at a time and observe for several days, because autovacuum operates on a slow timescale and a single snapshot misleads.

Preventing Autovacuum From Being Interrupted

A vacuum that starts and never finishes is worse than one that never starts, because it holds resources and defers the next attempt. Long-running read transactions block vacuum from reclaiming tuples, so a single idle-in-transaction connection can stall cleanup indefinitely. Check for the culprits regularly:

SELECT pid, state, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state  'idle' OR xact_start IS NOT NULL
ORDER BY duration DESC;

Any transaction open for hours is actively preventing dead-tuple reclamation. Fix the application bug or set idle_in_transaction_session_timeout so abandoned transactions are closed automatically. On a small VPS this single setting often does more for bloat control than any autovacuum parameter.

The Wraparound Safety Net

Autovacuum also advances the transaction ID counter that PostgreSQL relies on. If autovacuum cannot keep up for long enough, you approach transaction wraparound and the database forces an emergency vacuum that stops writes entirely. This is rare but catastrophic, and on a small instance with aggressive churn it is a realistic risk. Monitor the age of the oldest unfrozen transaction:

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database ORDER BY xid_age DESC;

Keep this number far below the wraparound limit. If it climbs steadily despite frequent autovacuum runs, the vacuum is not completing — revisit the blocked-transaction check above before touching any other parameter.

A Practical Starting Configuration

For a 2–4 GB VPS running a production web workload, a sensible baseline combines per-table thresholds on the churn-heavy tables with conservative global throttling. Set the global cost limit low enough that vacuum never starves the application, then rely on the per-table scale factors to keep dead tuples down where it matters. Write the global changes to postgresql.auto.conf via ALTER SYSTEM, and keep per-table overrides in a migration or schema-management file so they survive a database rebuild.

If bloat stays controlled but queries are still slow, the bottleneck has moved to memory or query planning. And if the table genuinely outgrows the plan, that is a purchasing decision, not a configuration one: compare storage and RAM guarantees across providers in the comparison section of our site before you spend another evening tuning a ceiling you cannot raise. Document every value you change and the date — autovacuum tuning is a slow feedback loop, and without a record you will repeat experiments. Measure, adjust one thing, wait, and measure again.