Autovacuum: defaults, per-table tuning, and monitoring

Autovacuum is PostgreSQL's daemon that runs VACUUM and ANALYZE automatically on your tables. It's the difference between having a healthy database and one that progressively degrades — without it, bloat would accumulate nonstop and stats would never get updated. It's active by default and that's good.

What's not so good is that its default parameters were calibrated for small tables and moderate workloads. In 2026, with tables of millions of rows and sustained workloads, the defaults let a lot of bloat accumulate before acting. A 100-million-row table with the default setting doesn't get a VACUUM until it has ~20 million dead tuples. By then, the queries are already degraded.

In this capsule you're going to learn exactly how autovacuum decides when to run, the three parameters to tune (vacuum_scale_factor, analyze_scale_factor, naptime), and why the answer is almost always to adjust per-table, not globally. You're also going to learn to monitor whether autovacuum is keeping up or whether there are tables where it's falling behind.

---

What autovacuum does exactly

Autovacuum runs two operations on each pass:

1. VACUUM: cleans up dead tuples, updates the visibility map, frees space within pages for reuse. It doesn't release physical space to the OS — only VACUUM FULL or pg_repack does that.
2. ANALYZE: updates the statistics the planner consumes (the ones you saw in capsules 02-04).

Autovacuum runs every autovacuum_naptime seconds (default: 60s). On each pass, it decides which tables need VACUUM and which need ANALYZE based on thresholds:

threshold_vacuum = autovacuum_vacuum_threshold
                 + (autovacuum_vacuum_scale_factor × n_live_tup)

threshold_analyze = autovacuum_analyze_threshold
                  + (autovacuum_analyze_scale_factor × n_live_tup)

Defaults:

• autovacuum_vacuum_threshold = 50 (50 dead tuples)
• autovacuum_vacuum_scale_factor = 0.2 (20% of the table)
• autovacuum_analyze_threshold = 50
• autovacuum_analyze_scale_factor = 0.1 (10%)

For a 100-million-row table:

• VACUUM runs when there are 50 + 0.2 × 100M = 20,000,050 dead tuples. 20 million.
• ANALYZE runs when 50 + 0.1 × 100M = 10,000,050 rows have been modified. 10 million.

For a 1,000-row table:

• VACUUM runs when there are 50 + 0.2 × 1000 = 250 dead tuples.
• ANALYZE runs when 50 + 0.1 × 1000 = 150 rows have been modified.

Notice that the behavior is diametrically opposite: on small tables the threshold is low (autovacuum reacts fast); on large tables the threshold is enormous (autovacuum waits a long time to act). It's a bad default for 2026 — modern workloads have large tables with constant changes.

---

The right pattern: per-table tuning

The solution isn't to lower autovacuum_vacuum_scale_factor globally — that would make autovacuum run too much on small tables, spending CPU unnecessarily. The solution is to identify the problematic tables and adjust per-table.

-- Large table with many UPDATEs: aggressive autovacuum
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,    -- 5% instead of 20%
    autovacuum_analyze_scale_factor = 0.02    -- 2% instead of 10%
);

-- Table with DELETE/UPDATE bursts: low absolute threshold
ALTER TABLE event_logs SET (
    autovacuum_vacuum_threshold = 1000,       -- React sooner
    autovacuum_vacuum_scale_factor = 0.05
);

-- Append-only table that only grows (logs, audit): normal or lower autovacuum
-- Nothing to adjust — the default autovacuum is fine here

To check per-table settings:

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

Output:

 relname     | reloptions
-------------+----------------------------------------
 event_logs  | {autovacuum_vacuum_threshold=1000,autovacuum_vacuum_scale_factor=0.05}
 orders      | {autovacuum_vacuum_scale_factor=0.05,autovacuum_analyze_scale_factor=0.02}

To clear settings and go back to defaults:

ALTER TABLE orders RESET (autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor);

---

How to choose which tables to tune

It's not a guessing task. There's a systematic pattern.

Step 1: identify tables with high bloat

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY dead_pct DESC
LIMIT 20;

Filter to the top 20 with the most percentage bloat. These are your candidates for more aggressive autovacuum.

