Autovacuum and table bloat

Space that is allocated but not used

PostgreSQL never modifies a row in place. An UPDATE writes a new version of the row and leaves the old one where it was; a DELETE only marks the existing version. Both leave behind a tuple that is still occupying a page but is no longer visible to anyone. That is a dead tuple, and the accumulation of them is bloat.

Vacuum is what reclaims the space, and the word “reclaims” is doing careful work there. Vacuum marks the space free for reuse by the same table. It does not, except for whole empty pages at the very end of the file, give anything back to the filesystem. A table that reached forty gigabytes during a bad week stays a forty-gigabyte file afterwards; what changes is that the next ten gigabytes of inserts go into the holes instead of extending it.

So the cost of bloat is not really disk. Disk is cheap. The cost is that every sequential scan reads the empty space, every index entry pointing into a sparse heap costs a page fetch that returns one useful row, and shared buffers fill up with pages that are mostly gaps. A table at three times its necessary size does roughly three times the I/O to answer the same question.

The single most important fact about vacuum, and the one that explains most confusing cases, is this: a dead tuple can only be removed once no transaction anywhere in the cluster could still need to see it. Autovacuum running on a table is not the same thing as autovacuum removing anything from it.

Reading the counters

The per-table statistics carry a running estimate of live and dead rows.

SELECT relid::regclass                                    AS table_name,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup
             / nullif(n_live_tup + n_dead_tup, 0), 1)     AS dead_pct,
       n_mod_since_analyze,
       last_autovacuum,
       autovacuum_count
FROM pg_stat_all_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 25;

Treat those two counts as estimates rather than measurements. They are maintained incrementally from the activity each backend reports and are corrected whenever the table is vacuumed or analysed, which means they can drift on a table with a lopsided write pattern and they are wrong immediately after a statistics reset. They are good enough to rank tables against each other and not good enough to bill anyone for.

The counts matter because they are what triggers autovacuum. A table becomes eligible when its dead tuples exceed autovacuum_vacuum_threshold plus autovacuum_vacuum_scale_factor multiplied by the estimated row count, which with the shipped defaults of 50 and 0.2 means a fifth of the table has to be dead before anything happens. On a table of ten rows that is instant. On a table of two hundred million rows it is forty million dead tuples, and by then the damage is done. Since version 13 there is a second trigger, autovacuum_vacuum_insert_threshold with its own scale factor, which catches append-only tables that never accumulate dead rows but still need their visibility map maintained.

Version 18 addresses the scale-factor problem directly with autovacuum_vacuum_max_threshold, a ceiling on the computed trigger point, so a table cannot push its own vacuum further and further away simply by growing. It ships at a hundred million rows, and at the default scale factor that ceiling starts to bind once a table passes five hundred million rows: below that size the percentage is still the smaller of the two numbers and nothing changes, and above it the vacuum arrives earlier than it would have on 17 and then keeps arriving at the same absolute interval however large the table gets. The same release changed the insert trigger in a subtler way, scaling it by the share of the table that is not yet frozen, so a mostly frozen append-only table fires its insert vacuum after far fewer rows than the raw row count would suggest. The work follows the part of the table that is still moving.

You can compute the distance to the threshold directly, remembering that per-table storage parameters override the cluster settings:

SELECT c.oid::regclass AS table_name,
       s.n_dead_tup,
       (current_setting('autovacuum_vacuum_threshold')::bigint
        + current_setting('autovacuum_vacuum_scale_factor')::float8
          * c.reltuples)::bigint AS cluster_threshold,
       c.reloptions
FROM pg_class c
JOIN pg_stat_all_tables s ON s.relid = c.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY s.n_dead_tup DESC
LIMIT 25;

Anything non-null in reloptions means the row above is not the threshold that applies; read the per-table value there instead.

Three reasons the dead rows are still there

The counters tell you a table is bloated. They do not tell you why, and the three causes want three different responses.

Nothing got to it. Autovacuum launches at most autovacuum_max_workers processes, sleeps autovacuum_naptime between rounds, and on a large fleet of tables a worker can spend an hour on one table while a hundred others queue behind it. The signature is last_autovacuum being old on many tables at once, not just the one you are looking at.

It ran and could not remove anything. This is the interesting case. Vacuum cannot remove a tuple that is newer than the oldest snapshot still held anywhere, and four things hold that horizon back: a long-running query, a session sitting idle in transaction, a replication slot whose consumer has stopped reading, and a prepared transaction nobody committed. All four are visible:

