PostgreSQL Autovacuum Tuning: When Cleanup Falls Behind

Most PostgreSQL performance issues do not start dramatically.

No outage. No obvious error. No scary log line.

Just slower queries. Then more disk usage.

Then a table that keeps growing even though rows are being deleted.

In many cases, the quiet culprit is not the query itself.

It is autovacuum falling behind.

Autovacuum is one of PostgreSQL’s most important background processes. When it works well, nobody notices it. When it cannot keep up, the symptoms appear everywhere:

bloated tables, heavier indexes, stale statistics, bad query plans, longer scans, and eventually painful latency.

Autovacuum is not good or bad by itself.

It is a balance: cleanup must run early enough to prevent bloat, but smoothly enough that foreground queries do not feel it.

First, what problem is autovacuum solving?

MVCC creates old row versions. VACUUM later marks dead space reusable when no active transaction can still see it.

PostgreSQL uses MVCC.

That means an UPDATE does not simply overwrite a row in place.

It creates a new row version.

The old row version may still be needed by another transaction that started earlier, so PostgreSQL cannot immediately remove it.

Same with DELETE.

A deleted row is not physically removed immediately. It becomes a dead tuple after no active transaction can still see it.

So over time, write-heavy tables collect dead tuples.

Autovacuum comes in later and marks that space as reusable. That distinction matters.

If you want to physically shrink the table file, that is a different operation, such as VACUUM FULL, which rewrites the table and takes a heavy lock.

So when someone says:

I deleted millions of rows, but the table size did not reduce.

That does not automatically mean vacuum failed.

It usually means PostgreSQL has reusable space inside the table, but the file was not returned to the operating system.

Dead tuples are not harmless

Dead tuples are old row versions.

They may look like a cleanup detail, but they affect performance directly.

If a table has too many dead tuples, PostgreSQL may need to scan through more pages and more row versions to find the visible data.

A table scan in PostgreSQL is a heap scan, so more dead tuples usually means more heap pages to inspect.

Beyond slowing heap scans, dead tuples affect something less obvious too:

The visibility map

Autovacuum helps maintain the visibility map, which tracks pages where all tuples are visible to all transactions.

This matters because PostgreSQL can use the visibility map for index-only scans. If the visibility map is not up to date, PostgreSQL cannot trust that an index-only scan is safe.

It falls back to visiting the heap to confirm tuple visibility — even when the index already contains all the required columns.

How dead tuples affect indexes

Dead tuples can also affect indexes.

Even after heap cleanup, indexes may remain large or inefficient depending on the workload, update/delete patterns, and how often cleanup can keep up.

Symptoms may include:

• Index scans reading too many pages
• Slow updates or deletes
• Growing index size
• High buffer reads from index access
• Planner choosing less efficient paths

However, index usage and index bloat are not the same thing.

You can use pg_stat_user_indexes to understand how indexes are being used:

SELECT
  schemaname,
  relname,
  indexrelname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_tup_read DESC
LIMIT 20;

This query does not tell you whether an index is bloated. It helps you identify heavily used indexes, rarely used indexes, and indexes where many tuples are read compared to tuples fetched.

For actual index bloat investigation, use tools such as pgstattuple / pgstatindex() where appropriate:

SELECT * FROM pgstatindex('schema_name.index_name');

This can give a better view of index-level health, including density and fragmentation-related details.

But do not jump to REINDEX first.

First understand whether the problem is:

• Table bloat
• Index bloat
• Stale statistics
• Bad query pattern
• Missing or incorrect index
• Autovacuum not keeping up

REINDEX can help when the index itself is bloated, but it does not fix the root cause if the real issue is an update-heavy workload, poor vacuum behavior, long-running transactions, or a query pattern that reads too much data.

HOT updates can reduce index churn because PostgreSQL avoids creating new index entries when indexed columns are unchanged.

This is why lowering table fillfactor can sometimes help update-heavy tables: it leaves room on the same page, increasing the chance of HOT updates.

HOT updates are particularly efficient because they clean themselves up without needing a full autovacuum pass.

Why autovacuum sometimes feels slow

Autovacuum spends cost budget as it touches pages. Dirty pages burn the budget faster than page hits.

Autovacuum does not run at unlimited speed. It uses a cost-based delay system.

Think of it like a small budget.