Step 2: identify tables with delayed autovacuum

SELECT
    relname,
    n_dead_tup,
    last_autovacuum,
    EXTRACT(EPOCH FROM (NOW() - last_autovacuum))/3600 AS hours_since_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 50000
  AND (last_autovacuum IS NULL OR last_autovacuum < NOW() - INTERVAL '24 hours')
ORDER BY hours_since_vacuum DESC NULLS FIRST;

Tables with many dead tuples but that haven't been autovacuumed in 24h have delayed autovacuum. It can be for two reasons:

• Threshold not reached (the table doesn't reach the autovacuum_vacuum_scale_factor).
• Autovacuum running but slow (full queues, long naptime).

Step 3: identify the change pattern

SELECT
    relname,
    n_tup_ins,    -- total INSERTs
    n_tup_upd,    -- total UPDATEs
    n_tup_del,    -- total DELETEs
    n_tup_hot_upd,  -- HOT UPDATEs (without updating indexes)
    ROUND(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2) AS hot_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 10000
ORDER BY n_tup_upd DESC
LIMIT 10;

Tables with many UPDATEs and low hot_pct (<50%) are the ones that accumulate the most bloat. Those need aggressive autovacuum.

Step 4: apply per-table adjustments

For an orders table with dead_pct = 35%, a high number of UPDATEs and hot_pct = 30%:

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.05,    -- VACUUM at 5%
    autovacuum_analyze_scale_factor = 0.02,   -- ANALYZE at 2%
    autovacuum_vacuum_cost_delay = 10         -- Ms of pause between pages (default 2ms)
);

autovacuum_vacuum_cost_delay controls how aggressive VACUUM is in terms of I/O. Raising it makes it gentler (less impact on normal queries) but slower. For large tables on decent hardware, 10ms is reasonable.

Step 5: validate after a day

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

If dead_pct dropped to <10% and last_autovacuum is recent, the adjustment worked. If it's still high, the next step is to check whether the table has so many UPDATEs that no autovacuum can keep up — in which case you need to reconsider the UPDATE pattern (capsule 05 covered that case).

---

Autovacuum workers and throughput

Autovacuum runs with several workers in parallel. By default:

• autovacuum_max_workers = 3 (3 simultaneous workers).
• autovacuum_naptime = 60s (check every 60s).

For databases with many active tables (50+), 3 workers can be a bottleneck. Raising it to 5-8 workers is reasonable, especially if you have hardware with available CPU.

-- In postgresql.conf or via ALTER SYSTEM
ALTER SYSTEM SET autovacuum_max_workers = 6;
ALTER SYSTEM SET autovacuum_naptime = 30;  -- Check every 30s instead of 60s
SELECT pg_reload_conf();

Check active workers at this moment:

SELECT
    pid,
    datname,
    relname,
    state,
    query
FROM pg_stat_activity
JOIN pg_class ON relname = (regexp_match(query, 'autovacuum: (?:VACUUM ANALYZE|VACUUM|ANALYZE) public\.(\w+)'))[1]
WHERE backend_type = 'autovacuum worker';

(This query is simplified. Reality is messier parsing the query text — but it gives the principle.)

---

The "anti-wraparound vacuum" problem

PostgreSQL uses a 32-bit transaction counter (XID). If a VACUUM is never done, eventually the counter runs out of values and the database can't accept more transactions — it "wraps around" (wraparound). To prevent this, autovacuum has an emergency mode: anti-wraparound vacuum.

When a table approaches wraparound, autovacuum locks it and does a VACUUM in aggressive mode, ignoring other parameters. This can take hours on large tables and blocks DDL operations during that time. In production, seeing "anti-wraparound vacuum running" is a sign that your normal autovacuum was behind for a long time.

To detect tables in danger:

SELECT
    relname,
    age(relfrozenxid) AS xid_age,
    pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
WHERE relkind = 'r'
  AND age(relfrozenxid) > 100000000  -- 100M XIDs without freeze
ORDER BY age(relfrozenxid) DESC;

autovacuum_freeze_max_age = 200000000 (default 200M XIDs) is when it triggers anti-wraparound. If you see tables near that number, it's an alert.