SELECT 'backend'              AS holder, pid::text   AS id, age(backend_xmin) AS xid_age
FROM pg_stat_activity        WHERE backend_xmin IS NOT NULL
UNION ALL
SELECT 'replication slot',    slot_name,               age(xmin)
FROM pg_replication_slots    WHERE xmin IS NOT NULL
UNION ALL
SELECT 'replication slot',    slot_name,               age(catalog_xmin)
FROM pg_replication_slots    WHERE catalog_xmin IS NOT NULL
UNION ALL
SELECT 'prepared transaction', gid,                    age(transaction)
FROM pg_prepared_xacts
ORDER BY xid_age DESC;

The age() function is the right tool here because the xid type carries no ordering operators of its own; asking for the largest of two transaction IDs will not compile, while asking how many transactions ago each one was will.

It ran, it could remove things, and it is going slowly on purpose. Autovacuum throttles itself: after doing autovacuum_vacuum_cost_limit units of work it sleeps for autovacuum_vacuum_cost_delay, so that maintenance does not starve the workload. A vacuum that appears to run permanently may be spending most of its wall clock asleep. Until PostgreSQL 18 you could not distinguish that from a vacuum that was genuinely busy. Now you can, if you turn on track_cost_delay_timing:

SELECT pid,
       relid::regclass AS table_name,
       phase,
       heap_blks_scanned, heap_blks_total,
       indexes_processed, indexes_total,
       delay_time
FROM pg_stat_progress_vacuum;

indexes_total and indexes_processed arrived in PostgreSQL 17, delay_time in 18. On 16 and below, drop those three columns and read index_vacuum_count instead, which counts how many times the index-scanning phase has restarted. Anything above one there is a memory problem rather than a throttling one, and it has a section of its own further down.

Putting a number on the throttle

“It is throttled” is not an actionable diagnosis. “It can move about thirty-five megabytes a second and the dead rows come back faster than that” is, and the arithmetic between the two is short.

Vacuum charges itself for every page it touches. A page already in shared buffers costs vacuum_cost_page_hit, a page it has to read costs vacuum_cost_page_miss, and a page it modifies costs vacuum_cost_page_dirty on top of whichever of those applied. When the running total reaches the cost limit the worker sleeps for the cost delay and the total resets. A handful of settings decide the whole rate, and the server will tell you both what it is using and what it shipped with:

SELECT name, setting, unit, boot_val, context
FROM pg_settings
WHERE name LIKE 'vacuum_cost%'
   OR name LIKE 'autovacuum%'
   OR name = 'maintenance_work_mem'
ORDER BY name;

Out of the box that is 200 cost units per 2 milliseconds of sleep, which is a hundred thousand units per second of throttled time. A page that has to be read and then written costs 22 of those units, so the shipped budget buys something like four and a half thousand dirtied pages a second, a little under thirty-five megabytes. A page that is only read costs 2, so the same budget waves through nearly four hundred megabytes a second of clean scanning. The gap between those two figures is deliberate: the throttle is there to bound the writes, and reading is charged almost nominally.

One row in that output surprises people who have only ever tuned the autovacuum settings: vacuum_cost_delay, which governs a VACUUM you type yourself, ships at zero. A manual vacuum is not throttled at all. That is why running one by hand finishes in a fraction of the time the background worker was taking, and it is also why doing so on a busy afternoon is a decision rather than a shortcut.

Two of those numbers have moved and the folklore has not caught up with either. autovacuum_vacuum_cost_delay was 20 milliseconds until PostgreSQL 12 lowered it to 2, multiplying the default budget by ten in a single release. vacuum_cost_page_miss was 10 until PostgreSQL 14 lowered it to 2, cutting the charge for a table that does not fit in cache by a further factor of five. Advice to raise autovacuum_vacuum_cost_limit far above its default is a relic of the first of those, and a limit copied out of a runbook written before both is asking the storage for something like fifty times what its author had in mind.

Then the correction that catches nearly everybody: the budget is not per worker. autovacuum_max_workers defaults to three, and the cost limit is divided among whichever workers are running, so what they consume between them never exceeds the single figure. Raising the worker count does not make vacuuming faster. It makes each vacuum slower and starts more of them at once, which is the right trade when a hundred tables are queueing and the wrong one when a single table cannot be finished. Throughput comes from the cost limit and the cost delay; coverage comes from the worker count; and the two get confused in the same direction every time, because adding workers feels like adding capacity.

