Autovacuum Tuning and the Cost Budget Every Worker Shares

autovacuum_max_workers is not a throughput setting. Autovacuum gets one budget of I/O per second for the entire cluster, and that budget is divided among the workers that happen to be running. Three workers each move at a third of the speed one worker would. The total work done per second is identical, and on your largest table more workers make things measurably worse.

The budget is two settings multiplied out. autovacuum_vacuum_cost_limit is how much accounting cost a vacuum may accumulate before it must sleep, and autovacuum_vacuum_cost_delay is how long it sleeps. At the PostgreSQL 18 defaults — a limit of 200, inherited from vacuum_cost_limit, and a delay of 2 ms — that is 200 cost units every 2 ms, or 100,000 cost units per second. Vacuum is charged 20 units for every 8 kB page it dirties and 2 for every page it reads from disk, so the ceiling is roughly 41 MB/s of dirtied pages, or 410 MB/s if it is only reading. That is the whole cluster's allowance, on any hardware, whether you run one worker or eight.

The panel runs 24 hours of an update-heavy cluster: six tables, 13,500 updates per second between them, and a 25 GB orders table with 200 million rows and 12 GB of indexes. Every control re-runs the whole day, so read each change as a fresh 24 hours. Start by dragging autovacuum_max_workers from 1 to 8 and watching the two numbers that matter: peak dead rows in orders barely moves, and cluster vacuum rate does not move at all.

At the defaults, eight workers and one worker vacuum orders exactly four times each, peaking at 44.4 million and 42.5 million dead rows respectively — a difference of 4%, in the wrong direction. What does change is who suffers. With one worker, job_queue — a 500,000-row table taking 4,000 updates per second — waits up to 32 minutes for its turn and peaks at 7.8 million dead rows. With eight, it waits 4 minutes and peaks at 1.3 million. The workers did not add capacity. They changed the queue discipline.

Where the workers actually hurt

Push cluster write rate to 40,500 updates per second — three times the default — and leave everything else alone. Now the cluster is busy 85% of the day and workers genuinely overlap. orders peaks at 60.1 million dead rows and its longest pass takes 56 minutes.

Now raise autovacuum_max_workers to 8. orders peaks at 74.9 million and its longest pass takes 97 minutes. Adding five workers made the important table 25% more bloated. The decision log says why in one line: the first pass on orders ended holding 40 of the 200 cost units, because four other workers were running and the limit was divided five ways.

This is the documented behaviour, stated once in the reference for a setting almost nobody touches. autovacuum_vacuum_cost_limit "is distributed proportionally among the running autovacuum workers, if there is more than one, so that the sum of the limits for each worker does not exceed the value of this variable." Section 24.1.6 puts it as a design goal: the balancing exists "so that the total I/O impact on the system is the same regardless of the number of workers actually running." Workers are a concurrency setting. They decide how many tables can be in progress at once, and therefore how long a small table waits behind a large one. They have never decided how fast.

Which is not to say the default of 3 is right. Look at job_queue in the timeline at one worker: a 500,000-row table absorbing 4,000 updates a second, sitting orange for half an hour at a time because the single worker is 20 minutes into orders. Small hot tables are exactly what extra workers are for. Just do not expect them to reduce the bloat on the table that is holding the worker.

The setting that does change capacity is the pair the log calls the budget. Put the write rate back to 40,500 and drag autovacuum_vacuum_cost_limit from 200 to 2,000. Peak dead rows in orders falls from 60.1 million to 41.4 million, the longest pass drops from 56 minutes to 3 minutes, and the cluster now has a worker awake only 12% of the day instead of 85%. Ten times the budget, ten times the throughput. Halving autovacuum_vacuum_cost_delay from 2 ms to 1 ms does exactly the same thing, for the same reason — the rate is the quotient of the two.

One caution about the delay, from the documentation rather than the model: "keep vacuum_cost_delay as small as your platform will consistently measure; large delays are not helpful." Sub-millisecond sleeps are not accurately measurable everywhere. If you are already at 1 ms, raise the limit rather than shaving the delay further.

