Free tool
Is this table losing on the trigger, or on throughput?
Give it the row counts, the dead-row rate and the size, and it prints the two numbers that decide which fix is the right one: when the next vacuum starts, and how long it has to run.
Run it on your numbers
pg_class.reltuples, which is an estimate maintained by the last vacuum or analyze rather than a count.
Updates and deletes both make one. The check-up estimates it from the dead rows standing since the last vacuum.
The heap alone. Indexes are asked for separately below, because vacuum pays for them once per pass rather than once per run.
A page found in shared buffers costs one unit, a page read costs more, and how much more changed at PostgreSQL 14.
New in PostgreSQL 18, and the reason the trigger on a very large table arrives much sooner there. -1 turns the cap off.
-1 disables the insert path entirely, which on an append-only table means nothing ever sets the visibility map.
pg_class.relallfrozen over relpages, which PostgreSQL 18 uses to scale the insert threshold down on a mostly frozen table.
Paste -1 and this reads vacuum_cost_limit instead, which is what the server does.
autovacuum_work_mem when it is set, and this when it is not. Before PostgreSQL 17 anything past a gigabyte is spent and unused.
Two failures that look identical on a bloat graph
A table that is getting fatter has one of two problems, and the graph does not tell you which. Either autovacuum is not being asked to visit often enough, or it is being asked often enough and cannot get round the table before the dead rows it was sent for have been replaced by new ones. The first is a threshold problem. The second is a throughput problem. The advice for one makes the other worse.
Lowering autovacuum_vacuum_scale_factor
on a table that cannot finish a pass does not help it finish; it starts the same losing pass
sooner and more often, and each one holds a worker that some other table needed. Raising the
cost limit on a table that vacuums cleanly in twenty minutes buys nothing except more I/O
during those twenty minutes. Deciding between them needs two numbers written on the same
line, and almost nobody has them on the same line.
How long a pass takes, and how long you have
The run time above is a floor rather than a prediction. It counts only the sleeping: vacuum charges itself for every page it touches, more for one it has to read than one already in cache, more again for one it dirties, and pauses whenever that bill reaches the cost limit. What it does not count is the work between the pauses or a disk that cannot keep up, which is why the figure comes with the throughput it implies. If your storage cannot supply that many megabytes a second, the real pass is longer than the number shown.
The index passes line is the one that surprises people. Vacuum collects the dead row pointers it has to remove from every index, and before PostgreSQL 17 it collected them into a flat array that could not grow past a gigabyte no matter what you set. Past that many dead rows the vacuum stops, walks every index, empties the array and starts again. A table with six indexes and three passes reads eighteen indexes in one vacuum.
What the version selector changes here
Three things, and all of them move the answer rather than the wording. A page read cost ten units through PostgreSQL 13 and costs two from 14, which on a large cold table is most of the run time. PostgreSQL 17 replaced the flat dead pointer array with a compressed store and removed the ceiling, so the repeated index passes mostly stop happening. And 18 added a cap on the dead-row threshold itself, which on a very large table brings the vacuum forward by days rather than pushing it back.
That last one is worth sitting with if you run something big. The threshold has always grown with the table, so the bigger a table got the more dead rows it was allowed to keep before anyone came to collect them. Flip the selector between 17 and 18 and watch the trigger move.
Nobody wants to be the one who ran the query too late.
DBExplore watches the numbers behind these checks on every cluster it is pointed at, and asks before it changes anything. We onboard design partners in small batches.