Postgres Autovacuum Tuning for Small DBs

Your Nextcloud Is Slow and Nobody Touched It

You set up Postgres in Docker, pointed Nextcloud or Gitea at it, and forgot about it. Six months later one table is several times bigger than its live data, queries crawl, and the disk is filling with rows nobody can see. The janitor exists. It just shows up too late for your one busy table.

Here is the short version. On a small database, leave almost every autovacuum setting alone. Raise autovacuum_vacuum_cost_limit a bit, turn on logging so you can see what autovacuum does, and put per-table overrides on the one or two tables that churn. The global default is fine for the small tables and wrong for the big hot one, and the per-table knob fixes exactly that.

Full example: Clone the working files at github.com/KingPin/sumguy-examples/self-hosting/postgres-autovacuum-tuning

Everything below ran on postgres:18.6, the current stable image as of November 2026. Postgres 19 is only at release candidate stage, so skip it for now.

Why the Default Waits Too Long on a Hot Table

An UPDATE or DELETE in Postgres does not remove the old row. It leaves a dead tuple behind, and vacuum cleans it up later. Autovacuum starts on a table when dead tuples pass this threshold (formula from the Postgres 18 docs):

threshold = min(autovacuum_vacuum_max_threshold,
                autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples)

The defaults are autovacuum_vacuum_threshold = 50 and autovacuum_vacuum_scale_factor = 0.2. So a 100-row config table triggers at 70 dead rows. That is fine. A 100,000-row table triggers at 20,050 dead rows. A 5-million-row events table triggers at 1,000,050 dead rows. That is a million dead rows sitting in your indexes and heap before the janitor even looks up.

I checked this on a container. I created two tables, updated 15% of each, and looked at the numbers with autovacuum switched off on both:

  relname  | reltuples | n_dead_tup | trigger_at | due
-----------+-----------+------------+------------+-----
 big_hot   |    100000 |      15000 |      20050 | f
 small_cfg |       100 |         15 |         70 | f

The big table sat at 15,000 dead rows with nothing due. The small table is not due either, but 15 dead rows cost nothing. A percentage rule makes the wait grow with the table, so one global number cannot fit both sizes.

One more term in that formula: autovacuum_vacuum_max_threshold. Postgres 18 added it, with a default of 100,000,000 dead tuples. It caps the trigger on enormous tables. With 0.2, the cap only starts to matter above roughly 500 million rows. On a home-lab database it never fires, so ignore it.

Watch It Work Before You Touch It

Before tuning, look. The view is pg_stat_user_tables:

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

A table with a high n_dead_tup and an old or empty last_autovacuum is your hot table. The kit has a longer version that computes trigger_at for every table and respects per-table scale factor and threshold overrides, so you can see how far each table is from its next vacuum.

In Postgres 18 the same view also has total_vacuum_time and total_autovacuum_time columns (I checked the column list on the container). Those tell you which table eats the most vacuum time.

To see a vacuum in the act, use the progress view:

SELECT p.pid, p.relid::regclass AS table_name, p.phase,
       p.heap_blks_total, p.heap_blks_scanned, p.heap_blks_vacuumed
FROM pg_stat_progress_vacuum p;

Empty result means nothing is vacuuming right now. That is normal most of the time.

Also turn on the log. The default log_autovacuum_min_duration is 10 minutes, which hides almost every run on a small database. One second shows you the real picture:

log_autovacuum_min_duration = 1s

Global Settings: Change Little, Change One Thing

Postgres 18 ships these defaults (I read them from pg_settings on the container):

Leave the naptime, the worker count, and the global scale factor alone. Lowering the global scale factor makes every table vacuum more often, including the tiny ones that did not need it. You pay in churn for tables that were fine.

The one global worth raising is the cost limit. Autovacuum is throttled on purpose. Each page it touches adds to a running cost: 1 for a buffer hit, 2 for a miss, 20 for dirtying a page. When the total reaches the limit, the worker sleeps for the cost delay. With the defaults (limit 200, delay 2ms), a worker that is dirtying pages sleeps after about 10 of them. The budget is also split across all running workers, so three busy workers get a third each.

That throttle protects a database that shares a disk with a busy app. It is conservative for a modern SSD. Raise the limit:

# postgresql.conf, no restart needed (pg_reload_conf())
autovacuum_vacuum_cost_limit = 1000
log_autovacuum_min_duration = 1s

Five times the budget, same 2ms nap. I would not touch the delay. If vacuum still lags, fix the hot table and leave the global alone.

For Docker, you can pass these on the command line or mount a file. The kit uses a mounted file:

services:
  db:
    image: postgres:18.6
    environment:
      POSTGRES_PASSWORD: change-me
    command: ["postgres", "-c", "config_file=/etc/postgresql/autovacuum.conf"]
    volumes:
      - ./autovacuum.conf:/etc/postgresql/autovacuum.conf:ro
      - pgdata:/var/lib/postgresql

