Monitor autovacuum

Autovacuum does three jobs: it reclaims space from dead tuples, maintains the visibility map that lets PostgreSQL skip pages during scans, and advances the transaction ID horizon to prevent wraparound. When it falls behind, the effects surface everywhere else, as CPU, as IOPS, as slow queries, which is why this guide is often the real destination of an investigation that started somewhere else.

Wraparound is the one failure mode here that stops writes entirely. Everything else degrades gradually.

Symptoms this guide explains

• Query plans degrading over time without query changes
• Table or index sizes growing faster than the data in them
• CPU or IOPS rising while workload stays flat
• Transaction ID age climbing toward wraparound thresholds
• Storage consumption that DELETE doesn't reclaim

Before you start

Enable PostgreSQL Server Logs, Autovacuum and schema statistics, and Remaining transactions; set log_autovacuum_min_duration to a non-negative value; and enable metrics.collector_database_activity for the enhanced metrics tab. See Use the troubleshooting guides.

Scope and limits

Read these before comparing numbers against your own queries:

• The guide shows only databases and schemas with more than 100 live tuples and more than 1,000 dead tuples. It filters out small tables, so totals don't match pg_stat_user_tables.
• The guide analyzes up to 30 databases per server, or 10 on Burstable, in database creation (OID) order. For servers that go beyond the cap, the guide silently excludes databases but warns you when this happens.
• The minimum time range is one hour.

How this guide is organized

A Database selector at the top of the guide scopes every tab to all databases or to one you choose.

Walkthrough: bloat growing on a busy table

1. Quantify it. The Bloat tab shows one database at a bloat ratio above 100%, meaning more dead tuples than live ones. That's severe, not marginal.

2. Understand the driver. Tuples shows heavy update activity on that database. Every UPDATE in PostgreSQL creates a new row version and marks the old one dead, so update-heavy tables generate bloat continuously by design. The question is whether autovacuum is keeping pace, not whether bloat is being created.

3. Check for gaps. Vacuum and analyze shows a meaningful percentage of tables never autovacuumed. Something is preventing autovacuum from reaching them.

4. Check capacity. Autovacuum activity shows a worker saturation banner, meaning all autovacuum_max_workers were busy at least once. Work was queued behind the available workers.

5. Find the cause. Autovacuum per table shows a small number of very large tables occupying workers for long durations, starving everything else. It also shows index scans above 1 on those tables, meaning maintenance_work_mem is too small and each vacuum is making multiple passes over the indexes, doing several times the necessary work.

6. Rule out the worse explanation. Enhanced metrics shows the oldest backend xmin advancing normally, so cleanup isn't blocked. This is a throughput problem, not a blocked-horizon problem. If xmin had been flat, you'd stop here and go to Troubleshoot autovacuum blockers and wraparound risk instead.

7. Act. You raise maintenance_work_mem so vacuums complete in a single index pass, apply aggressive per-table autovacuum settings to the few large tables so they're vacuumed more often and in smaller increments, and raise autovacuum_max_workers alongside autovacuum_vacuum_cost_limit so added workers actually get I/O budget.

The branch point at step 6 is the one to internalize: advancing xmin means a throughput problem; flat xmin means a blocker. They need completely different fixes.

Tab reference

Bloat

The bloat ratio is dead_tuples / live_tuples * 100. It can exceed 100%:

Drill from server-wide, into a database, then per schema. A high schema-level ratio has exactly two explanations, and you must distinguish them:

• A blocker is preventing cleanup. Go to Troubleshoot autovacuum blockers and wraparound risk.
• Autovacuum can't keep up. Tune it, see Autovacuum tuning in Azure Database for PostgreSQL flexible server.

Tuples

Live versus dead tuples, alongside the insert, update, and delete activity producing them:

• Updates are the biggest bloat source. Each one writes a new row version and marks the old dead. Tables with many indexes are worse, since every index needs updating too.
• Deletes leave space that isn't reusable until vacuum processes it.
• Inserts contribute when a table is already fragmented.

A rising dead-tuple trend that pushes the bloat ratio up is the signal to act.

Vacuum and analyze

Tracks how many tables were vacuumed and analyzed per database. Watch Tables never autovacuumed [%] and Tables never autoanalyzed [%].

Common causes of a high never-autovacuumed percentage:

1. Too many databases for autovacuum to service. Autovacuum tuning in Azure Database for PostgreSQL flexible server.
2. Autovacuum cleaning too slowly. Autovacuum tuning in Azure Database for PostgreSQL flexible server.
3. Workers monopolized by large tables, starving others. Apply table-specific settings.
4. A value of zero can mean autovacuum is disabled outright. Check the banners.
5. After an upgrade or migration, statistics start empty. Run a database-wide manual vacuum and analyze before resuming the workload.

