Ubuntu 24.04 PostgreSQL autovacuum “mystery slowness”: how to tune it safely

October 21, 2025 • February 3, 2026 • Read: 26 min • Views: 10

Was this helpful?

Autovacuum is supposed to be the quiet janitor. On Ubuntu 24.04, it sometimes shows up like a marching band: latency spikes, “idle in transaction” sessions pile up, disks look busy but nothing is “using CPU,” and your app team swears they changed nothing.

The trap is assuming autovacuum is either “fine” or “the problem.” In practice it’s a symptom amplifier. It turns existing bloat, bad query patterns, and I/O ceilings into very visible pain. The good news: you can make it predictable without turning your database into a science experiment.

What “mystery slowness” looks like in production

Autovacuum slowness rarely presents as “vacuum takes long.” It presents as everything else taking long.

Read queries that used to be fast start doing more random I/O. You see shared read climb, cache hit ratio drops, and your p99 falls off a cliff.
Write-heavy tables show heap bloat and index bloat. The working set no longer fits in RAM. Your SSD starts acting like it’s spinning rust.
Autovacuum workers appear “active” but throughput is low. Wait events show I/O or locks, and the vacuum progress view looks like it’s stuck.
Occasional “cannot vacuum … because it contains tuples still needed”-style behavior: not an error, but a sign of long-running transactions pinning cleanup.
And the classic: wraparound pressure sneaks up. Suddenly vacuum stops being optional and starts being existential.

One dry truth: autovacuum isn’t slow because it’s lazy. It’s slow because it’s polite—unless you force it not to be.

Fast diagnosis playbook (do this first)

This is the “I need signal in 10 minutes” sequence. Do it in order. Each step narrows the bottleneck category before you touch any knobs.

1) Is the pain I/O, locks, or wraparound?

If system I/O is pegged and Postgres wait events show I/O waits → you’re mostly storage-bound.
If autovacuum is blocked on locks or cannot advance because of long transactions → you’re mostly transaction/locking-bound.
If datfrozenxid age is high → you’re in wraparound-prevention mode. Performance is now secondary to survival.

2) Identify the hottest tables, not just “autovacuum”

Autovacuum is table-by-table. One 200 GB table with a bad update pattern can create the illusion that “the whole database is slow.” Find the table.

3) Verify whether autovacuum is under-provisioned or intentionally throttled

Workers, cost delay, and cost limit determine how hard vacuum pushes. On many production systems, defaults are safe but too gentle for modern write rates.

4) Validate the “pinning” suspects

Long-running transactions, logical replication slots, and abandoned sessions can prevent cleanup. If you don’t fix those, tuning autovacuum is like buying a faster mop for a floor that’s still flooding.

Joke #1: Autovacuum is the only employee who cleans up everyone else’s mess and still gets blamed for the smell.

How autovacuum actually spends time

Vacuum is not one job. It’s a bundle of jobs that compete with your workload:

Heap scan: reading table pages to find dead tuples and mark space reusable.
Index cleanup: removing dead index entries (unless bypassed by index vacuuming choices and heuristics).
Freezing: marking old transaction IDs as frozen to prevent wraparound.
Visibility map updates: enabling index-only scans by recording all-visible/all-frozen pages.
ANALYZE: updating planner stats (autovacuum often runs analyze as well).

“Mystery slowness” often happens when the work flips from incremental cleanup to catch-up cleanup. Once bloat grows, vacuum reads and writes more pages, touches indexes more, and creates more random I/O. That increases latency for everything else, which increases transaction duration, which increases dead tuples… and the flywheel spins.

What makes vacuum slow even when it’s “running”

Cost-based delay: vacuum intentionally sleeps. This is normal behavior.
I/O saturation: vacuum is doing real reads/writes but storage is at its limit.
Buffer cache churn: vacuum displaces hot pages, making application reads miss cache more often.
Lock conflicts: regular VACUUM doesn’t take heavy locks, but it still needs a lock mode that can be blocked by some DDL and by certain edge cases. VACUUM FULL is a different beast and will absolutely ruin your afternoon.
Long-running transactions: vacuum cannot remove tuples still visible to old snapshots.

Ubuntu 24.04 specifics that matter

Ubuntu 24.04 is not inherently “worse” for Postgres, but it’s a modern Linux baseline with modern defaults—some helpful, some surprising.