To avoid this scenario on large tables, adjust:

ALTER TABLE huge_table SET (autovacuum_freeze_max_age = 100000000);

This makes the anti-wraparound run sooner (while there's still margin) instead of in an emergency.

---

Traps and common mistakes

1. Lowering autovacuum_vacuum_scale_factor globally.

This makes autovacuum run constantly over all tables, spending CPU/I/O. Per-table is the right answer — only where you really need it.

2. Disabling autovacuum to "manage it manually with scripts".

Terrible idea. Any real app has hundreds of tables with different patterns. Managing it manually means forgetting tables, generating wraparound, and having progressively degraded databases. Autovacuum works — it just needs tuning.

-- ❌ DON'T DO THIS
ALTER TABLE orders SET (autovacuum_enabled = false);

The only legitimate exception is disabling autovacuum temporarily during massive bulk loads where you know you're going to run a manual ANALYZE at the end. And re-enabling it afterward.

3. Not monitoring when autovacuum runs vs when it should run.

If you never check last_autovacuum and n_dead_tup, you don't know whether autovacuum is keeping up. Having a dashboard (Grafana, Datadog) with those metrics is basic operational discipline.

4. Thinking autovacuum handles indexes automatically.

Autovacuum runs VACUUM, which cleans up the dead pointers in indexes, but it doesn't rebuild fragmented indexes. For that you need REINDEX CONCURRENTLY (manual or via a monthly cron). Bloat in indexes doesn't cure itself.

5. Forgetting that ANALYZE is part of autovacuum.

Autovacuum does VACUUM AND ANALYZE. If you tune autovacuum_vacuum_scale_factor aggressively but forget autovacuum_analyze_scale_factor, you end up with old stats even though the table is clean. Tune both together.

6. Assuming more workers = faster.

Raising autovacuum_max_workers helps only if you have many tables that need vacuum simultaneously. If you only have one problem table, five workers aren't going to process it faster (PostgreSQL doesn't parallelize VACUUM within a table on versions <13). See autovacuum_vacuum_cost_limit to speed up the individual VACUUM.

7. Tuning without measuring the impact.

After any autovacuum change, monitor for at least 24-48h. Sometimes "more aggressive" improves bloat but degrades other metrics (query latency during the VACUUM). The right balance requires observation.

---

Exercise: identify and tune problematic tables

You're going to simulate a table with accumulated bloat and apply systematic tuning.

Setup:

DROP TABLE IF EXISTS user_sessions;
CREATE TABLE user_sessions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    last_seen TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    metadata JSONB
);

CREATE INDEX idx_sessions_user ON user_sessions(user_id);
CREATE INDEX idx_sessions_last_seen ON user_sessions(last_seen);

-- Insert 100k sessions
INSERT INTO user_sessions (user_id, metadata)
SELECT
    (random() * 1000)::INT + 1,
    jsonb_build_object('ua', 'Mozilla/5.0', 'ip', '192.168.1.' || (random() * 255)::INT)
FROM generate_series(1, 100000);

ANALYZE user_sessions;

Step 1: simulate frequent updates (typical of activity tracking).

-- 10 rounds of UPDATE — simulating "user activity tracking" that runs often
DO $$
BEGIN
    FOR i IN 1..10 LOOP
        UPDATE user_sessions SET last_seen = NOW() WHERE user_id IN (
            SELECT (random() * 1000)::INT + 1 FROM generate_series(1, 50)
        );
    END LOOP;
END $$;

Step 2: measure bloat.

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    n_tup_upd,
    n_tup_hot_upd,
    ROUND(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2) AS hot_pct
FROM pg_stat_user_tables
WHERE relname = 'user_sessions';

Question: what percentage are HOT updates? Why aren't they 100%?