Each autovacuum worker is allowed to spend a certain amount of “I/O cost”. Once it reaches the limit, it pauses briefly, then continues.

One important nuance:

When multiple autovacuum workers are active, the effective cost budget is shared across workers, so increasing workers alone does not always make vacuum faster unless the cost budget is also considered.

The important settings are:

autovacuum_vacuum_cost_limit = 200
autovacuum_vacuum_cost_delay = 2ms

For illustration, assume the effective vacuum cost limit is 200.

Support Tip: It’s worth noting that in PostgreSQL 12, the default cost_delay was reduced from 20ms to 2ms. This effectively made vacuum 10x faster by default. If a user is on an old version (PG 11 or below), their defaults are significantly more “choked.”

The cost limit is the budget.

The cost delay is the pause after the budget is used.

Different page operations have different costs:

vacuum_cost_page_hit   = 1
vacuum_cost_page_miss  = 10
vacuum_cost_page_dirty = 20

A page hit is cheap because the page is already in memory.

A page miss is more expensive because the page is not already in PostgreSQL’s buffer cache and must be fetched through the OS/storage path.

A dirty page is the most expensive because vacuum modifies the page and it must eventually be written back.

With a cost limit of 200, autovacuum could roughly do 200 page hits or 20 page misses or 10 dirty page operations before it pauses.

In practice, a single pass is usually a mix of all three. Some pages are already in cache, some are not, and some get modified. The budget is consumed by whatever combination of operations autovacuum actually performs.

This is why autovacuum can sometimes feel slow on large write-heavy tables. It is not just scanning rows.

It is spending cost budget as it touches pages, and dirty pages burn that budget much faster.

When PostgreSQL decides a table needs vacuum

PostgreSQL does not vacuum every table continuously. Instead, it uses thresholds to decide when a table needs cleanup.

A simplified mental model is:

vacuum trigger = 
dead_tuples > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor 
× estimated_live_tup

The default behavior is reasonable for many databases. Small tables should not be vacuumed too frequently, and large tables should not be vacuumed after every small change.

For very large tables, we often switch from a scale_factor (percentage) to a flat vacuum_scale_factor = 0 and use autovacuum_vacuum_threshold= 50000. This ensures vacuum runs after a fixed number of changes, regardless of how much the table grows.

For example, with the default autovacuum_vacuum_scale_factor = 0.2, a table with 100 million rows may wait until roughly 20 million tuples are updated or deleted before autovacuum is triggered.

For a busy OLTP table, that can be too late.

By the time autovacuum starts:

• Dead tuples may already be consuming space
• Indexes may have become heavier
• Queries may already be doing extra work
• Storage may already be growing

At that point, adding more CPU usually does not solve the problem, because the bottleneck is not CPU. The cleanup policy is simply too late for that table.

This is why large write-heavy tables often need table-level autovacuum tuning instead of random global changes.

A 10,000-row lookup table and a 500-million-row event table should not follow the same vacuum behavior.

Why defaults work — until one table gets too hot

It is easy to blame defaults.

But defaults are designed to be safe for many workloads, not perfect for your workload.

For small and moderate systems, default autovacuum settings are often fine.

The trouble usually starts when one table becomes much larger or much hotter than the rest of the database.

Event tables, Job queues, Audit tables, Session tables, High-update status tables, Multi-tenant activity tables, Frequently updated JSON-heavy tables are some examples.

These tables can generate dead tuples faster than autovacuum can clean them. That is when global settings are too blunt.

The better fix is usually per-table tuning.

Example:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

This tells PostgreSQL:

Do not wait for 20% of this table to change before vacuum/analyze becomes interesting.

For a large hot table, that can make a big difference.

In one production case, a write-heavy table had around 180 million rows and was receiving constant updates throughout the day.

With the default autovacuum_vacuum_scale_factor = 0.2, PostgreSQL could wait until roughly 36 million tuples were updated or deleted before vacuum became aggressive enough for that table.

By that time, query latency had already increased, index scans were touching more pages, and storage growth looked abnormal.

The fix was not adding more CPU. The fix was lowering the table-level vacuum and analyze scale factors so cleanup and statistics refresh happened earlier for that specific table.

Why append-only tables need different thinking

Different workloads create different vacuum pressure.

UPDATE-heavy and DELETE-heavy tables create dead tuples and usually need more careful autovacuum tuning.