Kernel and I/O stack: you’re typically on a 6.x kernel. The I/O scheduler defaults and NVMe behavior are usually good, but misconfigured cgroups or noisy neighbors can still starve Postgres.
Systemd: services may run with resource controls you didn’t set intentionally. CPUQuota/IOWeight can produce “vacuum is slow but nothing is maxed” confusion.
Transparent Huge Pages (THP): often still enabled by default on general-purpose systems. It can cause latency hiccups. It’s not an autovacuum setting, but it can make autovacuum look guilty.
Filesystems and mount options: ext4 vs XFS vs ZFS have different patterns under vacuum’s mixed read/write workload. Autovacuum isn’t special—it just touches lots of pages.

Interesting facts and context (because history repeats)

These are small, concrete bits that help you reason about autovacuum without superstition.

Autovacuum became standard in PostgreSQL 8.1 after being an add-on. Before that, many systems simply rotted unless humans ran vacuum routinely.
MVCC is the reason vacuum exists: Postgres keeps old row versions for concurrency. Cleanup is deferred by design.
Vacuum doesn’t “shrink” tables in the general case. It makes space reusable inside the file; the file size often stays the same unless you use more invasive methods.
Wraparound is not theoretical: transaction IDs are 32-bit and will eventually wrap. If you don’t vacuum/freeze, Postgres will protect itself by forcing aggressive vacuums and eventually refusing writes.
Hot updates can reduce index churn, but only if updated columns aren’t indexed. Update the wrong column and you’ll bloat indexes rapidly.
Visibility maps enable index-only scans. Vacuum that updates visibility maps can speed up reads later, even if it costs I/O now.
Cost-based vacuum delay exists to protect latency, not to annoy you. It was designed when disks were slower and shared environments were common.
Autovacuum scale factors were meant for “normal” tables. Very large tables with high churn often need per-table overrides; defaults scale too linearly for some workloads.
ANALYZE accuracy affects vacuum impact indirectly: bad stats lead to bad plans, which create longer transactions, which create more dead tuples and more vacuum work.

Practical tasks: commands, outputs, and decisions (12+)

These are the field checks I actually run. Each task includes: command, example output, what it means, and what decision you make.

Task 1: Confirm Postgres version and cluster layout

cr0x@server:~$ psql --version
psql (PostgreSQL) 16.3 (Ubuntu 16.3-1.pgdg24.04+1)

Meaning: You’re on PG 16.x client tools. On Ubuntu, you might have multiple clusters/versions.

Decision: Ensure you’re tuning the right instance and editing the right config file path for that cluster.

cr0x@server:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

Meaning: Single cluster, PG16 main, standard Debian/Ubuntu packaging paths.

Decision: You know where to look for logs and which port to target.

Task 2: Is autovacuum even enabled, and what are the global knobs?

cr0x@server:~$ sudo -u postgres psql -X -c "SHOW autovacuum; SHOW autovacuum_max_workers; SHOW autovacuum_naptime; SHOW autovacuum_vacuum_cost_limit; SHOW autovacuum_vacuum_cost_delay;"
 autovacuum
------------
 on
(1 row)

 autovacuum_max_workers
-----------------------
 3
(1 row)

 autovacuum_naptime
-------------------
 1min
(1 row)

 autovacuum_vacuum_cost_limit
-----------------------------
 -1
(1 row)

 autovacuum_vacuum_cost_delay
-----------------------------
 2ms
(1 row)

Meaning: Autovacuum is on, only 3 workers, 1-minute nap between checks. Cost limit -1 means “use vacuum_cost_limit.”

Decision: If you have many active tables and bloat, 3 workers is often too low. But don’t increase blindly—first find whether you’re blocked or I/O-limited.

Task 3: Find active autovacuum workers and what they’re waiting on

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT pid, datname, relid::regclass AS table, phase, wait_event_type, wait_event, now()-xact_start AS xact_age \
FROM pg_stat_progress_vacuum v \
JOIN pg_stat_activity a USING (pid) \
ORDER BY xact_age DESC;"
 pid  | datname |        table        |   phase   | wait_event_type | wait_event | xact_age
------+--------+---------------------+-----------+-----------------+------------+----------
 8421 | appdb  | public.events       | scanning  | IO              | DataFileRead | 00:12:41
 8534 | appdb  | public.sessions     | vacuuming indexes | CPU      |            | 00:05:10
(2 rows)

Meaning: One worker is I/O waiting on reads; another is spending CPU in index vacuuming.