For a high never-autoanalyzed percentage, consider more aggressive autovacuum_analyze_scale_factor and autovacuum_analyze_threshold. Stale statistics cause bad plans, which is often the first symptom users actually notice.

Autovacuum activity

Server-wide autovacuum runs in the window. Use the trend chart to look for spikes, the trigger breakdown to separate routine runs from anti-wraparound and failsafe runs, and the per-database grid to see where the load is.

Anti-wraparound and failsafe runs aren't routine. They mean normal cleanup already failed to keep up and PostgreSQL is now protecting itself.

If the worker saturation banner appears, autovacuum hit autovacuum_max_workers at least once and work waited. Raise autovacuum_max_workers together with autovacuum_vacuum_cost_limit. The cost limit is shared across workers, so adding workers without raising it just divides the same I/O budget into smaller pieces.

Autovacuum per table

Table-level detail. Requires log_autovacuum_min_duration non-negative.

Enhanced metrics

Requires metrics.collector_database_activity.

Maximum used transaction IDs. PostgreSQL has roughly 2 billion usable transaction IDs before wraparound, at which point it stops accepting writes:

On PostgreSQL 14 and later, the failsafe engages at vacuum_failsafe_age, 1.6 billion by default, bypassing cost throttling entirely to avoid shutdown. If the failsafe is running, you're close to an outage.

Oldest backend xmin. The oldest snapshot horizon across backends. Vacuum can't remove any row version newer than this, so a stalled xmin blocks cleanup no matter how often autovacuum runs:

• Advancing steadily with throughput is healthy.
• Flat or barely moving indicates a blocker. Most likely a long-running transaction, an idle-in-transaction session, an orphaned prepared transaction, or an inactive replication slot. Go to Troubleshoot autovacuum blockers and wraparound risk.

This single metric is the fastest way to tell a throughput problem from a blocked one.

Vacuum throttling overview

Cost-based throttling pauses vacuum workers. You can see this pause through the VacuumDelay wait. For finer resolution, use pgms_wait_sampling. For a coarser five-minute view, use pg_stat_activity snapshots. Some throttling is by design. It stops vacuum from starving your workload of I/O. Tune only when throttling is sustained and coincides with growing bloat, rising XID age, or autovacuum falling behind.

• Prefer per-table settings for a few high-traffic tables over changing server-wide defaults.
• Change one thing at a time and confirm that throttling drops without introducing CPU or I/O pressure.

Configuration

Current autovacuum parameters, defaults, and modifications.

Operational guidance autovacuum can't provide

Some situations need scheduled maintenance regardless of tuning. Use pg_cron or your own scheduler.

Bloat that tuning alone won't fix:

• Blocked cleanup. No autovacuum setting overcomes a pinned xmin horizon. See Troubleshoot autovacuum blockers and wraparound risk.

• TOAST tables. Large text and jsonb values live in a separate TOAST table with its own toast.autovacuum_* storage parameters. Bloat there's invisible in normal table statistics.

• Index bloat. VACUUM reclaims table space but doesn't shrink indexes. Run REINDEX CONCURRENTLY periodically on heavily updated indexes. Tuning preferences:

• Prefer per-table settings for the few busy tables that need them: ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 1000);

• Lower fillfactor to roughly 80 or 90 on heavily updated tables to enable more HOT updates, which reduces both bloat and index maintenance.

• Don't disable autovacuum. It doesn't prevent forced anti-wraparound vacuums, and bloat accumulates until something worse happens. Tune it instead.

• Set log_autovacuum_min_duration to something like 10000 (10 seconds) to capture long runs, and watch n_dead_tup, n_mod_since_analyze, last_autovacuum, and last_autoanalyze in pg_stat_user_tables.

What good looks like

What this guide can't tell you

• Index bloat. Only table-level bloat is shown. Assess indexes separately.
• TOAST bloat. Not reflected in the main table's statistics.
• Small tables. Filtered out below the tuple thresholds.
• Databases past the cap. Silently excluded in OID order, though the guide warns you.
• Effective per-table settings. Only server-level configuration is displayed.
• What is blocking cleanup. This guide tells you cleanup is blocked. Troubleshoot autovacuum blockers and wraparound risk tells you what's doing it.

Related content

• Use the troubleshooting guides in Azure Database for PostgreSQL flexible server
• Troubleshoot autovacuum blockers and wraparound risk
• Telemetry reference for the troubleshooting guides
• Autovacuum tuning in Azure Database for PostgreSQL flexible server
• Routine vacuuming (PostgreSQL documentation)