volumes:
  pgdata:

That is a demo shape: the mounted file is the whole config, so the kit’s file also sets listen_addresses. On a real setup, copy the lines into your existing postgresql.conf or use -c autovacuum_vacuum_cost_limit=1000 flags instead.

Per-Table Overrides: The Actual Fix

Now the hot table. Say it is sessions, or a job queue, or the Home Assistant states table. Give it its own thresholds:

ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold    = 1000
);

Now the trigger is 1000 + 0.01 * reltuples. On the 100,000-row demo table that is 2,000 dead rows instead of 20,050. I applied exactly that override to the demo table. After the next autovacuum pass (about a minute, because of the naptime) the table showed:

 relname | reltuples | n_dead_tup | trigger_at | last_autovacuum              | autovacuum_count
---------+-----------+------------+------------+------------------------------+-----------------
 big_hot |    100000 |          0 |       2000 | 2026-11-05 20:05:22.40092+00 |                1

Dead rows went from 15,000 to 0. The small table next to it still had its 15 and nobody cared.

Per-table storage parameters can also carry the cost settings, if one table needs to run faster than the global budget:

ALTER TABLE sessions SET (autovacuum_vacuum_cost_limit = 2000);

Check what is set, and undo it:

SELECT relname, reloptions FROM pg_class WHERE relname = 'sessions';

ALTER TABLE sessions RESET (autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold);

Overrides live in pg_class.reloptions, so they travel with the table in dumps and with a pg_upgrade. They do not survive you recreating the table, which is the one gotcha. If an app migration drops and recreates sessions, your override is gone. Put the ALTER TABLE in a deploy script.

Don’t Forget the Analyze Half

Autovacuum does two jobs. Vacuum removes dead rows. Analyze refreshes the planner statistics, the row counts and value histograms Postgres uses to pick between an index scan and a sequential scan. The analyze trigger has its own pair of knobs, and they have the same blind spot on big tables:

analyze_trigger = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * reltuples

The defaults on 18.6 are autovacuum_analyze_threshold = 50 and autovacuum_analyze_scale_factor = 0.1. Postgres compares that number to n_mod_since_analyze, the count of rows inserted, updated, or deleted since the last analyze. On a 5-million-row table that is 500,050 changed rows before the planner’s value statistics change.

Stale stats cause a quieter failure than bloat. Postgres scales the row count to the table’s current size on its own, but the value statistics (most-common values and histograms) only change when analyze runs. Say one status in your sessions table grows from 1% of rows to 40%. The planner keeps estimating 1%, picks a nested loop sized for a few rows, and a query that took 3 ms takes 3 seconds. Nothing is broken. The plan is just based on data that no longer exists.

Watch it with the same view:

SELECT relname, n_live_tup, n_mod_since_analyze,
       last_autoanalyze, autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_mod_since_analyze DESC
LIMIT 10;

The fix is the same shape as before. Put a tighter analyze trigger on the hot table, next to the vacuum override:

ALTER TABLE sessions SET (
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_analyze_threshold    = 500
);

A queue table that stays tiny but churns all day is the opposite case. With 500 live rows, the vacuum trigger is 150 dead rows and the analyze trigger is 100 changed rows, so autovacuum visits it about once a minute (the naptime) with no tuning at all. Leave tables like that on the defaults. The scale factor only hurts once a table gets big.

Insert-Only Tables Need Vacuum Too

Since Postgres 13, a table that only gets inserts still triggers autovacuum. The settings are autovacuum_vacuum_insert_threshold (default 1000) and autovacuum_vacuum_insert_scale_factor (default 0.2). Insert-triggered vacuums set visibility-map bits, which helps index-only scans, and freeze old rows before they become a wraparound problem.

In Postgres 18 the docs define the insert scale factor against the share of the table that is not yet frozen, not the whole table. For an append-only event log you can tighten it per table:

ALTER TABLE event_log SET (
  autovacuum_vacuum_insert_scale_factor = 0.05,
  autovacuum_vacuum_insert_threshold    = 5000
);

What Else Postgres 18 Changed

The Postgres 18 release notes list three autovacuum items worth knowing:

1. autovacuum_vacuum_max_threshold (covered above): a hard cap on the dead-tuple trigger, default 100 million.
2. autovacuum_worker_slots: reserves backend slots for workers at server start (default 16, restart required). Because the slots are already reserved, autovacuum_max_workers can now change with a plain reload, up to the slot count. Before 18 you needed a restart to change the worker count.
3. Eager freezing: normal vacuums now freeze some pages that are already all-visible, to cut the cost of the big freezing pass later. It is controlled by vacuum_max_eager_freeze_failure_rate (default 0.03), also settable per table. You do not need to touch it.

None of these force a config change on a small database. The worker-slots change is the reason you can now change the worker count without a restart.

Long Transactions Block Cleanup, Whatever You Tune

