How Autovacuum Tuning works in PostgreSQL

 Autovacuum Tuning in PostgreSQL

Autovacuum is PostgreSQL's automatic background process that:

1. Removes dead tuples created by UPDATE/DELETE.
2. Updates optimizer statistics (ANALYZE).
3. Prevents transaction ID wraparound, which is critical for database survival.

A DBA's first priority is often:

Ensure autovacuum is running effectively before changing application code.

---

How Autovacuum Decides to Run

For VACUUM:

Trigger =

autovacuum_vacuum_threshold

+

autovacuum_vacuum_scale_factor × number_of_rows

Default values:

autovacuum_vacuum_threshold = 50

autovacuum_vacuum_scale_factor = 0.2

Example:

Table:

10,000 rows

Trigger:

50 + (10000 × 0.2)

= 2050 changes

After about 2050 updates/deletes:

Autovacuum starts

---

Problem with Large Tables

Consider:

100 million rows

Default trigger:

50 + (100,000,000 × 0.2)

= 20,000,050 changes

Autovacuum waits until:

20 million row modifications

which is often far too late.

This causes:

Table bloat

Index bloat

Slow queries

---

Typical Production Setting

Instead of:

autovacuum_vacuum_scale_factor = 0.2

Many DBAs use:

autovacuum_vacuum_scale_factor = 0.01

or

0.005

Example:

100 million row table

Trigger:

50 + (100,000,000 × 0.01)

≈ 1,000,050

Much more aggressive.

---

Important Parameters

1. autovacuum_vacuum_scale_factor

Most important tuning parameter.

Check:

SHOW autovacuum_vacuum_scale_factor;

Typical values:

Table Type	Value
Small tables	0.05 - 0.2
OLTP tables	0.01
Heavy update tables	0.005
Very busy tables	0.001

---

2. autovacuum_vacuum_threshold

Default:

50

Formula:

Threshold + Scale Factor

Example:

autovacuum_vacuum_threshold=500

Useful when small tables receive frequent updates.

---

3. autovacuum_analyze_scale_factor

Controls statistics refresh.

Default:

0.1

For OLTP systems:

0.02

or

0.01

helps query planning.

---

4. autovacuum_max_workers

Default:

3

Number of autovacuum workers.

Large systems:

autovacuum_max_workers = 10

or

15

Common on servers with:

16-64 CPUs

---

5. autovacuum_naptime

Default:

1min

Worker wake-up interval.

Usually:

1min

is fine.

Rarely changed.

---

6. autovacuum_vacuum_cost_limit

Controls speed.

Default:

200

Many production systems:

1000

or

2000

Allows faster cleanup.

---

7. autovacuum_vacuum_cost_delay

Default:

2ms

Lower value:

1ms

or

0

means faster vacuum.

Tradeoff:

More IO

More CPU

---

Per-Table Tuning

Usually better than global tuning.

Example:

ALTER TABLE orders

SET (

  autovacuum_vacuum_scale_factor = 0.005,

  autovacuum_analyze_scale_factor = 0.005

);

Very common for:

orders

transactions

event_logs

audit tables

---

Monitoring Autovacuum

Check Dead Tuples

SELECT

  relname,

  n_live_tup,

  n_dead_tup

FROM pg_stat_user_tables

ORDER BY n_dead_tup DESC;

Look for:

dead tuples > 10%-20% of table

---

Last Vacuum

SELECT

  relname,

  last_autovacuum,

  last_autoanalyze

FROM pg_stat_user_tables;

---

Running Vacuums

SELECT *

FROM pg_stat_progress_vacuum;

Shows:

Current table

Blocks scanned

Blocks cleaned

---

Detect Table Bloat

Common symptom:

Table grows continuously

even though row count stays stable.

For example:

orders table

Rows: 20 million

Expected Size: 15 GB

Actual Size: 80 GB

Usually means:

Autovacuum not keeping up

---

Anti-Patterns

1. Disabling Autovacuum

ALTER TABLE mytable

SET (autovacuum_enabled = false);

Dangerous.

Can lead to:

Massive bloat

Transaction wraparound risk

Never disable without a specific reason.

---

2. Increasing Scale Factor

Bad idea:

autovacuum_vacuum_scale_factor=0.5

Large tables may never get cleaned in time.

---

3. Ignoring Long Transactions

Check:

SELECT pid,

       now()-xact_start

FROM pg_stat_activity

WHERE xact_start IS NOT NULL;

A transaction running for hours can prevent dead tuple removal even if autovacuum runs.

---

Transaction ID Wraparound (Most Important)

Autovacuum does more than reclaim space.

PostgreSQL transaction IDs are finite (~2 billion usable before danger).

Autovacuum freezes old tuples:

VACUUM FREEZE

Without this:

database shutdown risk

PostgreSQL will eventually force emergency vacuuming to avoid corruption.

So:

Oracle DBAs typically worry about UNDO_RETENTION, while PostgreSQL DBAs worry about whether Autovacuum can keep up with dead tuples and transaction ID freezing.

Practical OLTP Starting Point

For a busy application database:

autovacuum_max_workers = 8

autovacuum_vacuum_scale_factor = 0.01

autovacuum_analyze_scale_factor = 0.02

autovacuum_vacuum_cost_limit = 2000

autovacuum_vacuum_cost_delay = 1ms

Then monitor:

pg_stat_user_tables

pg_stat_progress_vacuum

pg_stat_activity

and tune high-update tables individually rather than applying extremely aggressive settings to the entire cluster.