Plain INSERT-heavy tables do not create dead tuples in the same way, but they still need vacuum for freezing and visibility map maintenance.

Since PostgreSQL 13, insert-triggered autovacuum settings can trigger vacuum on insert-heavy tables, mainly to support freezing and visibility map maintenance even when there are few or no dead tuples.

So when tuning autovacuum, separate the workload type:

• Update-heavy tables
• Delete-heavy tables
• Insert-heavy append-only tables
• Mixed OLTP tables

They do not all need the same treatment.

For update/delete-heavy tables, dead tuple cleanup is the main concern.

For insert-heavy tables, The problem is usually not dead tuple cleanup in the same way as update/delete-heavy tables.

The bigger concerns are often:

• freezing old tuples
• maintaining the visibility map
• keeping planner statistics fresh
• managing table growth
• avoiding very large unpartitioned tables

This is why insert-heavy event or audit tables often benefit more from partitioning, retention policies, and regular analyze than from only lowering the dead-tuple vacuum scale factor.

Autovacuum also updates statistics

Vacuum is only one part of the story.

The autovacuum subsystem also triggers auto-analyze, which updates planner statistics.

This is critical because the PostgreSQL planner depends on statistics.

If statistics are stale, the planner may make bad decisions:

• Choose a sequential scan when an index scan would be better
• Underestimate rows
• Overestimate rows
• Choose a bad join order
• Pick a bad join type
• Use an inefficient plan after data distribution changes

A common pattern:

• A table grows quickly.
• Queries become slow.
• Everyone looks for missing indexes.

But the real issue is stale statistics.

That is why autovacuum_analyze_scale_factor can be just as important as autovacuum_vacuum_scale_factor.

For manual maintenance after large data changes, Manual ANALYZE can be useful because it refreshes planner statistics.

For volatile tables, analyze needs to happen early enough for the planner to stay informed.

In production, high CPU usage and P99 latency are sometimes caused not by missing indexes, but by outdated planner statistics. Running ANALYZEor tuning autovacuum_analyze_scale_factor can help the planner make better decisions earlier.

Long-running/Open transactions can make autovacuum look broken

This is one of the most common production traps.

Autovacuum may be running. The settings may look reasonable. But dead tuples are not being removed.

Why?

Because an old transaction is still open.

PostgreSQL cannot remove row versions that may still be visible to an old snapshot. One long-running transaction can therefore hold back cleanup across important tables.

This includes:

• Long-running queries
• idle-in-transaction sessions
• Forgotten application transactions
• Migration tools
• Reporting jobs
• Stuck clients

If vacuum is blocked by old snapshots, making autovacuum more aggressive does not solve the real problem.

What falling behind looks like

When vacuum fails, the ‘Heap’ becomes sparse. Postgres has to load more 8KB pages into memory just to find a few live rows, driving up I/O.

When autovacuum falls behind, you often see a pattern like this:

dead tuples increase
      ↓
table/index bloat grows
      ↓
queries touch more pages
      ↓
I/O increases
      ↓
planner estimates become less reliable
      ↓
latency becomes unstable

The application team may report:

Queries are randomly slow.

Or:

The database is slow only during traffic peaks.

Or:

We deleted data, but storage is still high.

Or:

The query plan changed suddenly.

Autovacuum may not be the only cause, but it should be part of the investigation.

How to tell if autovacuum is falling behind

Five checks I run when autovacuum falls behind

1. Tables with many dead tuples

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_autoanalyze,
  vacuum_count,
  autovacuum_count,
  analyze_count,
  autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Keep an eye on n_dead_tup compared to n_live_tup.

If n_dead_tup keeps climbing while last_autovacuum is old or frequent autovacuum runs are not reducing it, autovacuum may be losing the race.

This does not prove physical bloat by itself, but it tells you where to look first.

2. Long-running transactions

SELECT
  pid,
  now() - xact_start AS xact_age,
  state,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

If old transactions exist, fix that before assuming autovacuum settings are wrong.

3. Vacuum progress

Check current vacuum progress with:

SELECT
  pid,
  relid::regclass AS table_name,
  phase,
  heap_blks_total,
  heap_blks_scanned,
  heap_blks_vacuumed,
  index_vacuum_count,
  num_dead_tuples
FROM pg_stat_progress_vacuum;

This helps confirm whether vacuum is actively progressing or spending time on heap scanning, index vacuuming, cleanup, or truncation.

