Vacuum advisor
Bloat is a symptom. Advice has to name the cause.
A table growing on disk while its row count stays flat has three possible explanations, and only one of them is about autovacuum. Anything calling itself a vacuum advisor has to tell you which one you are looking at.
Three different problems wearing one symptom
Postgres will not remove a dead row while any transaction might still need to see it. That single rule produces the same appearance from three unrelated failures. Somebody left a session open in a psql window yesterday morning and every deletion since then is pinned behind it. A replication slot with no consumer is doing the same thing from another machine, and it will keep doing it until somebody notices the slot. Or autovacuum is simply losing: too few workers for the number of tables, a cost limit last considered on smaller hardware, a table big enough that a pass never gets to the end before it is interrupted.
The fixes have nothing in common. One is a conversation with an application team about transaction lifetimes. One is a slot that should have been dropped. Only the third is a setting anybody changes. So advice that starts and ends at the symptom sends an engineer to tune a table whose problem is somewhere else, and when the tuning does nothing the advice is never trusted again.
The freeze side has the opposite character. It is quiet, it is measurable months ahead, and it is the one Postgres failure that stops the database until somebody fixes it. Nothing on a dashboard changes on the way there. The counter just moves.
Four things to demand of vacuum advice
Useful whoever is offering it, us included. The first is the one most tools skip, because it needs a view wider than the table the finding is about.
It names which of the three it is
Dead rows pile up because a snapshot is held open, because a slot is holding one at a distance, or because autovacuum is losing the race. Two of those are not fixed by touching vacuum at all.
It watches the freeze runway too
Bloat costs money. Running out of transaction IDs costs the database. The second is visible months ahead on a counter, and it is the one people are not looking at.
It knows what storage means here
A rule reasoning about page reuse and visibility maps says something true about a single node and something meaningless about a distributed engine. Firing everywhere is not coverage.
It does not vacuum on its own initiative
A manual pass over a large table is unscheduled IO on a production system. Whether that may happen without a person is a decision for somebody who knows the maintenance window.
Where we start instead
With the engine. Every detection rule carries the engines it applies to, so the ones reasoning about page reuse and visibility never fire on a platform where those internals are not yours to reason about. Which engine this is gets established by fingerprint rather than by the connection string, and the engine dimension lists what that distinguishes between. Rule tuning per engine is the bulk of detection, and vacuum is where it earns itself.
Then with everything outside the table. The oldest snapshot in a cluster is frequently not on the node the bloated table lives on, so the age of the oldest transaction, the state of every replication slot and the retained write-ahead log behind each one are collected per node and read together. That is the difference between “this table is bloated” and “this table cannot be cleaned, and here is what is holding it”. Replication monitoring covers the slot side of that in its own right, because a slot nobody consumes ends clusters for reasons beyond bloat.
Freeze headroom is then reported as runway — the time left at the rate the counter is actually moving — rather than as a percentage, which tells you nothing about whether you have a fortnight or a year. Bloat and runway are two of the signals every cluster contributes to the fleet view, and what that looks like across a few hundred of them is on fleet management.
Acting is a separate question from finding. Running a vacuum, freezing a table, or dropping a slot are governed actions: each declares a safety class and whether it can be undone, each dry-runs before it executes and probes afterwards to prove the change landed, and a no-op comes back as a no-op. Above that sits the earned-autonomy ladder, whose first rung writes nothing at all, and the fail-closed gate that refuses anything you have not allowed in writing. Disruptive work needs more than one approver and cannot be switched to automatic, ever.
What a finding looks like
The cause on the second line, the thing holding it on the third, and the reason to believe all of it underneath.
finding dead rows on orders are not removable, and have not been for days cause the oldest snapshot in the cluster is a replication slot holder a logical slot that has had no consumer for days evidence autovacuum has completed twice and reclaimed nothing either time freeze headroom on this table is shrinking · weeks of runway, not months rung recommend · disruptive, so more than one approver before anything runs
The line that changes the conversation is the fourth. Two completed autovacuum runs that reclaimed nothing is proof the setting is not the problem, and it arrives before anybody has spent an afternoon lowering a cost delay that was never going to help. Bloat also moves row estimates, which is how a vacuum problem turns up first as a plan that stopped being chosen — query performance is where that thread gets picked up.
Go and look yourself
Everything above is catalog data you already have. Autovacuum and table bloat has the queries that separate the three causes, including the ones that are wrong in ways worth knowing about, and transaction ID wraparound tells you how far along the counter is on your worst table and what the number means. An hour with both will answer this for one cluster.
Do that first. If one cluster is the whole problem, the answer costs an afternoon and you should keep the afternoon. Ten clusters where the oldest transaction is on a different node each week is a different shape of problem, and that is the one worth paying to make somebody else’s.
See which of your tables cannot be cleaned, and why.
A pilot starts where nothing is allowed to mutate. You get the causes and the freeze runway on your own clusters, and nothing runs a vacuum until you have said it may.