Nov 20, 20265 min read

Postgres Autovacuum Tuning for Busy Tables

Why the default autovacuum settings fall behind on large and frequently updated tables, how the thresholds and cost limits work, per-table settings that fix it, and the long-running transactions that stop vacuum regardless of tuning.

Illustration for Postgres Autovacuum Tuning for Busy Tables

Every UPDATE and DELETE in Postgres leaves the old row version behind, and autovacuum is the background process that reclaims it. Its defaults suit small and medium tables.

On a busy queue table or a 200 million row events table they fall behind until bloat and slow index scans make the lag visible, and the fix is usually to tune those few tables rather than the whole server.

When autovacuum triggers

The autovacuum docs give the rule: a table is vacuumed when its dead tuples exceed

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples 50 + 0.2 * rows

On a 10,000 row table that is about 2,050 dead rows, which happens often and cleans up quickly. On a 100 million row table it is 20 million dead rows, and by then the table and its indexes carry gigabytes of dead space. Analyze uses the same formula with a 10% scale factor, so statistics on large tables go stale for long stretches too. From Postgres 13 on, inserts also trigger vacuum through autovacuum_vacuum_insert_threshold and its scale factor, which matters for append-only tables that need their visibility map updated.

Throttling

Autovacuum deliberately runs slowly so it does not compete with queries. Each worker accumulates cost for pages it reads and dirties, and sleeps for autovacuum_vacuum_cost_delay (2 ms by default) whenever it reaches the cost limit, which defaults to vacuum_cost_limit of 200 shared across all workers, as described in the autovacuum parameters reference. On modern SSD storage, that limit is often too conservative for a large table, and raising it to 1000 or 2000 lets each run finish in a fraction of the time. Raising autovacuum_max_workers alone does not help much, since the cost limit is divided among the active workers.

Per-table settings

The most effective change is per table, where storage parameters override the global values for one table only:

-- A large, frequently updated table: vacuum after ~1% churn instead of 20% ALTER TABLE events SET ( autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 10000, autovacuum_analyze_scale_factor = 0.02, autovacuum_vacuum_cost_limit = 2000 ); -- A small, hot queue table: vacuum after a fixed number of dead rows ALTER TABLE jobs SET ( autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 5000 );

Setting the scale factor to 0 and using a fixed threshold is a common pattern for tables whose size stays roughly constant while rows churn, such as job queues and session tables.

Finding tables that fall behind

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, last_autovacuum, autovacuum_count FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

A table with a high dead percentage and a recent last_autovacuum is being vacuumed but not cleaned, which points to the next section. A table with an old last_autovacuum needs per-table thresholds.

The xmin horizon

Vacuum can only remove row versions that no running transaction might still need. One session left idle in transaction, a six hour analytics query, a stale prepared transaction or an inactive replication slot pins that horizon, and vacuum then runs repeatedly without freeing anything on any table. Check pg_stat_activity for old xact_start values and pg_replication_slots for inactive slots, and set idle_in_transaction_session_timeout so abandoned sessions end on their own.

The same mechanism protects against transaction ID wraparound: if freezing falls far enough behind, Postgres forces an anti-wraparound vacuum that cannot be skipped, and in the extreme stops accepting writes. Keeping regular vacuum healthy prevents ever getting close.

Best fit: per-table scale factors and thresholds for the few large or hot tables, a higher cost limit on SSD storage, and an idle transaction timeout everywhere.

Autovacuum settings with Encore

Encore

Encore is a backend framework with built-in infrastructure, and the per-table settings above fit into the service's migrations, which run as the database's owning role locally and on deploy. The table, its autovacuum settings and the code that churns it then live in one service, and a metric can track dead tuples so you notice before bloat does:

Get started
brew install encoredev/tap/encore &&
encore app create
Copy

Run in your terminal to get started locally.

-- jobs/migrations/1_jobs.up.sql CREATE TABLE jobs ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, status text NOT NULL DEFAULT 'queued', payload jsonb NOT NULL, updated_at timestamptz NOT NULL DEFAULT now() ); ALTER TABLE jobs SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 5000);
package jobs import ( "context" "encore.dev/cron" "encore.dev/metrics" "encore.dev/storage/sqldb" ) var db = sqldb.NewDatabase("jobs", sqldb.DatabaseConfig{Migrations: "./migrations"}) type TableLabels struct { Table string } var DeadTuples = metrics.NewGaugeGroup[TableLabels, int64]("db_dead_tuples", metrics.GaugeConfig{}) var _ = cron.NewJob("record-dead-tuples", cron.JobConfig{ Title: "Record dead tuples per table", Every: 10 * cron.Minute, Endpoint: RecordDeadTuples, }) //encore:api private func RecordDeadTuples(ctx context.Context) error { rows, err := db.Query(ctx, `SELECT relname, n_dead_tup FROM pg_stat_user_tables`) if err != nil { return err } defer rows.Close() for rows.Next() { var table string var dead int64 if err := rows.Scan(&table, &dead); err != nil { return err } DeadTuples.With(TableLabels{Table: table}).Set(dead) } return rows.Err() }

encore run starts Postgres locally and applies the migration, so the storage parameters are in place on a laptop too. The cron job is managed by the Encore platform in deployed environments and does not run locally, so call RecordDeadTuples by hand there. In production the database is RDS or Cloud SQL in your account, where global settings such as the cost limit stay in the parameter group, as the AWS infrastructure docs describe.

An AI coding agent creating a new high-churn table can add its autovacuum settings in the same migration, which is far easier to review than a parameter group change made weeks after the bloat appeared.

Best fit: services with queue-like or large tables that want vacuum settings versioned with the schema.

How to approach it

Find the tables with the most dead tuples, give them per-table scale factors and thresholds, and raise the cost limit if vacuum runs take hours. Before tuning anything, check for long transactions and inactive replication slots, since no setting helps while the xmin horizon is pinned.

Frequently asked questions

When does autovacuum run on a Postgres table?

Autovacuum vacuums a table when its dead tuples exceed autovacuum_vacuum_threshold plus autovacuum_vacuum_scale_factor times the row count, which is 50 plus 20% of the table by default. On a 100 million row table that means about 20 million dead rows before it starts.

How do I make autovacuum run more often on one table?

Set per-table storage parameters, for example ALTER TABLE jobs SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 1000). Only that table changes, and the setting is stored with the table definition.

Why is autovacuum not removing dead tuples?

The usual cause is an old transaction that is still open. Vacuum cannot remove row versions that any running transaction might still see, so a session idle in transaction, a long report or an abandoned replication slot holds back cleanup on every table until it ends.

How do I set autovacuum parameters for an Encore database?

Put ALTER TABLE ... SET (autovacuum_...) statements in a migration file next to the CREATE TABLE. The migration runs as the role that owns the tables, both on the local Postgres from encore run and on deploy.

Deploy to your cloud on AWS and Google Cloud
without managing infrastructure

Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.

~/orders — claude
Claude Code
Claude Code v2.1.180Opus 5.5~/orders