PostgreSQL 18 at least takes the restart out of that decision. Worker slots are allocated at startup from the new autovacuum_worker_slots, and autovacuum_max_workers moved into the group of settings a reload can change, so the count can be raised during an incident and put back afterwards without a bounce. An unused slot costs shared memory and nothing else.

If you would rather have the numbers than the argument, the autovacuum trigger calculator takes your row counts, your dead-row rate, your table size and your cost settings and prints how long one pass sleeps against how long the dead rows take to return to the threshold. Whichever of those two is larger decides which fix is yours, and they are rarely on the same screen anywhere else.

The pass that happens six times

The expensive part of a vacuum is usually not the heap scan. A vacuum collects the item pointers of the dead tuples it finds, then visits every index on the table to remove the entries pointing at them, then returns to the heap to free the line pointers. If the collection fills before the heap scan has finished, the cycle repeats from where it stopped: more heap, every index again, more heap.

What sizes that collection is autovacuum_work_mem, or maintenance_work_mem when it is left at its default of -1. Before PostgreSQL 17 the collection is a flat array of six-byte item pointers, which makes the capacity arithmetic rather than a matter of opinion: the shipped 64 MB holds a little over eleven million dead tuples, so a table that accumulated forty million of them reads every one of its indexes four times in one vacuum. On a table carrying half a dozen indexes, that is where the hours go. The array is also clamped at a gigabyte however much memory you give it, which puts a hard ceiling near a hundred and seventy-eight million dead tuples per pass on those majors, so a large enough table cannot be done in a single pass at any setting at all.

PostgreSQL 17 replaced the array with a compressed store and dropped the clamp with it, which is worth understanding before sizing the setting. How many dead tuples a given amount of memory now holds depends on how they cluster in the heap, so there is no honest figure to quote. What changed operationally is that one pass became the normal case rather than the lucky one, and that raising the memory past a gigabyte finally does something.

Two cautions on raising it. It is per worker, so multiply by the worker count before deciding the machine has the memory to spare. And it is allocated as needed rather than reserved, so coping on a quiet afternoon is not evidence about the night three large tables come due together. The pass count is also the strongest argument for doing the throttle arithmetic above: a pass that sleeps for four hours is bad, and four of them is a table shut out of maintenance for most of a day.

The cheapest vacuum is the one with nothing to do

Everything so far treats the dead rows as a given. On an update-heavy table they are partly a choice.

When an UPDATE changes no indexed column and the new version fits on the same page as the old one, PostgreSQL writes it as a heap-only tuple. The new version is chained off the old one on that page and no index entry is written, because the indexes already point at a line pointer that now leads to the chain. The old version becomes reclaimable by page pruning, which happens opportunistically when any backend reads the page, so the space comes back without a vacuum visiting the table at all.

Both conditions are yours to influence. The indexed-column one is a schema question, and the usual offender is an index on the column the hot path rewrites every time, a last_seen_at or a status that carries an index because somebody once sorted a report by it. Every update to the table then pays twice, once for the index entry it writes and once for the dead tuple it can no longer avoid. The page-space condition is fillfactor, which defaults to a hundred for a heap, so pages are packed full on first write and the very first update to a row has nowhere local to go. Dropping it to ninety on a heavily updated table spends disk to buy back in-place updates, and how the two conditions interact is worth reading before picking a number.

The ratio is reported per table, and it is the one statistic on this page that tells you about your schema rather than your vacuum settings:

SELECT relid::regclass AS table_name,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct,
       n_tup_del
FROM pg_stat_all_tables
WHERE n_tup_upd > 0
ORDER BY n_tup_upd DESC
LIMIT 15;

A table doing millions of updates at a low percentage is generating dead tuples and index churn that no amount of vacuum tuning will make cheap. From PostgreSQL 16 a third counter sits beside those two and separates the updates that failed for want of space from the ones that failed because an indexed column changed. That is exactly the distinction you need, because only the first kind is fixed by fillfactor.

Bloating a table on purpose

None of this needs a production table to try, and watching the numbers move is worth more than reading about them. On a scratch database:

CREATE TABLE public.orders (
  order_id     bigserial PRIMARY KEY,
  customer_id  integer     NOT NULL,
  status       text        NOT NULL,
  amount_cents bigint      NOT NULL,
  placed_at    timestamptz NOT NULL DEFAULT now()
);

INSERT INTO public.orders (customer_id, status, amount_cents, placed_at)
SELECT (random() * 50000)::int,
       'placed',
       (random() * 20000)::bigint,
       now() - (random() * interval '90 days')