Why the pass costs what it costs

Nothing above is tunable without knowing what a pass has to do. A vacuum of one table is three pieces of work, and only the first one scales with how much garbage there is.

The heap scan. Vacuum reads every page that is not marked all-visible in the visibility map — the one-bit-per-page fork that records which pages contain only rows visible to every transaction. A page it must clean costs 2 units to read and 20 to dirty. At the default trigger, orders has 40 million dead rows spread over 3.75 million pages, which means essentially every page has at least one, so nothing is skipped: 86.3 million cost units before a single index is touched. Note the version dependency: vacuum_cost_page_miss was 10 rather than 2 until PostgreSQL 14, so on an older server the same heap scan is charged five times as much for its reads.

The index scans. To delete an index entry pointing at a dead row, Postgres has to find it, and a B-tree offers no way to look up an entry by the row it points at. So it reads the entire index, every time. All 12 GB of orders' indexes are scanned to remove 40 million pointers, and they would be scanned to remove 400. That is a flat 33 million cost units per pass, independent of how much work there is to do — which is the single most important fact for tuning, and the reason index bloat and vacuum cost are the same conversation.

PostgreSQL 14 added the escape hatch: if fewer than 2% of the table's pages contain a dead line pointer, and the collected dead-item list is under 32 MB, vacuum skips index vacuuming entirely and logs index scan bypassed:. On a table where updates land randomly, 2% of pages means roughly 2% of a page's worth of updates, so a big table almost never qualifies. On an append-mostly table with localised churn, it qualifies constantly.

The second heap pass. After the indexes are clean, vacuum revisits the pages it noted and turns the dead line pointers into free space. Those pages are already in shared buffers, so they cost 1 unit each.

Put those together and the shape of the cost function is not what most tuning advice assumes. A pass that removes 40 million rows costs 119 million units. A pass that removes 2 million costs 59.9 million — half the cost for a twentieth of the benefit, because the index scan is fixed and the heap scan has a large fixed component too. Drag orders scale_factor from 0.200 to 0.010 and read the cluster line: peak dead rows collapses from 44.3 million to 4.7 million, which is the result you wanted, but total vacuum cost units for the day go from 3,004 million to 7,360 million and the cluster goes from 35% busy to 85% busy. You bought a 9× reduction in bloat with 2.5× the I/O. On this cluster that is a good trade. On one already at 80% busy it is not available.

The floor, and the spiral below it

Keep dragging. At a scale factor of 0.002 — 400,050 dead rows, a setting people really do put on big tables — peak dead rows in orders only improves from 4.7 million to 3.6 million, while the cluster hits 100% busy and spends 8,603 million units. The trigger is now firing long before the previous pass has finished, so the threshold has stopped controlling anything.

What controls it instead is the length of one pass. A vacuum removes the rows that were dead when it started and nothing that dies while it runs, so the dead count can never sit below update rate × pass duration. At 2,000 updates per second and a 17-minute pass, that floor is 2.0 million rows. Every threshold setting below the floor is a setting that costs I/O and buys nothing. The decision log prints the floor for the current configuration on the orders line, and the only two ways to move it are to make the pass cheaper — fewer indexes, more HOT updates, a smaller table — or to raise the budget so the same pass finishes sooner.

Below the floor there is a worse regime, and it is one drag away. Set autovacuum_vacuum_cost_delay to 20 ms and put everything else back to default.

20 ms was the default until PostgreSQL 12 lowered it to 2 ms, which means every postgresql.conf copied forward from a 9.x or 11 installation still carries it. It gives the cluster 10,000 cost units per second: 4 MB/s of dirtied pages. Watch what the day looks like:

• orders completes one vacuum in 24 hours. It takes 10 hours. The next one is 76% done at midnight, and the table peaks at 132 million dead rows against 200 million live ones.
• job_queue is over its threshold for 21 hours straight with no worker ever free, and ends the day holding 311 million dead rows on a 500,000-row table.
• The total spend is 855 million cost units — a third of what the default configuration used — because the cluster was never able to spend faster, not because there was less to do.