Decision: If you see a lot of IO waits, tuning cost limits may just increase contention. Consider storage throughput and cache first.

Task 4: Check for blocked vacuum due to locks

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT a.pid, a.wait_event_type, a.wait_event, a.query, l.relation::regclass AS rel, l.mode, l.granted \
FROM pg_stat_activity a \
JOIN pg_locks l ON l.pid=a.pid \
WHERE a.query ILIKE '%autovacuum%' AND NOT l.granted;"
 pid | wait_event_type |  wait_event   |                 query                 |      rel       |      mode       | granted
-----+-----------------+---------------+---------------------------------------+----------------+-----------------+---------
(0 rows)

Meaning: No evidence the autovacuum workers are waiting on ungranted locks right now.

Decision: Don’t chase lock ghosts. Move to transaction pinning and I/O.

Task 5: Check for long-running transactions that pin vacuum

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT pid, usename, application_name, state, now()-xact_start AS xact_age, wait_event_type, wait_event \
FROM pg_stat_activity \
WHERE xact_start IS NOT NULL \
ORDER BY xact_age DESC \
LIMIT 10;"
 pid  | usename | application_name |        state        | xact_age  | wait_event_type | wait_event
------+--------+------------------+---------------------+-----------+-----------------+-----------
 7712 | app    | api              | idle in transaction | 03:17:09  | Client          | ClientRead
 9120 | app    | batch            | active              | 00:42:11  |                 |
(2 rows)

Meaning: An “idle in transaction” session has held a snapshot for hours. Vacuum cannot remove dead tuples that are still visible to it.

Decision: Fix the app bug (missing commit/rollback). In the short term, terminate that session if safe; otherwise autovacuum tuning won’t help.

Task 6: Check wraparound risk (you care about this even when performance is “fine”)

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT datname, age(datfrozenxid) AS xid_age, age(datminmxid) AS mxid_age \
FROM pg_database \
ORDER BY xid_age DESC;"
  datname  | xid_age  | mxid_age
-----------+----------+----------
 appdb     | 145000000 | 1800000
 postgres  |   2300000 |  120000
 template1 |   2300000 |  120000
 template0 |   2300000 |  120000
(4 rows)

Meaning: appdb is far ahead in XID age; that’s where vacuum/freeze must keep up.

Decision: If xid_age is approaching your autovacuum_freeze_max_age safety margin, prioritize freeze progress over “nice” throttling.

Task 7: Identify tables with the most dead tuples and vacuum urgency

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT relid::regclass AS table, n_live_tup, n_dead_tup, last_autovacuum, last_vacuum, vacuum_count, autovacuum_count \
FROM pg_stat_user_tables \
ORDER BY n_dead_tup DESC \
LIMIT 15;"
        table        | n_live_tup | n_dead_tup |      last_autovacuum       | last_vacuum | vacuum_count | autovacuum_count
---------------------+------------+------------+----------------------------+-------------+--------------+------------------
 public.events       | 120000000  | 48000000   | 2025-12-30 08:10:01+00     |             |            0 |               91
 public.sessions     |  90000000  | 22000000   | 2025-12-30 08:20:14+00     |             |            0 |              143
(2 rows)

Meaning: Massive dead tuple counts; autovacuum is running frequently but not winning. That’s classic catch-up failure.

Decision: Consider per-table autovacuum thresholds and more aggressive vacuum settings, plus query/app fixes.

Task 8: Check if the table is “vacuumed a lot” but still bloated (approximate bloat clues)

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT schemaname, relname, n_live_tup, n_dead_tup, \
pg_size_pretty(pg_total_relation_size(relid)) AS total_size, \
pg_size_pretty(pg_relation_size(relid)) AS heap_size, \
pg_size_pretty(pg_indexes_size(relid)) AS index_size \
FROM pg_stat_user_tables \
ORDER BY pg_total_relation_size(relid) DESC \
LIMIT 10;"
 schemaname |  relname  | n_live_tup | n_dead_tup | total_size | heap_size | index_size
------------+-----------+------------+------------+------------+-----------+-----------
 public     | events    | 120000000  | 48000000   | 180 GB     | 120 GB    | 60 GB
 public     | sessions  |  90000000  | 22000000   | 110 GB     | 70 GB     | 40 GB
(2 rows)

Meaning: Big heap and big indexes; dead tuple ratio is high. Vacuum is likely creating heavy I/O.

