Skip to main content

Autovacuum monitoring

note

In closed beta.

What autovacuum does​

To keep concurrent reads and writes consistent, PostgreSQL uses a built-in system generally known as MVCC (multi-version concurrency control). One core principle is that data is never directly deleted, just marked as dead. This way, that data may still be accessible for other parallel operations. However, eventually old data becomes dead-weight and needs to be cleaned up. This is the main task of the PostgreSQL autovacuum daemon.

When Autovacuum works perfectly, you do not even notice it. However, if the Autovacuum falters in some way, tables tend to start accumulating excessive dead rows. This often drastically reduces the performance of your database. There are a plenty of issues that could cause Autovacuum to fail to keep up: insufficient capacity, lingering open transactions, insufficient memory allowance, etc.

Autovacuum in DBtune

DBtune watches autovacuum at two levels, each on its own view under the Autovacuum tab of a database:

  • The instance view summarises the health of autovacuum across the whole server through a row of headline cards. Each card opens a detail panel with the supporting evidence.
  • The table view drills down to individual tables, ranking them by how urgently they need vacuuming.

Instance view​

The instance view carries five headline cards, each colour-coded green / yellow / red so you can see at a glance where attention is needed.

Transaction ID wraparound​

Every row records the transaction ID (the xid) that last modified it. Because the xid is a 32-bit counter, PostgreSQL constantly recycles it, and to do so safely it must "freeze" old rows — marking them as permanently visible so their xid can be reused. If freezing ever falls far enough behind that the oldest unfrozen transaction approaches roughly two billion transactions of age, PostgreSQL takes increasingly drastic protective measures, culminating in refusing new writes until the backlog is cleared.

This card reports how close the server is to that point, expressed as a percentage of the way into the danger zone. Green means freezing is comfortably keeping up, yellow means the oldest unfrozen transaction has aged past autovacuum_freeze_max_age, and red means it is approaching the hard wraparound limit.

Details​

The detail panel breaks the xid age down per database and per table (for the database the agent is connected to), each with its own state, xid age, oldest xid, and XID % — how far that database or table has progressed toward the wraparound limit. This makes it easy to spot the specific relation that is holding the whole server back.

Xmin horizon latency​

Autovacuum cannot remove a dead row while any active snapshot might still need to see it. The xmin horizon is the single oldest such snapshot anywhere on the server, and it is frequently the real ceiling on what autovacuum can reclaim — often more limiting than any individual table's freeze age. Crucially, the horizon is not held back only by long-running queries: an idle-in-transaction session, a replication connection, an inactive replication slot, or a prepared (two-phase-commit) transaction can each pin it just as effectively.

If the horizon stays stuck for too long, cleanup stalls server-wide and problems will surface as vacuuming issues later. It is far better to unblock it early.

Details​

The panel shows the current oldest xid and how long it has been stuck. It also lists everything currently holding the horizon back — grouped by source: backend connections, replication connections, replication slots, and prepared transactions — so you can identify and clear the culprit.

Worker saturation​

PostgreSQL runs at most autovacuum_max_workers autovacuum workers at once. When every slot is continuously busy, tables that need vacuuming have to queue and cleanup falls behind. Sustained high saturation is a strong signal that autovacuum simply cannot keep pace with the current load.

Details​

Over a selectable 1-hour, 6-hour, or 24-hour window, the panel reports the average worker saturation, the fraction of time all workers were busy at once, the average number of active vacuums, and an estimate of the total time tables spent waiting for a free worker, alongside a chart of active workers over time.

Autovacuum configuration​

Autovacuum's behaviour is governed by a set of server parameters that control how often it triggers and how aggressively it runs.

Details​

This panel lists the autovacuum-related parameters in effect on the server, each with its current value and a short description of what it does, so you can review the configuration without leaving DBtune.

Table view​

The table view ranks every user table by how urgently it needs vacuuming and lets you drill into any one of them.

Vacuum priority​

Each table is scored by a single vacuum priority number — the most urgent reason it needs attention. It is the maximum of four separate scores, each replicating one of PostgreSQL's own autovacuum triggers:

  • Bloat score — dead rows accumulated relative to the vacuum threshold.
  • Insert score — rows inserted since the last vacuum (insert-heavy tables need periodic vacuuming for visibility-map maintenance even without dead rows).
  • Transaction ID freeze score — how close the table's oldest unfrozen row is to the mandatory freeze deadline.
  • MultiXact freeze score — the same, for the shared row-locking (multixact) ID space.

Each score is scaled so that a value above 1 means PostgreSQL's own condition for triggering a vacuum has been met. This mirrors the prioritisation approach introduced in PostgreSQL 19, applied consistently across all PostgreSQL versions DBtune supports. Once any score for a table exceeds 1, the table enters a "should be vacuumed" state, and DBtune tracks how long it stays there.

Details​

Opening a table shows all four scores individually, the underlying tuple counts (live, dead, inserted, frozen ages), and a status-history chart so you can see how each score has trended over time rather than just its latest value.

note

A table may have been vacuumed during the time DBtune reports it as waiting, without DBtune observing the priority drop back below 1. This can happen because:

  • DBtune samples at discrete intervals, so a busy table's score can climb back above 1 between the vacuum finishing and the next sample.
  • Vacuum was unable to clear the rows because of a stalled xmin horizon.

Table bloat​

Autovacuum's main day-to-day job is clearing out dead rows. For each table DBtune reports two related but distinct measures:

  • Simple bloat — the share of a table's rows that are dead (dead / (live + dead)). It is expected to rise and fall: active tables build up dead rows between vacuums and reset when autovacuum runs. What matters is the trend — bloat that drifts steadily upward, or fails to fall after a vacuum, points to autovacuum not working properly.
  • Live tuples per page — how densely live rows are packed on disk.

The distinction matters because autovacuum reclaims dead space for reuse within the table but almost never reduces the physical space usage of the table. So a table can have zero dead rows yet still occupy far more disk than its live rows need, with those live rows spread thinly across many pages — a low live-tuples-per-page. Once space is lost this way it is hard to win back: the usual remedies, VACUUM FULL and pg_repack, both rewrite the table and are intrusive operations. Ideally, the autovacuum should make sure that the live-tuples-per-pages never drops that low in the first place.

Known limitations​

We are actively developing this functionality and are aware of the following limitations:

  • The data collection agent holds open a connection to a single database, so table-level information is scoped to just that database.
  • We currently surface only ordinary user tables — TOAST tables, PostgreSQL system catalogs, and indexes are not yet covered.
  • Full support for PostgreSQL 18 tables requires agent version 1.2.2 or later, which adds the relallfrozen column used by the score calculations on PostgreSQL 18.
Last updated on