This is the state the brief usually describes as "autovacuum can't keep up", and it is where the misconception does its real damage, because the timeline is full of orange and the obvious reading is that there are not enough workers. Set autovacuum_max_workers to 8 here. orders completes zero passes: the one that started at 05:36 is still going at midnight, 92% done, because it is sharing 200 units six ways. Peak dead rows goes from 132 million to 172 million. Meanwhile job_queue is rescued — 11.7 million dead rows instead of 311 million. Total spend: 856 million units, against 855 million with three workers. Same capacity, redistributed, and the redistribution took the largest table off the cliff and put it over.

There is a feedback loop in this that the model does include and that makes the real thing worse than the arithmetic suggests: the dead rows are stored in the table. At 64 rows to a page, 132 million dead rows is 2.1 million extra pages on top of 3.1 million — a heap two thirds larger than the one the settings were chosen for. The next pass has more pages to scan, so it takes longer, so more rows die while it runs. Bloat makes vacuum slower, and slow vacuum makes bloat. Once you are inside the loop, restoring the original settings is not enough to get out of it — the table is already big.

The per-table escape hatch, and its price

Keep the delay at 20 ms, put the workers back to 3, and tick per-table cost limit of 2,000 on orders. That is ALTER TABLE orders SET (autovacuum_vacuum_cost_limit = 2000). orders goes from one 10-hour pass to four 20-minute passes and peaks at 47.4 million dead rows instead of 132 million, while every other table stays exactly as broken as it was.

The reason it works is a clause most people never read. A worker processing a table that has its own autovacuum_vacuum_cost_limit or autovacuum_vacuum_cost_delay storage parameter is "not considered in the balancing algorithm." It does not take a share of the global limit and it does not reduce anyone else's share. It runs at its configured rate, on top of everything else.

Which is exactly the danger. Watch total vacuum cost units when you tick the box: 855 million becomes 1,337 million. Per-table cost settings do not reallocate the cluster's I/O budget, they enlarge it, and nothing enforces a ceiling on the sum. Five tables with autovacuum_vacuum_cost_limit = 2000 and a 2 ms delay can each demand 1,000,000 cost units per second, all at once, on a disk that cannot supply it. The global limit is a promise about total impact; a per-table limit is an exemption from that promise.

Two more boundaries the simulation does not model, both of which turn a slow vacuum into one that never finishes.

The first is cancellation. An autovacuum holds a SHARE UPDATE EXCLUSIVE lock, and if any statement asks for a conflicting lock — ALTER TABLE, CREATE INDEX, a plain ANALYZE, or another VACUUM — the autovacuum is cancelled, and the client sees nothing but a brief wait while the server logs ERROR: canceling autovacuum task. The work it had done on the heap is not wasted, but the pass restarts from the first page next time. A 10-hour vacuum on a table with an hourly migration job will never complete a pass, and the documentation says so plainly: "regularly running commands that acquire locks conflicting with a SHARE UPDATE EXCLUSIVE lock (e.g., ANALYZE) can effectively prevent autovacuums from ever completing." The one exception is an anti-wraparound autovacuum, which is not cancelled automatically — that is the wraparound emergency refusing to yield.

The second is that none of this matters if vacuum is not allowed to remove anything. Everything on this page assumes each pass removes the rows it finds. If one old transaction, prepared transaction or replication slot is holding the xmin horizon — the oldest transaction id any consumer might still need to see — then every pass reads all 25 GB, dirties nothing, removes nothing and reports success. Before you tune anything here, confirm you have the throughput problem and not the visibility problem. They look identical on a dashboard and share no fixes.

Checking it on a real system

Four queries, in this order. First, work out what your actual budget is, because the effective value is usually not the one in postgresql.conf:

SELECT name, setting FROM pg_settings
WHERE name IN ('autovacuum_vacuum_cost_limit','vacuum_cost_limit',
               'autovacuum_vacuum_cost_delay','autovacuum_max_workers',
               'vacuum_cost_page_dirty','vacuum_cost_page_miss');