Decision: If the table is update-heavy, assess HOT update feasibility and index design. Tuning autovacuum alone is not enough.

Task 9: See if autovacuum is being throttled (cost delay in effect)

cr0x@server:~$ sudo -u postgres psql -X -c "SHOW vacuum_cost_limit; SHOW vacuum_cost_delay;"
 vacuum_cost_limit
-------------------
 200
(1 row)

 vacuum_cost_delay
-------------------
 0
(1 row)

Meaning: Global vacuum cost delay is 0, but autovacuum has its own delay (often 2ms). The effective behavior depends on the autovacuum settings you saw earlier.

Decision: If you are I/O-bound, decreasing delays may hurt latency. If you’re behind on cleanup, you may need to increase cost limit and/or reduce delay carefully.

Task 10: System-level I/O reality check (is the disk the limiter?)

cr0x@server:~$ iostat -x 1 5
Linux 6.8.0-41-generic (server)  12/30/2025  _x86_64_ (16 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.15    0.00    4.33   18.90    0.00   64.62

Device            r/s     rkB/s   rrqm/s  %rrqm r_await rareq-sz     w/s     wkB/s   w_await wareq-sz  aqu-sz  %util
nvme0n1         820.0  52480.0     0.0   0.00    9.20    64.0     610.0  48800.0   12.10    80.0    18.4   99.0

Meaning: NVMe is at ~99% utilization with high await. You’re storage-saturated. Vacuum “slow” is basically “physics.”

Decision: You can still tune autovacuum, but the bigger fix is reducing write amplification, increasing RAM/cache hit ratio, improving storage throughput, or spreading I/O.

Task 11: Check filesystem mount options quickly

cr0x@server:~$ findmnt -no TARGET,SOURCE,FSTYPE,OPTIONS /var/lib/postgresql
/var/lib/postgresql /dev/mapper/vg0-pgdata ext4 rw,relatime,errors=remount-ro

Meaning: ext4 with relatime. Nothing obviously exotic.

Decision: If you see network filesystems or odd sync options, stop and reassess. Vacuum hates slow fsyncs.

Task 12: Inspect autovacuum logging to get ground truth timing

cr0x@server:~$ sudo -u postgres psql -X -c "SHOW log_autovacuum_min_duration;"
 log_autovacuum_min_duration
----------------------------
 -1
(1 row)

Meaning: Autovacuum isn’t logging durations, so you’re debugging blind.

Decision: Set log_autovacuum_min_duration = '30s' (or even 0 temporarily during an incident) to capture what’s actually taking time.

cr0x@server:~$ sudo tail -n 5 /var/log/postgresql/postgresql-16-main.log
2025-12-30 08:20:14.902 UTC [8534] LOG:  automatic vacuum of table "appdb.public.sessions": index scans: 1
2025-12-30 08:20:14.902 UTC [8534] DETAIL:  pages: 0 removed, 842113 remain, 500000 skipped due to pins, 0 skipped frozen
2025-12-30 08:20:14.902 UTC [8534] DETAIL:  tuples: 0 removed, 0 remain, 0 are dead but not yet removable
2025-12-30 08:20:14.902 UTC [8534] DETAIL:  buffer usage: 21000 hits, 180000 misses, 40000 dirtied
2025-12-30 08:20:14.902 UTC [8534] DETAIL:  avg read rate: 45.0 MB/s, avg write rate: 10.0 MB/s, I/O timings: read 220000.000 ms, write 90000.000 ms

Meaning: “Skipped due to pins” is the smoking gun: something is holding snapshots/tuples. Also note the I/O timings—reads are slow and dominating.

Decision: Fix pinning first (long transactions, replication slots), then consider I/O throughput and cost tuning.

Task 13: Check replication slots (common vacuum pin)

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn \
FROM pg_replication_slots;"
   slot_name   | slot_type | active | restart_lsn | confirmed_flush_lsn
---------------+-----------+--------+-------------+---------------------
 analytics_cdc | logical   | f      | 2A/90000000 | 2A/100000000
(1 row)

Meaning: An inactive logical slot can retain WAL. That’s not the same as freezing tuples, but it can create disk pressure and prolonged recovery time after restarts. It also often correlates with long transactions in CDC consumers.

Decision: If the slot is abandoned, drop it. If it’s needed, fix the consumer so it advances.

Task 14: Check for temp file storms and query plan issues (the “vacuum is blamed” classic)

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp \
FROM pg_stat_database \
ORDER BY temp_bytes DESC;"
 datname | temp_files |  temp
---------+------------+--------
 appdb   |      18234 | 96 GB
(1 row)

Meaning: Big temp usage. Your disks are busy, but not necessarily because of vacuum.

Decision: If temp usage aligns with slow periods, tune work_mem per query, fix sorts/joins, and reduce temp churn. Otherwise vacuum tuning won’t move the needle.

Safe tuning strategy: what to change, and in what order

Autovacuum tuning is a game of trade-offs: throughput vs latency, background maintenance vs foreground queries. The safe approach is to raise capacity with guardrails and target the worst tables, rather than globally cranking everything.

One paraphrased idea from Werner Vogels (Amazon CTO): Everything fails eventually; design and operate as if failure is normal, not exceptional.

Principle 1: Treat “can’t keep up” as a structural problem

If dead tuples grow faster than vacuum can remove them, you have one of these:

Too few workers / too much throttling
I/O ceiling (storage, cache, or noisy neighbors)
Pinning (long transactions, idle in transaction, replication/slots)
Write pattern that creates bloat faster than any reasonable vacuum can handle (certain UPDATE-heavy patterns, missing HOT opportunities, bloated indexes)

Tuning autovacuum only solves the first item. The others require operational and schema/app changes.

Principle 2: Prefer per-table settings for large or pathological tables

Global settings are blunt instruments. Large tables with high churn are where defaults fail. Per-table storage parameters let you be aggressive where it matters and gentle everywhere else.

Example: if public.events is huge and constantly updated, you can lower the scale factor so autovacuum triggers earlier, and potentially raise cost limit for that table only.

cr0x@server:~$ sudo -u postgres psql -X -c "\
ALTER TABLE public.events SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 50000);"
ALTER TABLE

Meaning: Vacuum triggers after ~1% of table changes + a base threshold. That’s earlier than defaults and helps prevent runaway bloat.

Decision: Use this when you see “autovacuum runs often but always late.” You want it to run earlier and do less work each time.

Principle 3: Increase workers carefully; it’s easy to DOS yourself

autovacuum_max_workers is tempting. More workers can improve throughput, but it multiplies I/O and buffer churn. On fast storage with adequate RAM, it can be a win. On saturated storage, it can turn a slow system into a collapsed one.

Baseline guidance I’m comfortable with for many production systems:

Start by moving from 3 to 5 workers on moderate systems if you have many large tables.
Go higher only with evidence that storage has headroom and wait events aren’t dominated by I/O.
Cap it if you share disks with other services or have strict latency SLOs.

cr0x@server:~$ sudo -u postgres psql -X -c "ALTER SYSTEM SET autovacuum_max_workers = 5;"
ALTER SYSTEM

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT pg_reload_conf();"
 pg_reload_conf
----------------
 t
(1 row)

Meaning: Increased worker count; config reloaded.

Decision: Watch I/O (iostat), latency, and progress views for 30–60 minutes. If p99 worsens sharply, revert or raise cost delays.

Principle 4: Use cost tuning to shape impact, not to “speed vacuum” blindly

The vacuum cost model tries to limit how much I/O vacuum does by forcing sleeps. The two knobs that matter most:

autovacuum_vacuum_cost_limit: how much work before sleeping
autovacuum_vacuum_cost_delay: how long to sleep

If vacuum is falling behind and you have I/O headroom, raise the cost limit and/or reduce the delay. If vacuum is causing query latency, do the opposite.

A conservative “first move” on modern SSD-backed systems is usually:

Increase autovacuum_vacuum_cost_limit moderately (e.g., 200 → 1000)
Keep delay small but nonzero (e.g., 2ms is fine; 0ms can be aggressive)

cr0x@server:~$ sudo -u postgres psql -X -c "ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 1000;"
ALTER SYSTEM

cr0x@server:~$ sudo -u postgres psql -X -c "ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';"
ALTER SYSTEM

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT pg_reload_conf();"
 pg_reload_conf
----------------
 t
(1 row)

Meaning: Autovacuum can do more work per unit time, but still yields periodically.

Decision: If you’re I/O-saturated already, don’t do this globally. Prefer per-table tuning or fix the underlying I/O limit.

Principle 5: Tune freeze behavior only when you understand your XID age profile

There are two common failure modes:

Too timid: freeze work doesn’t keep up, you approach wraparound and vacuum becomes urgent and disruptive.
Too aggressive: you force heavy freezing too often on large tables, increasing write amplification.

Make decisions based on measured age(relfrozenxid) for your biggest tables and the database-level age.

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT c.oid::regclass AS table, age(c.relfrozenxid) AS xid_age \
FROM pg_class c \
JOIN pg_namespace n ON n.oid=c.relnamespace \
WHERE n.nspname='public' AND c.relkind='r' \
ORDER BY xid_age DESC \
LIMIT 10;"
       table       | xid_age
-------------------+----------
 public.events      | 142000000
 public.sessions    | 120000000
(2 rows)

Meaning: These tables are driving XID age. Freeze work has to happen here.

Decision: Consider per-table autovacuum_freeze_max_age or more aggressive vacuum cadence for these tables—after addressing pinning.

Principle 6: Remember the hidden partner: analyze

Autovacuum often also runs analyze. If stats are stale, query plans get weird, which changes write patterns and transaction duration. That feeds back into vacuum.

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT relid::regclass AS table, last_analyze, last_autoanalyze, n_mod_since_analyze \
FROM pg_stat_user_tables \
ORDER BY n_mod_since_analyze DESC \
LIMIT 10;"
      table       | last_analyze |      last_autoanalyze      | n_mod_since_analyze
------------------+--------------+----------------------------+--------------------
 public.events     |              | 2025-12-29 22:10:01+00     |            24000000
 public.sessions   |              | 2025-12-30 00:05:44+00     |            11000000
(2 rows)

Meaning: Massive modifications since analyze; stats may be lagging behind churn.

Decision: Lower analyze scale factor per-table on high-churn tables. This can stabilize plans and reduce transaction duration spikes.

Principle 7: Don’t tune autovacuum in a vacuum (yes, I said it)

Here’s where storage engineering meets Postgres reality:

Vacuum reads lots of pages. If your working set already barely fits RAM, vacuum will evict hot pages and amplify misses.
Vacuum writes (visibility map updates, freezing, index cleanup). That’s fsync pressure and WAL pressure.
On cloud volumes, IOPS/throughput limits can make “random I/O heavy” workloads collapse in dramatic ways.

So: verify I/O limits and memory sizing before you “fix autovacuum.”

Three corporate mini-stories (anonymized)

Mini-story 1: Incident caused by a wrong assumption

The company ran a subscription service with a busy Postgres cluster. After migrating to Ubuntu 24.04 and a newer Postgres minor version, they saw nightly latency spikes. The on-call conclusion was immediate: “New OS, new kernel, autovacuum got slower.”

They raised autovacuum_max_workers and lowered cost delay to zero across the board. The spikes turned into a sustained plateau of misery. CPU looked fine, but disks were pinned. The dashboard said “vacuum running constantly,” which only strengthened the narrative that vacuum was the villain.

Then someone finally looked at pg_stat_activity and saw a connection from a legacy batch tool sitting idle in transaction for hours. It had been “fine” before because the dataset was smaller. After growth, the same bug became a vacuum pin.

Killing that session made vacuum immediately start removing dead tuples instead of skipping pinned pages. The next night’s spike was gone without any of the aggressive tuning.

The wrong assumption wasn’t technical—it was sociological: blaming the recent change because it was recent. The fix was boring: find the pin, fix the client, then re-evaluate vacuum settings based on real throughput needs.

Mini-story 2: An optimization that backfired

A fintech team had an update-heavy ledger-like table. They noticed bloat and decided to “help” autovacuum by setting extremely aggressive thresholds and a high cost limit—globally. Their thinking: if vacuum runs constantly, bloat can’t accumulate.

What they got was a quiet disaster. Autovacuum began churning through large indexes during peak hours, trashing the cache. Read-heavy API endpoints started missing cache and hitting storage. Latency climbed, timeouts increased, and application retries created even more writes.

The on-call tried to fix it by adding more autovacuum workers. That multiplied I/O and made the cache churn worse. The system wasn’t under-vacuumed; it was under-provisioned for the new level of background work competing with foreground traffic.

The eventual fix was per-table targeting: aggressive vacuum only on the hot table, and only during defined low-traffic windows using manual vacuum scheduling for the worst bloat periods. They also adjusted indexes to improve HOT update rates, which reduced the amount of index vacuuming required in the first place.

The lesson: you can absolutely tune autovacuum into a load generator. Postgres will let you. It’s polite like that.

Mini-story 3: A boring but correct practice that saved the day

A SaaS provider ran multi-tenant Postgres with strict uptime requirements. They had a ritual: every week, a small script collected vacuum duration logs, top dead-tuple tables, XID age trends, and I/O utilization snapshots. Nothing fancy. Just consistent.

One week, the script flagged that age(datfrozenxid) was climbing faster than usual on a single tenant database. No incident yet, no alerts firing. Just a trend that looked wrong.

They investigated and found a long-lived replication slot from an analytics pipeline that had been disabled but not removed. WAL retention was inflating disk usage, checkpoints were getting heavier, and autovacuum was fighting more I/O contention than it used to. Vacuum wasn’t “broken”; it was being outcompeted.

They removed the abandoned slot, WAL pressure dropped, and vacuum returned to predictable behavior. Nobody noticed externally. The best kind of incident is the one you never have to explain.

Yes, it was boring. That’s why it worked.

Common mistakes: symptom → root cause → fix

1) Symptom: autovacuum “runs all the time” and dead tuples still climb