You can set perfect thresholds and still end up with bloat, because vacuum cannot remove a dead row that an open transaction might still see. One forgotten session sitting “idle in transaction” after a write, or in REPEATABLE READ, holds the cleanup horizon back for every table in its database.

I reproduced it. I held a write transaction open in a second session, updated all 50,000 rows of a test table, and ran a verbose vacuum:

tuples: 0 removed, 100000 remain, 50000 are dead but not yet removable

After the transaction closed, the same vacuum printed tuples: 50000 removed, 50000 remain, 0 are dead but not yet removable. Autovacuum ran fine. The open transaction was the problem.

Find the culprits with the oldest open transactions:

SELECT pid, usename, state,
       now() - xact_start AS xact_age,
       backend_xmin, left(query, 60) AS last_query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND pid <> pg_backend_pid()
ORDER BY xact_start
LIMIT 10;

Two other holders hide outside pg_stat_activity: forgotten prepared transactions (pg_prepared_xacts) and stale replication slots (pg_replication_slots). The kit’s 03_blockers.sql checks all of them.

To stop the problem at the source, set a timeout:

idle_in_transaction_session_timeout = 10min

The default is 0, which means never. Postgres terminates sessions that sit idle in a transaction longer than that. Apps with a leaky connection pool will start seeing errors, which also tells you where the leak is. Test it on the one app first, because a 2 AM page about “terminating connection” is a poor way to learn your Home Assistant integration holds transactions open.

XID Wraparound: One Query Is Enough

Postgres numbers transactions with a 32-bit counter. Vacuum “freezes” old rows so the counter can wrap safely. If freezing falls too far behind, Postgres eventually stops accepting writes. That is the scary story.

On a small database it almost never happens, because autovacuum forces an anti-wraparound vacuum once a table’s age passes autovacuum_freeze_max_age (default 200,000,000). One query keeps you honest:

SELECT datname, age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid)
             / current_setting('autovacuum_freeze_max_age')::int, 1) AS pct_of_limit
FROM pg_database
ORDER BY xid_age DESC;

On a fresh container every database showed an age under 50. If a value approaches 100% and keeps climbing, something is blocking vacuum, and the previous section is where to look. Put this query on a dashboard or a weekly cron and forget it.

When you do have real bloat that vacuum cannot reclaim, pg_repack vs VACUUM FULL covers the cleanup. Tuning is how you avoid needing it.

The Short Checklist

1. Run the pg_stat_user_tables query and find the table with the most dead rows.
2. Set autovacuum_vacuum_cost_limit = 1000 and log_autovacuum_min_duration = 1s, then reload.
3. Put autovacuum_vacuum_scale_factor = 0.01 with a threshold of about 1000, plus autovacuum_analyze_scale_factor = 0.02, on each hot table.
4. Set idle_in_transaction_session_timeout and check pg_stat_activity for old transactions.
5. Run the age(datfrozenxid) query once a week.

That is a ten-minute job, and it replaces the usual “should I just run VACUUM FULL at 2 AM” conversation with a config file.

Common Questions

Should I turn off autovacuum on a small Postgres database?

No. Autovacuum removes dead rows and refreshes planner statistics, and without it a small database still bloats and its query plans drift. Postgres also forces anti-wraparound vacuums even with autovacuum off, so you get the heavy runs anyway, just later and bigger. Leave autovacuum = on and tune per-table settings for the one or two tables that churn.

How do I check if autovacuum is running in Postgres?

Query pg_stat_user_tables and read last_autovacuum and autovacuum_count for each table. A recent timestamp means autovacuum ran. For a vacuum in progress right now, query pg_stat_progress_vacuum. Setting log_autovacuum_min_duration = 1s also writes every autovacuum run that takes longer than one second to the server log, with tuples removed and timing.

Is autovacuum_vacuum_scale_factor 0.2 too high?

For small tables, no. For a large, busy table, yes: 0.2 means 20% of the rows must be dead before vacuum starts. Keep the global 0.2 and override a hot table with ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.01) instead of changing every table.

Do I need VACUUM FULL after tuning autovacuum?

No. Regular autovacuum marks dead space for reuse inside the table, which stops further growth. VACUUM FULL only matters to return disk space from a table that has already bloated badly, and it takes an exclusive lock while it rewrites the table. Tune first, and use it only for existing bloat.

Can I tune autovacuum on managed Postgres like RDS or Cloud SQL?

Mostly yes. Per-table ALTER TABLE ... SET storage parameters work on managed services, and so do monitoring queries on pg_stat_user_tables. Global settings go through the provider’s parameter groups or flags instead of postgresql.conf. Check which parameters your provider lets you change.

Related Reading

• pg_repack vs VACUUM FULL: Reclaim Bloat Live
• Postgres in Docker: The Durable Setup
• Postgres EXPLAIN ANALYZE Without Crying
• pg_stat_statements: What Most Tutorials Miss
• PostgreSQL + Linux: Kernel Tuning That Actually Matters