An autovacuum_vacuum_cost_limit of -1 means the real limit is vacuum_cost_limit, default 200. Divide it by the delay in milliseconds and multiply by 1,000 to get units per second; divide that by vacuum_cost_page_dirty and multiply by 8,192 for the worst-case megabytes per second. If that number is smaller than the write throughput your application produces, you have found your answer before looking at anything else.

Second — and this is the query that answers the most-viewed autovacuum question on Stack Exchange — find out which tables have already been given their own settings, since those silently override the global ones and do not appear in pg_settings at all:

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

Third, watch a running vacuum rather than guessing at it. pg_stat_progress_vacuum gives you the position and, crucially, the number of index passes:

SELECT p.pid, c.relname, p.phase,
       p.heap_blks_scanned, p.heap_blks_total,
       round(100.0 * p.heap_blks_scanned / nullif(p.heap_blks_total,0), 1) AS pct,
       p.index_vacuum_count, p.delay_time,
       now() - a.xact_start AS running_for
FROM pg_stat_progress_vacuum p
JOIN pg_class c ON c.oid = p.relid
JOIN pg_stat_activity a ON a.pid = p.pid;

Two columns decide the diagnosis. index_vacuum_count greater than 1 means maintenance_work_mem was too small to hold the dead row identifiers in one go, so every index is being read from start to finish more than once — raising maintenance_work_mem is then worth more than any cost setting. And delay_time, added in PostgreSQL 18 and only populated when track_cost_delay_timing is on, is the total milliseconds this vacuum has spent asleep in the cost delay. If it is most of running_for, the vacuum is throttle-bound and nothing else is wrong with it.

Fourth, read the autovacuum log rather than inferring from statistics. Set log_autovacuum_min_duration to 0 temporarily — the default is 10 minutes, which hides every fast vacuum and therefore hides the contrast you need. Each completed autovacuum then logs, on PostgreSQL 18:

LOG:  automatic vacuum of table "app.public.orders": index scans: 1
      pages: 0 removed, 3751250 remain, 3751164 scanned (100.00% of total), 0 eagerly scanned
      tuples: 40080000 removed, 200000000 remain, 0 are dead but not yet removable
      removable cutoff: 884213991, which was 412 XIDs old when operation ended
      index scan needed: 3751164 pages from table (100.00% of total) had 40080000 dead item identifiers removed
      avg read rate: 12.404 MB/s, avg write rate: 11.980 MB/s
      buffer usage: 41203 hits, 3751164 reads, 3623410 dirtied
      system usage: CPU: user: 91.22 s, system: 41.10 s, elapsed: 2100.41 s

Compare avg write rate against the ceiling you computed in step one. Within a few percent of it means the throttle is the binding constraint and raising the cost limit will translate directly into a shorter pass. Far below it means something else is — the disk, or the CPU line above, or an index scans: count above 1. The word bypassed in place of needed tells you this table is getting the 2% optimisation and its vacuums are cheap. And 0 are dead but not yet removable is the line that rules out the horizon problem; a large number there sends you to the visibility problem instead.

What to change, in the order worth trying. Raise autovacuum_vacuum_cost_limit globally — on modern storage, 1,000 to 2,000 is defensible where 200 was chosen for spinning disks in 2006, and it is the only change that increases total capacity. Then set per-table autovacuum_vacuum_scale_factor on tables over a few million rows, because 20% of a large table is an absurd amount of garbage to tolerate — but stop at the floor the pass duration imposes, and add autovacuum_vacuum_threshold in absolute rows rather than chasing the scale factor towards zero. Raise autovacuum_max_workers only when the symptom is small tables waiting, and know that you are slowing the big ones to do it. On PostgreSQL 18 that last one no longer needs a restart: autovacuum_worker_slots reserves the process slots at startup, default 16, and autovacuum_max_workers can then be changed with a reload up to that number.

Next: the visibility map these vacuum rounds maintain is not only bookkeeping — it is what decides whether an index-only scan actually avoids the heap. See index-only scans and the visibility map.