Root cause: vacuum is pinned by long transactions or “idle in transaction” sessions, or it’s repeatedly scanning but skipping pages due to pins.

Fix: Find and eliminate long snapshots. Enforce statement timeouts and idle-in-transaction timeouts. Confirm pins via autovacuum logs (“skipped due to pins”) and pg_stat_activity. Tuning workers won’t help until pins are gone.

2) Symptom: vacuum appears slow; `pg_stat_progress_vacuum` phase is “scanning” forever

Root cause: I/O-limited heap scan on a bloated table, often with cache misses and saturated storage.

Fix: Reduce bloat causes (update patterns, indexes), ensure storage throughput, consider raising cost limit only if storage has headroom, and tune per-table thresholds to vacuum earlier.

3) Symptom: latency spikes correlate with autovacuum starting

Root cause: vacuum is evicting hot pages and increasing random I/O; too many workers or too aggressive cost settings during peak.

Fix: Cap autovacuum impact: increase cost delay, lower cost limit, reduce worker count, and prefer per-table aggressiveness only where needed. Consider scheduling manual vacuum during off-peak for pathological tables.

4) Symptom: sudden emergency vacuum behavior, warnings about wraparound

Root cause: freeze work fell behind; you’re near autovacuum_freeze_max_age. Often caused by disabled autovacuum, pinning, or too-large tables with default thresholds.

