If there’s one PostgreSQL command every administrator eventually has to understand deeply, it’s VACUUM. It’s tied directly to how PostgreSQL’s storage engine handles updates and deletes, and neglecting it leads to some of the most common and most painful production problems a Postgres database can run into — table bloat, degraded query performance, and in the worst case, transaction ID wraparound emergencies.
In this article, I’ll explain why VACUUM exists in the first place, walk through its full syntax and options, cover autovacuum, and go through practical examples and troubleshooting scenarios you’re likely to run into.
Why VACUUM Exists: MVCC and Dead Tuples
PostgreSQL uses a concurrency model called MVCC (Multi-Version Concurrency Control). Instead of updating a row in place, an UPDATE in PostgreSQL actually creates a brand new version of the row and marks the old version as no longer current. A DELETE similarly doesn’t immediately remove a row — it just marks it as no longer visible to new transactions.
This design is what lets PostgreSQL give concurrent transactions consistent snapshots of data without heavy locking. But it comes with a cost: those old, no-longer-visible row versions — called “dead tuples” — don’t disappear on their own. They sit around taking up space until something cleans them up. That something is VACUUM.
Without regular vacuuming, a table can bloat significantly, meaning it consumes far more disk space than the actual live data requires, and queries slow down because they have to skip over all those dead rows during scans.
Basic Syntax
VACUUM [ ( option [, ...] ) ] [ table_name [ (column_name [, ...]) ] ];
If you run it with no table name, PostgreSQL vacuums every table in the current database that you have permission to vacuum:
VACUUM;
Or target a specific table:
VACUUM orders;
Key Options
VERBOSE
Prints detailed information about what the vacuum operation found and did — number of dead tuples removed, pages processed, and so on.
VACUUM (VERBOSE) orders;
ANALYZE
Runs ANALYZE immediately after vacuuming, updating the query planner’s statistics for the table in the same operation. This is extremely common to combine, since fresh statistics matter just as much as reclaimed space for good query performance.
VACUUM (ANALYZE) orders;
There’s also a shorthand for this combination:
VACUUM ANALYZE orders;
FULL
This is a much more aggressive form of vacuuming. Instead of just marking space as reusable, VACUUM FULL actually rewrites the entire table into a new, compact file on disk, physically reclaiming space back to the operating system.
VACUUM FULL orders;
The catch: it requires an ACCESS EXCLUSIVE lock for the duration, blocking all reads and writes, and it needs roughly double the table’s disk space temporarily (similar to CLUSTER, which it’s closely related to internally). This makes it something you generally reserve for maintenance windows on tables with severe bloat, not routine use.
FREEZE
Aggressively freezes tuples’ transaction IDs sooner than they’d normally be frozen, which helps stave off transaction ID wraparound issues. It’s roughly equivalent to running vacuum with vacuum_freeze_min_age set to zero for this operation.
VACUUM (FREEZE) orders;
SKIP_LOCKED
Skips any tables (in a multi-table or whole-database vacuum) that currently have a conflicting lock held by another process, rather than waiting for the lock.
VACUUM (SKIP_LOCKED);
PARALLEL
Lets you specify a degree of parallelism for the index-cleanup phase of vacuuming, potentially speeding up large vacuum operations on tables with multiple indexes.
VACUUM (PARALLEL 4) orders;
VACUUM vs. Autovacuum
It’s worth being clear about this up front: PostgreSQL has a background process called autovacuum that runs automatically, triggering VACUUM (and ANALYZE) operations on tables once they cross certain thresholds of dead tuples or time since last analysis. For most workloads, autovacuum handles the bulk of routine cleanup without any manual intervention needed.
That said, manual VACUUM still matters:
- Autovacuum’s default thresholds are sometimes too conservative for very high-churn tables, leading to bloat accumulating faster than autovacuum keeps up.
- Certain operations, like
VACUUM FULL, are never run automatically by autovacuum and always require manual intervention. - During or after unusually large batch updates or deletes, manually triggering a vacuum can be worthwhile rather than waiting for autovacuum’s next scheduled pass.
You can check autovacuum activity and settings with:
SHOW autovacuum;
SELECT relname, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_autovacuum NULLS FIRST;
Practical Examples
Example 1: Basic Manual Vacuum With Statistics Update
VACUUM (VERBOSE, ANALYZE) orders;
A good general-purpose command to run manually after a large batch job that inserted, updated, or deleted a significant portion of a table.
Example 2: Reclaiming Disk Space From a Severely Bloated Table
VACUUM FULL VERBOSE orders;
Use this sparingly, and only during a maintenance window, since it locks the table completely for its duration. This is the right tool when regular vacuuming has kept the table functionally healthy but disk usage has grown far beyond what the live data actually needs.
Example 3: Vacuuming a Specific Column Set (Rarely Needed, But Supported)
VACUUM (ANALYZE) orders (customer_id, order_date);
This narrows the ANALYZE portion to specific columns, useful if you only need updated statistics for particular columns rather than the whole table.
Example 4: Whole-Database Vacuum After a Major Migration
VACUUM (VERBOSE, ANALYZE);
Running this without a table name processes every table in the database. This can take a long time on large databases, so it’s typically scheduled during low-traffic periods.
Example 5: Checking for Transaction ID Wraparound Risk
This is one of the most important health checks in PostgreSQL, since ignoring it can eventually force the database into a forced shutdown to prevent data corruption:
SELECT
datname,
age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;
If xid_age is climbing toward the autovacuum_freeze_max_age threshold (default 200 million), it’s a sign that vacuuming — specifically freezing — isn’t keeping up, and you may need to manually run VACUUM (FREEZE) on the oldest, most neglected tables.
Example 6: Identifying Tables Most in Need of a Manual Vacuum
SELECT
relname,
n_dead_tup,
n_live_tup,
round(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 3) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
Tables with a high dead-to-live tuple ratio are strong candidates for a manual vacuum pass, especially if autovacuum hasn’t caught up recently.
Common Use Cases
- Routine cleanup of dead tuples from regular update/delete activity — mostly handled by autovacuum, but worth understanding.
- Reclaiming disk space from tables that have shrunk significantly after a large deletion, using
VACUUM FULL. - Refreshing planner statistics after significant data changes, via the
ANALYZEoption. - Preventing transaction ID wraparound on long-lived, high-churn databases through periodic freezing.
- Post-migration or post-bulk-load cleanup, especially after large
COPYor bulkUPDATE/DELETEoperations.
Troubleshooting Common Issues
VACUUM Is Taking a Very Long Time
Large tables with lots of dead tuples and multiple indexes can take a while to vacuum. Use VERBOSE to monitor progress, and consider pg_stat_progress_vacuum for a live view of an in-progress vacuum:
SELECT * FROM pg_stat_progress_vacuum;
Table Bloat Persists Even After Vacuuming
Regular VACUUM marks space as reusable for future inserts and updates within the same table — it doesn’t shrink the file on disk or return space to the operating system. If you need the file itself to shrink, you need VACUUM FULL (with its locking trade-off) or a tool like pg_repack, which achieves a similar result with less locking impact.
“database is not accepting commands to avoid wraparound data loss” Error
This is a serious situation — it means autovacuum wasn’t able to keep up, and PostgreSQL has forced the database into a read-only/emergency state to protect data integrity. Recovery typically involves connecting as a superuser and running an aggressive manual vacuum on the affected tables immediately:
VACUUM (FREEZE, VERBOSE) affected_table;
Getting into this state at all usually points to autovacuum being disabled, misconfigured, or unable to complete due to long-running transactions holding back cleanup — worth investigating the root cause once the immediate emergency is resolved.
Autovacuum Seems to Never Run on a Specific Table
Check whether a long-running transaction elsewhere is holding back vacuum progress (vacuum can’t remove tuples that might still be visible to an old, still-open transaction):
SELECT pid, state, xact_start, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY xact_start;
Long-idle-in-transaction sessions are a very common, often overlooked cause of vacuum falling behind.
VACUUM FULL Fails Due to Insufficient Disk Space
Since it builds a new copy of the table before dropping the old one, you need roughly double the current table size in free space. Check available space beforehand and consider pg_repack as an alternative if you can’t spare that much temporarily.
Best Practices
- Don’t disable autovacuum unless you have a very specific reason and a solid manual vacuuming strategy to replace it — this is one of the most common causes of serious production incidents.
- Tune autovacuum settings per table for high-churn tables, rather than relying solely on database-wide defaults, using
ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = ...)for tables that need more aggressive cleanup. - Monitor
n_dead_tupand transaction ID age regularly, not just after something goes wrong. - Reserve
VACUUM FULLfor genuine maintenance windows, given its exclusive lock and disk space requirements. - Watch for long-running or idle-in-transaction sessions, since these silently prevent vacuum from doing its job effectively.
- Combine
VACUUMwithANALYZEroutinely, since stale statistics can hurt query performance just as much as bloat does. - Set up alerting on transaction ID age well before it approaches
autovacuum_freeze_max_age, so you have time to react before an emergency shutdown scenario.
Tuning Autovacuum for Specific Tables
While database-wide autovacuum settings work reasonably well as defaults, individual tables often have very different write patterns that justify their own tuned settings. PostgreSQL lets you override autovacuum behavior per table using storage parameters:
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_vacuum_threshold = 1000,
autovacuum_analyze_scale_factor = 0.02
);
The default autovacuum_vacuum_scale_factor of 0.2 means a table needs roughly 20% of its rows to become dead tuples before autovacuum considers it due for a cleanup pass. For a huge table with tens of millions of rows, that 20% threshold represents an enormous number of dead tuples accumulating before cleanup even triggers, during which time bloat and query performance can degrade noticeably. Lowering the scale factor for specifically high-churn, large tables — while leaving smaller, low-churn tables on the sensible defaults — is a common and effective tuning strategy.
Understanding Freeze and Transaction ID Wraparound More Deeply
PostgreSQL’s MVCC system uses 32-bit transaction IDs (XIDs) to determine which row versions are visible to which transactions. Because this counter is finite and wraps around, PostgreSQL needs to periodically “freeze” old row versions, marking them as permanently visible regardless of XID comparison, so the wraparound doesn’t cause old data to suddenly appear to be from the future (which would make it invisible or, worse, misinterpreted).
Normal VACUUM operations handle freezing as part of their regular work, guided by vacuum_freeze_min_age. The FREEZE option simply tells that particular vacuum run to be more aggressive about it immediately, which is useful right before a period where you know the table won’t be vacuumed again for a while, or when you’re proactively responding to a climbing transaction ID age warning.
For a genuinely deep understanding of how close a database is to a wraparound-related problem, this query gives more actionable detail than the simpler age check shown earlier:
SELECT
datname,
age(datfrozenxid) AS xid_age,
2147483647 - age(datfrozenxid) AS xids_remaining
FROM pg_database
ORDER BY xid_age DESC;
Watching xids_remaining trend downward over time, rather than just the raw age, gives a clearer sense of how much runway remains before intervention becomes urgent.
VACUUM and Replication Considerations
If you’re running streaming replication, it’s worth knowing that a long-running query on a replica (or a replication slot that’s fallen behind) can hold back vacuum’s ability to clean up dead tuples on the primary, since PostgreSQL needs to ensure that row versions still potentially visible to replicas aren’t removed prematurely. This shows up as unexpectedly high table bloat on the primary even when nothing looks wrong there directly. Checking replication slot lag is a worthwhile step when vacuum seems to be falling behind for no obvious local reason:
SELECT slot_name, active, restart_lsn,
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
FROM pg_replication_slots;
An inactive or heavily lagging replication slot is a common, easy-to-overlook cause of vacuum-related bloat problems on otherwise healthy-looking primaries.
Controlling Vacuum’s Resource Impact With Cost-Based Delays
Running a large manual vacuum on a busy production table can compete for I/O and CPU resources with normal application traffic. PostgreSQL includes a cost-based throttling mechanism specifically to limit this impact, governed by a handful of settings:
SHOW vacuum_cost_delay;
SHOW vacuum_cost_limit;
vacuum_cost_limit defines a budget of “cost units” vacuum can accumulate (based on how many pages it reads from cache, reads from disk, or dirties) before pausing briefly, with the pause length controlled by vacuum_cost_delay. Higher limits and lower delays mean vacuum runs faster but has more impact on concurrent traffic; lower limits and higher delays mean it’s gentler but takes longer to finish.
For a specific, unusually large or urgent manual vacuum where you want it to finish as quickly as possible and are willing to accept more resource contention temporarily, you can override these settings for just that session:
SET vacuum_cost_delay = 0;
VACUUM (VERBOSE) orders;
Conversely, if you need a large vacuum to run more gently in the background without disrupting a latency-sensitive production workload, you can loosen the throttling instead:
SET vacuum_cost_delay = 20;
SET vacuum_cost_limit = 200;
VACUUM (VERBOSE) orders;
Autovacuum has its own separate, similarly named settings (autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit) that can be tuned independently of manual vacuum runs, which is worth knowing if you want autovacuum to behave more or less aggressively than your manual vacuum invocations.
Watching a Long-Running Vacuum in Real Time
Similar to other long-running maintenance operations, PostgreSQL exposes a dedicated progress view for vacuum operations:
SELECT
pid,
relid::regclass,
phase,
heap_blks_total,
heap_blks_scanned,
heap_blks_vacuumed,
round(100.0 * heap_blks_scanned / NULLIF(heap_blks_total, 0), 1) AS percent_scanned
FROM pg_stat_progress_vacuum;
The phase column reports exactly which stage the vacuum is currently in — scanning heap, vacuuming indexes, vacuuming heap, or cleaning up, among others — which is genuinely useful for estimating remaining time on a large table, or confirming a vacuum that’s been running for a while is still actively making progress rather than stuck.
A Realistic Manual Vacuum Maintenance Routine
Pulling several of these pieces together, a reasonable manual maintenance routine for a specific table you’ve identified as needing attention — via high dead tuple counts, climbing bloat, or a maintenance ticket — might look like this:
-- Check current state first
SELECT n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables WHERE relname = 'orders';
-- Run a standard vacuum with fresh statistics
VACUUM (VERBOSE, ANALYZE) orders;
-- Confirm improvement
SELECT n_live_tup, n_dead_tup, last_vacuum
FROM pg_stat_user_tables WHERE relname = 'orders';
If dead tuple counts remain stubbornly high even after this, and the table’s on-disk size has grown well beyond what the live row count would suggest, that’s the point where a scheduled VACUUM FULL (or pg_repack, for a less disruptive alternative) genuinely becomes worth the operational cost of the maintenance window it requires.
Wrapping Up
VACUUM isn’t optional maintenance — it’s a core part of how PostgreSQL’s storage model works, tied directly to MVCC. Autovacuum handles the vast majority of this automatically for most workloads, but understanding what it’s doing, when to intervene manually, and how to recognize warning signs like climbing transaction ID age or rapidly accumulating dead tuples will save you from some of the most painful production incidents PostgreSQL administrators run into.
Treat regular monitoring of vacuum-related statistics as part of routine database health checks, not something you only think about after performance has already degraded.