Step 3: simulate massive load over several hours (we're going to force the scenario).

-- Repeat the UPDATE block many more times
DO $$
BEGIN
    FOR i IN 1..100 LOOP
        UPDATE user_sessions SET last_seen = NOW() WHERE id BETWEEN
            (random() * 99000)::INT + 1 AND (random() * 99000)::INT + 100;
    END LOOP;
END $$;

SELECT relname, n_live_tup, n_dead_tup,
       ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables WHERE relname = 'user_sessions';

Question: did we reach dead_pct >20%? With the default autovacuum_vacuum_scale_factor = 0.2, should autovacuum have run?

Step 4: check whether autovacuum ran.

SELECT relname, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables WHERE relname = 'user_sessions';

If in a local demo session it didn't run, it's because autovacuum_naptime hasn't passed yet. In production it would have run (eventually). But you can force a manual VACUUM:

VACUUM user_sessions;

Step 5: apply per-table tuning and justify it.

ALTER TABLE user_sessions SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02
);

-- Verify
SELECT relname, reloptions FROM pg_class WHERE relname = 'user_sessions';

Question: why is 0.05 reasonable for this specific table? Why not 0.02 even more aggressive?

See solution

Step 2 — HOT updates:

hot_pct would typically be 0% or very low in this setup. Why: we update last_seen, which is indexed (idx_sessions_last_seen). When an indexed column is updated, PostgreSQL can't use HOT — it has to update the index. If we removed the index or only updated non-indexed columns, hot_pct would be ~95%.

Step 3 — dead_pct:

After many UPDATEs on the same rows, dead_pct goes from 0% to 50%+. Each UPDATE creates a dead tuple. With the default 0.2, autovacuum eventually runs and cleans up, but in a local demo it depends on naptime.

Step 5 — justification for 0.05:

This table:

• Has very frequent UPDATEs (activity tracking).
• Each UPDATE creates a dead tuple (because there's an index on the updated column).
• Bloat accumulates fast if you wait for 20%.

With 0.05, autovacuum runs when there are 5,000 dead tuples (in 100k rows) — more timely. With 0.02 it runs when there are 2,000 — too aggressive, you spend CPU unnecessarily. 0.05 is the sweet spot.

General lessons:

• Tables with frequent updates on indexed columns → aggressive per-table autovacuum.
• Each extra index is a cost on updates → consider whether you need all the indexes.
• The "tracking column" pattern (last_seen, last_login, view_count) is the main bloat producer → consider alternative designs (Redis, a separate table).

---

Summary and next step

What you learned:

• Autovacuum runs VACUUM and ANALYZE automatically, but the defaults (scale_factor = 0.2 for vacuum, 0.1 for analyze) are bad for large tables.
• The right strategy is per-table tuning, not global. ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.05).
• Identify problematic tables with pg_stat_user_tables: dead_pct > 20%, hot_pct < 50%, high n_tup_upd.
• Tables with frequent UPDATEs on indexed columns are the main candidates for aggressive autovacuum.
• Anti-wraparound vacuum is the emergency mode. Seeing it in production is a sign of delayed maintenance.
• Raising autovacuum_max_workers helps with many tables; autovacuum_vacuum_cost_delay controls I/O aggressiveness.

Before moving on, you should be able to:

• Calculate which dead_pct triggers autovacuum given scale_factor and n_live_tup.
• Identify the top 5 problematic tables in a database with pg_stat_user_tables.
• Apply ALTER TABLE ... SET with justification (not copying numbers from blogs).
• Detect tables at risk of wraparound with age(relfrozenxid).

In the next capsule we go to the "it's already too late" case: you have a table with 60% accumulated bloat, autovacuum isn't going to recover the physical space (it only marks it reusable), and you need to give the space back to the operating system. The default option is VACUUM FULL — but it locks the table for hours. The right option for production is pg_repack, which does the same work online. You're going to learn when to use each one and how to run pg_repack safely.

---

Resources

1. PostgreSQL Docs — Autovacuum — reference for all the parameters.
2. PostgreSQL Docs — Routine Vacuuming — deep explanation of maintenance.
3. pganalyze — Autovacuum tuning — modern operational checks.
4. Crunchy Data — Autovacuum Tuning Basics — a practical guide with cases.
5. depesz — Why is my autovacuum not running? — diagnosis of delayed autovacuum.
6. Tomas Vondra — VACUUM internals — deep talks.
7. Robert Haas — Anti-wraparound vacuum — posts on wraparound and critical maintenance.

---

Capsule 06 of 08 — Module 7 — Database Performance & Query Tuning Guide