4. XID age / wraparound risk

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

Wraparound prevention is one of autovacuum’s most important jobs.

Ignoring it is dangerous. In severe wraparound-risk situations, PostgreSQL can stop accepting writes to protect data integrity.

5. Table-level settings

SELECT
  relname,
  reloptions
FROM pg_class
WHERE reloptions IS NOT NULL;

This helps check whether specific hot tables already have custom autovacuum settings.

A practical tuning workflow

For a large update/delete-heavy table, I usually think in this order:

Step 1: Confirm the table is actually the problem

Check dead tuples, table size, query plans, and access patterns.

Do not tune blindly.

Step 2: Rule out cleanup blockers

Check long-running transactions, idle-in-transaction sessions, and replication slots before changing vacuum settings.

Step 3: Lower scale factors on hot large tables

Example:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

For very large tables, you may go lower.

The point is to trigger cleanup earlier.

Step 4: Adjust cost settings carefully

If vacuum is too slow, consider table-level cost settings.

Example:

ALTER TABLE orders SET (
  autovacuum_vacuum_cost_limit = 2000,
  autovacuum_vacuum_cost_delay = '2ms'
);

Do this carefully.

The right value depends on storage capacity, workload, and concurrency.

Step 5: Watch storage and checkpoint behavior

If storage is already saturated, making autovacuum more aggressive can hurt.

Always correlate with I/O metrics.

Step 6: Consider partitioning or workload changes

Sometimes tuning is not enough.

If one table is a constant write/delete hotspot, partitioning may be the better design.

For queue-like tables, partition rotation and TRUNCATE can be much cleaner than endless row-by-row churn.

Vacuum prevents bloat, but does not always shrink files

This is another common misunderstanding.

Vacuum helps prevent bloat from getting worse by making dead space reusable.

But if a table is already heavily bloated, normal vacuum may not reduce the physical file size.

You may need other approaches:

• VACUUM FULL
• pg_repack
• Table rewrite
• Partition rebuild
• dump/restore in extreme cases

But these are heavier operations and need planning.

VACUUM FULLtakes an exclusive lock.

VACUUM FULLshould be treated as a planned maintenance operation, not a quick production fix.

VACUUM VERBOSE can help show what vacuum actually cleaned, how many pages were scanned, and whether dead tuples were removable.

A safer production-friendly option is often pg_repack, if available and supported. It rebuilds bloated tables or indexes with much less blocking than VACUUM FULL, although it still needs planning, extra disk space, and extension support.

When autovacuum is blocked, the symptoms spread

A blocked or ineffective autovacuum process does not just affect one metric.

It can impact:

Query latency

Storage growth

Index efficiency

Planner quality

WAL volume

Replica lag

Backup size

Maintenance windows

Wraparound risk

Replica lag can also increase indirectly. When cleanup falls behind, table and index bloat can generate more I/O and WAL activity, while long-running transactions or replication slots may prevent old WAL from being removed, putting more pressure on replicas and recovery.

That is why autovacuum deserves attention.

It is not background noise. It is part of the database’s health system.

When investigating autovacuum-related performance issues, I ask:

1. Which tables have the most dead tuples?
2. Are those tables large enough that the default scale factor is too late?
3. When did autovacuum last run?
4. Is autovacuum running but unable to clean because of old transactions?
5. Are statistics stale?
6. Are checkpoints or storage latency causing vacuum I/O to hurt foreground traffic?
7. Is XID age approaching dangerous territory?
8. Is this a table design problem, such as queue churn or hot updates?
9. Should this be solved with per-table settings instead of global settings?
10. Is the table already bloated enough to require a rewrite/repack?

This checklist is often more useful than starting with parameter changes.

Final takeaway

Autovacuum prevents PostgreSQL from drifting into slower performance over time.

Autovacuum is easy to ignore because it runs in the background.

But background does not mean optional.

When it falls behind, your foreground queries eventually pay. When it is too aggressive, your foreground queries may also pay.

The real skill is not simply enabling autovacuum or increasing every value.

The skill is knowing which table needs earlier cleanup, which workload needs smoother cleanup, and when the real issue is storage pressure, old transactions, replication slots, or table design.

A healthy PostgreSQL system is not just one where queries are fast today.

It is one where cleanup keeps up quietly enough that queries stay predictable tomorrow.