Fix: Treat as priority 0. Remove pinning sessions. Temporarily increase vacuum resources and run targeted manual vacuums. Afterwards, tune freeze settings and thresholds so you never get near the cliff again.

5) Symptom: “vacuum causes high WAL and replication lag”

Root cause: heavy freezing and visibility map updates can generate WAL; aggressive vacuum settings amplify. Also checkpoints may be stressed.

Fix: Smooth maintenance: avoid vacuum storms by vacuuming earlier and smaller; tune checkpoint parameters; ensure replica I/O keeps up; use per-table settings rather than global aggression.

6) Symptom: vacuum is blocked by locks occasionally

Root cause: concurrent DDL, or operations that take stronger locks (like VACUUM FULL, REINDEX without CONCURRENTLY) interfering.

Fix: Stop doing heavy DDL in peak. Use concurrent variants where possible. Reserve VACUUM FULL for planned maintenance windows and only when you’ve proven it’s necessary.

Joke #2: VACUUM FULL is like moving apartments to find the TV remote—effective, but your weekend is gone.

Checklists / step-by-step plan

Step-by-step: from incident to stable tuning

Freeze safety check: query age(datfrozenxid) and top tables by age(relfrozenxid). If you’re close to wraparound thresholds, stop optimizing and start preventing downtime.
Pinning check: find long transactions and “idle in transaction.” Fix or terminate offenders, and set timeouts to prevent recurrence.
Autovacuum visibility: enable log_autovacuum_min_duration (e.g., 30s) so you can see “skipped due to pins,” buffer usage, and I/O timings.
Top offenders: list tables by dead tuples and by total size. Your tuning should target the top 3–5 tables, not the entire cluster.
Storage headroom: verify with iostat -x whether you have I/O room. If you’re pegged, increasing workers/cost will likely worsen latency.
Per-table thresholds: reduce autovacuum_vacuum_scale_factor on huge high-churn tables so vacuum runs earlier and smaller.
Workers: increase autovacuum_max_workers modestly if there are many tables and you have headroom.
Cost tuning: adjust cost limit/delay to balance throughput vs latency. Prefer per-table when possible.
Analyze tuning: for churny tables, reduce analyze scale factor. This prevents plan regressions that make vacuum’s life harder.
Validate: compare before/after: dead tuples trend, vacuum durations, p95/p99 latency, cache hit ratio, I/O await.
Guardrails: set sensible timeouts (idle in transaction, statement timeout where appropriate), and operational rules for DDL.
Revisit schema: reduce unnecessary indexes, avoid updating indexed columns unnecessarily, and consider partitioning for huge append/update tables.