FROM generate_series(1, 200000);

CREATE INDEX orders_customer_id_idx ON public.orders (customer_id);
VACUUM (ANALYZE) public.orders;

UPDATE public.orders SET status = 'shipped';

The last statement rewrites every row, so the table now holds one live and one dead version of each. The file has doubled, the row count has not moved, and the index has a second entry for every row it already had. That is bloat in its purest form, and it took one statement.

Measuring instead of estimating

When the decision is expensive, measure. The pgstattuple extension opens the table and counts:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple('public.orders');
SELECT * FROM pgstatindex('public.orders_customer_id_idx');

pgstattuple returns the real dead_tuple_percent and free_percent at the cost of a full scan, which on a very large table is not free. pgstattuple_approx samples instead and is cheap enough to run in business hours. Between them they answer the question the statistics views can only guess at: how much of this file is actually air. Run a plain VACUUM on the table above and then ask both questions again. The dead percentage collapses, the free percentage takes its place, and the file on disk is the size it was before. That is the distinction the first section of this page was drawing, in numbers you produced yourself.

The vacuum that keeps being asked to leave

There is a fourth reason a table stays bloated, and it hides from all three of the queries above.

A regular autovacuum yields. When a worker holds a lock another session wants, the worker is signalled and gives up, leaving a line in the log saying its task was cancelled. That is the right default, because maintenance must never be the reason a deployment blocks. It becomes a failure mode when the conflicting lock is regular. A table that receives an ALTER TABLE, a CREATE INDEX, a partition attach or an application-level LOCK every few minutes can have its autovacuum cancelled every single time it starts, and in the statistics view that is indistinguishable from a table nothing ever got to.

autovacuum_count separates them, because it counts completions rather than attempts. A table with a large dead-tuple count, an autovacuum_count that has not moved in a week and cancellation lines in the log is being interrupted, and no threshold you lower will help. What helps is making the conflicting work less frequent, or running a manual VACUUM at an hour when it is not happening.

Anti-wraparound vacuums are the exception. A worker running to hold off wraparound is not cancelled by a conflicting lock request, which is why the first sign of a long-neglected table is often a DDL statement that hangs indefinitely behind a vacuum nobody knew had started, and why transaction ID wraparound is a page of its own rather than a section of this one. The final phase of a normal vacuum behaves the same way in reverse: truncating trailing empty pages needs an exclusive lock, and vacuum abandons the attempt rather than waiting for it, so a table can stay exactly as large as it was after a vacuum that reported removing everything.

Set log_autovacuum_min_duration to zero on a server you are investigating. Every autovacuum then logs its pages scanned, tuples removed, buffer usage and elapsed time, including the runs that removed nothing, and that log is the only place cancellations and abandoned truncations appear at all.

Getting the space back, and when not to

Regular vacuum is the answer for keeping bloat flat. It is not the answer for undoing bloat that already happened, because it only truncates trailing empty pages. To actually shrink the file you need a rewrite: VACUUM FULL, which takes an ACCESS EXCLUSIVE lock and needs room for a second copy of the table, or CLUSTER, which does the same thing in physical index order, or the pg_repack extension, which does it with brief locks at either end instead of one long one.

Before any of those, fix the cause, or you will be doing it again next quarter. On a large, heavily updated table the lasting change is usually to lower autovacuum_vacuum_scale_factor for that table alone, so it is vacuumed on a fixed-ish budget rather than a percentage of an ever-growing row count:

ALTER TABLE public.orders SET (autovacuum_vacuum_scale_factor = 0.01,
                               autovacuum_vacuum_threshold    = 10000);

That is the right lever only when the table is losing on the trigger. When one pass takes longer than the dead rows take to come back, moving the trigger earlier starts the same losing race sooner, and it is the cost limit, the index count or the schema that has to change instead.

On PostgreSQL 18 the guesswork about whether a vacuum is behind or merely throttled goes away, because the server reports the time spent asleep under the cost delay directly in the progress views and in the log line; the measurement and what to do with it are in how much of a vacuum is spent asleep, and what each table has cost to maintain is in what each table costs to keep vacuumed.

And if the horizon query above returned anything older than a few minutes, none of this tuning will help until that is dealt with. A single forgotten session in idle in transaction will hold every table in the cluster hostage, and behind a pooler the session holding it may not even be attributable to a service, which is covered in connection pooling and PgBouncer.