Operational checklist: the “don’t wake me up again” set

Autovacuum duration logging enabled at a reasonable threshold.
Dashboards for: dead tuples per top tables, XID age, vacuum progress, I/O utilization, temp bytes, replication slot lag/LSNs.
Idle-in-transaction timeout set (where safe).
Runbook for terminating obvious offenders (with escalation rules).
Per-table autovacuum policies documented for the top churn tables.
DDL policy: no VACUUM FULL during business hours; concurrent operations preferred.

FAQ

1) Should I just disable autovacuum and run manual VACUUM at night?

No. That’s how you get bloat, bad stats, and eventually wraparound pressure. Use manual vacuum as a targeted tool, not as your primary strategy.

2) Why does vacuum sometimes “skip due to pins”?

Because some transaction still needs visibility of older row versions. Typical causes: long-running queries, “idle in transaction,” or certain replication/CDC patterns. Fix the pin; vacuum can’t override MVCC rules.

3) Is increasing `autovacuum_max_workers` always safe?

It’s safe in the sense that Postgres won’t explode immediately, but it can absolutely degrade latency by saturating storage and churning cache. Increase workers only after confirming I/O headroom and pinning issues are addressed.

4) What’s the safest first tuning change?

Enable autovacuum duration logging (log_autovacuum_min_duration) and adjust per-table scale factors on the largest, highest-churn tables so vacuum runs earlier. Those changes improve observability and reduce “vacuum storms.”

5) Why is autovacuum slow on SSD? SSDs are fast.

SSDs are fast, not infinite. Vacuum can generate mixed random reads and writes plus WAL. If your workload already consumes most IOPS/throughput, vacuum competes for the same budget. Also, cache churn can make “fast storage” irrelevant if everything misses RAM.

6) Does `VACUUM (ANALYZE)` help with mystery slowness?

Sometimes. If the issue is stale stats leading to terrible plans, yes. If the issue is pinning or I/O saturation, it won’t fix root causes, but it can reduce plan volatility after you fix the underlying problem.

7) Why do my indexes bloat even when table bloat looks okay?

Updates to indexed columns create dead index entries. Even with HOT updates, if the updated columns are indexed (or the row doesn’t fit on the same page), HOT won’t apply and indexes churn. Fix by reducing unnecessary indexes and avoiding updates to indexed columns when possible.

8) How do I know if autovacuum is the cause of latency, or just correlated?

Look for wait events and system I/O metrics during the spike. If application queries are waiting on I/O and vacuum is consuming a big share of reads/writes, it’s likely causal. If temp bytes, sorts, or a batch job align with spikes, vacuum may just be present at the scene.

9) Can I tune autovacuum differently per table?

Yes, and you should for large high-churn tables. Use table storage parameters like autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor, and per-table cost settings if needed.

10) What if I’m on a managed platform or container with cgroups limits?

Then you must verify CPU and I/O quotas. Vacuum may be throttled by the platform even if the VM looks “idle.” Check systemd and cgroup settings and align them with your maintenance needs.

Conclusion: next steps you can ship this week

If autovacuum is “mysteriously” slow on Ubuntu 24.04, assume it’s telling you the truth about one of three things: you’re pinned, you’re I/O-limited, or you’re under-provisioned/throttled.

Turn on visibility: set log_autovacuum_min_duration to something useful and start reading what vacuum says about pins and I/O timings.
Kill pinning at the source: eliminate “idle in transaction,” fix long-lived snapshots, and clean up abandoned replication slots.
Target the worst tables: reduce vacuum/analyze scale factors on giant churn tables so vacuum runs earlier and smaller.
Only then adjust workers and cost parameters, in small increments, while watching I/O await and p99 latency.
Make it boring: trends for dead tuples, XID age, and autovacuum durations. Boring is stable. Stable is fast.