PostgreSQL: VACUUM and Autovacuum in 60 seconds
# View tables with the most dead tuples
SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
# Execute VACUUM and ANALYZE on a specific table, with detailed output
VACUUM (VERBOSE, ANALYZE) my_table;
# Set a more aggressive autovacuum for a specific table
ALTER TABLE my_table SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_threshold = 500);
# Check the age of the oldest transaction ID per database (wraparound risk)
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;
# Show the maximum number of globally configured autovacuum workers
SHOW autovacuum_max_workers;
# Monitor the progress of an ongoing VACUUM
SELECT pid, datname, relid::regclass AS table_name, phase, heap_blks_scanned, num_dead_tuples FROM pg_stat_progress_vacuum;
Managing the VACUUM command and the autovacuum subsystem is one of the most critical aspects of PostgreSQL administration. It simultaneously impacts performance, disk space consumption, and the long-term logical integrity of the database. The PostgreSQL engine, based on the MVCC model, does not physically delete modified or deleted rows. Instead, it marks them as “dead” and defers their cleanup to garbage collection processes, which are implemented by the VACUUM and autovacuum commands. Incorrect configuration or insufficient monitoring can lead to table bloat, significant performance degradation, and even the extreme risk of transaction ID wraparound, where the system is forced to halt writes to protect data consistency. This article will explore why PostgreSQL needs VACUUM, how to identify tables with too many dead rows, the operational differences between VACUUM, VACUUM ANALYZE, and VACUUM FULL, why the latter blocks tables, how to tune autovacuum at the table and cluster level, which parameters govern wraparound risk, and which tools allow monitoring autovacuum activity over time, with a focus on real-world scenarios, known pitfalls, and vendor recommendations.
Reference versions: PostgreSQL 16 · documentation consulted in November 2026
Prerequisites
To follow the procedures and understand the concepts presented, a basic understanding of PostgreSQL administration and relational database concepts is required. It is assumed that you have access to a PostgreSQL environment with superuser privileges or a role with permissions to execute maintenance commands and query system views (pg_stat_user_tables, pg_stat_activity, pg_stat_progress_vacuum, pg_database). The pgstattuple extension must be installed and available in the database for more detailed analysis queries.
Why PostgreSQL Needs VACUUM
MVCC, Dead Tuples, and Disk Space Growth
To understand why VACUUM is indispensable in PostgreSQL, we must start with the concurrency model adopted by the system: Multi-Version Concurrency Control (MVCC). In an MVCC system, every modification to a row does not overwrite the existing data but creates a new version of the row, leaving the previous one in the table until it is certain that no transaction can see it as valid. PostgreSQL implements this behavior by storing both current and obsolete row versions in the table, distinguishing them using visibility information based on transaction IDs (XIDs) and transaction snapshots.
This model has the great advantage of allowing consistent and non-blocking reads even in the presence of concurrent writes, because a query can see a stable set of rows without having to wait for modifying transactions to finish. However, this approach has an obvious cost: rows that are no longer visible to any active transaction remain physically in the table file as dead tuples and occupy disk space. Without a cleanup mechanism, this accumulation would lead to potentially unlimited growth of table files, expansion of indexes, and consequently, progressive performance degradation due to increased I/O and less efficient execution plans.
To prevent this, PostgreSQL introduces the VACUUM command, whose task is to scan tables, identify tuples that are no longer reachable by any transaction, and free up the space they occupy, making it available again for future insertions. In normal operation, tuples deleted by a DELETE or made obsolete by an UPDATE are not immediately removed but are marked as no longer visible, and VACUUM will perform the actual cleanup later. This deferment reduces contention between read and write operations but shifts the responsibility of controlling disk space and table health to the vacuuming process.
The need for VACUUM is therefore not optional but a structural feature of PostgreSQL: the official documentation explicitly states that PostgreSQL databases require periodic maintenance known as vacuuming, which must be performed on every table for several reasons, including the recovery or reuse of space occupied by updated or deleted rows and the updating of statistics for the query planner. In modern versions (PostgreSQL 14, 15, 16, 17, 18), the regular execution of VACUUM is so central that the system includes an autovacuum daemon dedicated to automating this maintenance.
The Role of the VACUUM Command in Routine Maintenance
The PostgreSQL SQL documentation defines the VACUUM command as an operation that “reclaims storage occupied by dead tuples.” When INSERT, UPDATE, and DELETE statements operate, previous tuples remain in the table file until a VACUUM is executed; consequently, VACUUM must be run periodically, especially on tables subjected to frequent updates.
The standard VACUUM comes in two main syntaxes: a modern syntax that uses a list of options in parentheses and a historical syntax with separate options. For example, a typical and recommended form for routine maintenance is:
VACUUM (VERBOSE, ANALYZE) mytable;
In this case, the VERBOSE mode provides a detailed report on the vacuum activity for each table, while the ANALYZE option instructs the system to perform, after cleanup, an update of the data distribution statistics used by the query planner to optimize execution plans. Executing VACUUM ANALYZE on highly dynamic tables is a recommended practice because it allows, in a single operation, to clean up dead tuples and keep planning statistics up-to-date.
Historical documentation (e.g., PostgreSQL 6.5) already clarified that VACUUM served to reclaim storage and gather information for the optimizer, opening every “class” in the database, removing records of aborted transactions, and updating statistics on system catalogs, including the number of tuples and pages stored for each table. Although that version is now unsupported, the concept remains valid and consistent with contemporary documentation: VACUUM is both a garbage collector and a statistics collection mechanism, and it must be run regularly to keep the database fast in query processing.
An important aspect highlighted by recent documentation is that standard VACUUM can be executed in parallel with normal production operations, such as SELECT, INSERT, UPDATE, and DELETE, without blocking table definitions. During a standard VACUUM, it is not possible to modify the table definition with commands like ALTER TABLE, but read and write operations on data can continue normally, albeit with a potential increase in I/O traffic. This feature makes standard VACUUM suitable for routine maintenance, even in environments with continuous load, while the VACUUM FULL form, as we will see, has more invasive impacts.
Standard Vacuum and VACUUM FULL: General Overview
Official documentation clearly distinguishes between two variants of VACUUM: standard VACUUM and VACUUM FULL. The first is designed to be run regularly and in parallel with normal database activities, while the second is a heavier operation that rewrites the entire table content into a new disk file, with the goal of returning unused space to the operating system.
Standard VACUUM removes dead row versions and marks the space as available for future insertions, but it normally does not return space to the operating system, except in the particular situation where one or more pages at the end of the table file become completely free and an exclusive lock on the table can be easily obtained. This means that even if a table has been internally cleaned, the disk file may maintain the same size, while the internal space is reused for new tuples. This strategy is designed to balance efficiency and concurrency, avoiding costly file rewrites in most cases.
VACUUM FULL, on the other hand, “rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system.” This form of vacuum is much slower, requires additional disk space because it writes a new copy of the table before the old one can be released, and needs an ACCESS EXCLUSIVE lock on the table, which prevents any concurrent use of it during the operation. For these reasons, the documentation recommends using VACUUM FULL only when it is necessary to reclaim a significant amount of space from a severely bloated table and not as part of routine maintenance.
The official PostgreSQL wiki emphasizes that VACUUM and VACUUM FULL “are actually quite different and should not be confused” and that, in most cases, standard VACUUM should be used instead of VACUUM FULL, unless there is an explicit need to return space to the operating system or to repair a severely bloated table and index due to insufficient autovacuum. Progressive improvements to autovacuum across PostgreSQL versions have made the need for VACUUM FULL less frequent, shifting the focus to proper autovacuum configuration rather than resorting to drastic manual interventions.
Finding Tables with Too Many Dead Rows
Indicators of Bloat and Dead Tuple Accumulation
For a system administrator or DBA, one of the fundamental tasks is to identify which tables or indexes are accumulating an excessive number of dead tuples, leading to bloat and potential performance degradation. PostgreSQL provides several tools to assess this phenomenon, starting from both cumulative statistics views and specialized extensions like pgstattuple.
The pg_stat_user_tables (or pg_stat_all_tables) view contains valuable metrics, including the number of tuples modified since the last VACUUM or ANALYZE (n_mod_since_analyze), the number of dead tuples (n_dead_tup), the timestamp of the last autovacuum (last_autovacuum), and the last autoanalyze (last_autoanalyze). This information helps determine if autovacuum is keeping pace with the table modification rate or if some tables are accumulating too many dead tuples between maintenance cycles. The cumulative statistics documentation highlights that these views are specifically designed to monitor server activity and include fields related to VACUUM and autovacuum.
A sharp increase in n_dead_tup on a table, without corresponding autovacuum executions recorded in last_autovacuum, is often a sign of growing bloat and an undersized autovacuum or one hindered by locks and long-running transactions. Microsoft’s guide for Azure Database for PostgreSQL, for example, recommends setting log_autovacuum_min_duration to log automatic vacuums that exceed a certain duration and monitoring n_dead_tup, n_mod_since_analyze, last_autovacuum, and last_autoanalyze in pg_stat_user_tables to identify autovacuum issues that fail to keep up.
In addition to dead tuple counters, the physical sizes of tables and indexes can be observed, comparing the growth over time of their pg_relation_size with the number of tuples actually present, to identify tables where allocated space increases much faster than actual data. In conjunction, progress tools like pg_stat_progress_vacuum allow checking if VACUUM is actually cleaning table blocks or if it is systematically interrupted by locks or cost-based throttling. Read also: Linux: Free Up Disk Space with 12 Commands (SysAdmin Guide 2026)
Using Monitoring Views: pg_stat_user_tables, pg_stat_activity, pg_stat_progress_vacuum
PostgreSQL includes an extensive statistics collection system, known as the statistics collector, which populates several views useful for monitoring the activity and progress of maintenance commands. Among these, two views are particularly relevant for VACUUM/autovacuum: pg_stat_activity and pg_stat_progress_vacuum, alongside table-specific views like pg_stat_user_tables.
The pg_stat_activity view shows one row for each server process, indicating the current state (e.g., active, idle, idle in transaction, idle in transaction (aborted)), the command being executed, the transaction start timestamp (xact_start), and other relevant details. Through this view, it is possible to identify sessions that remain “idle in transaction” for long periods, blocking the VACUUM cutoff and preventing the removal of older dead tuples, with direct effects on both bloat and the risk of transaction ID wraparound. Long inactive transactions, highlighted by states like idle in transaction, are indeed one of the most notorious pitfalls in PostgreSQL, and the documentation explicitly recommends monitoring them and preventing them from delaying VACUUM’s progress for extended periods.
The pg_stat_progress_vacuum view was introduced to report the progress of VACUUM for each backend, including autovacuum workers. When VACUUM is running, this view contains one row for each process performing vacuum, with columns such as pid (process ID), datid and datname (affected database), relid (table OID), and various counters for the total number of heap blocks (heap_blks_total), scanned blocks (heap_blks_scanned), vacuumed blocks (heap_blks_vacuumed), number of vacuum cycles on indexes (index_vacuum_count), and number of dead tuples in memory (num_dead_tuples). The phase column indicates the current VACUUM phase, such as scanning heap, vacuuming indexes, vacuuming heap, cleaning up indexes, truncating heap, and performing final cleanup, allowing interpretation of how much work has already been completed.
These counters allow evaluating whether VACUUM is actually progressing on the table or if it is blocked, for example, in the heap scanning phase with minimal progress, which could signal I/O contention or excessive cost-based throttling. It is important to note that VACUUM FULL progress is not reported in pg_stat_progress_vacuum but in pg_stat_progress_cluster, because VACUUM FULL is internally treated similarly to CLUSTER. This means that, to monitor a VACUUM FULL, one must consult pg_stat_progress_cluster, which has a different semantic, more oriented towards physical table rewriting operations.
Finally, the pg_stat_user_tables view provides cumulative statistics for each user table, including the number of manual vacuums (vacuum_count) and automatic vacuums (autovacuum_count), the last autovacuum (last_autovacuum), the last autoanalyze (last_autoanalyze), and counters for modified and dead rows. By cross-referencing this data with autovacuum logs and pg_stat_progress_vacuum, a DBA can build a complete picture of which tables are suffering from bloat, which have overly conservative autovacuum parameters, and which are hindered by locks or long-running transactions. Read also: mysqldump Backup: Consistency & MySQL/MariaDB Restore
Using pgstattuple and pgstattuple_approx
To precisely quantify the percentage of dead tuples and free space in a table, PostgreSQL provides the pgstattuple extension, which offers functions to obtain tuple-level statistics. In PostgreSQL 8.4 and later documentation, pgstattuple is presented as a tool that can “help users to determine whether vacuum is necessary or not,” returning the physical length of the relation, the number and length of live tuples, dead tuples, and free space, expressed in both bytes and percentages.
The pgstattuple(regclass) function accepts the name (optionally schema-qualified) or OID of a relation and returns a record with columns such as table_len (physical table length in bytes), tuple_count (number of live tuples), tuple_len (total length of live tuples), tuple_percent (percentage of live tuples), dead_tuple_count (number of dead tuples), dead_tuple_len (total length of dead tuples), dead_tuple_percent (percentage of dead tuples), free_space (total free space), and free_percent (percentage of free space).
It is important to note that pgstattuple acquires only a read lock on the relation, so the results do not represent an instantaneous snapshot and can be influenced by concurrent updates during the scan. Furthermore, in newer versions, pgstattuple judges a tuple “dead” based on the internal HeapTupleSatisfiesDirty function, while older versions used HeapTupleSatisfiesNow. This difference reflects improvements in visibility semantics, but for the end-user, the concept remains: pgstattuple measures tuples that would not be considered visible by a “normal” transaction.
The pgstattuple_approx function was introduced to provide a lighter version that avoids a full-table scan, returning an exact count of dead tuples and an estimate of the number and size of live tuples and free space. The documentation highlights that, unlike pgstattuple, the _approx version estimates the total number of live tuples based on the number of scanned pages and tuples, in the same way that VACUUM estimates pg_class.reltuples. This allows pgstattuple_approx to be used on very large tables with less performance impact, accepting a slight approximation on live tuples and free space.
A fundamental caveat is that, for security reasons, in recent versions, the execution of pgstattuple functions is limited to the pg_stat_scan_tables role, which has pre-assigned EXECUTE privileges. This means that, in managed environments, the DBA must ensure they have the necessary permissions or delegate the use of pgstattuple to appropriate roles, maintaining control over the potential impact of detailed scans on large tables.
Examples of Practical Queries to Identify Problematic Tables
A common operational approach to identify tables with an excess of dead tuples is to combine system views and pgstattuple functions with ad-hoc monitoring queries. A typical query to highlight tables with high n_dead_tup and infrequent autovacuum might be:
SELECT
relname,
n_dead_tup,
n_mod_since_analyze,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
This query leverages the pg_stat_user_tables view to show the twenty tables with the highest number of dead tuples, along with the number of modifications since the last analyze and the last autovacuum and autoanalyze timestamps, allowing for quick identification of candidates for autovacuum tuning or manual vacuuming. By observing tables with very high n_dead_tup and a last_autovacuum far in the past, one might suspect that the autovacuum threshold and scale factor are too high, or that locks and long transactions are preventing autovacuum from running.
To check the actual percentage of dead tuples and free space, you can run:
SELECT *
FROM pgstattuple('public.my_table');
This will provide a record indicating dead_tuple_percent and free_percent, which can be used to assess how bloated the table truly is and whether a simple standard VACUUM or, in extreme cases, a VACUUM FULL is justified. Since pgstattuple performs a full-table scan with only a read lock, this operation should be planned for very large tables during periods of moderate load, but it provides a reliable reference for deciding the maintenance strategy.
During the execution of a manual or automatic VACUUM on a suspicious table, it is useful to monitor pg_stat_progress_vacuum:
SELECT
pid,
datname,
relid::regclass AS table_name,
phase,
heap_blks_total,
heap_blks_scanned,
heap_blks_vacuumed,
index_vacuum_count,
num_dead_tuples
FROM pg_stat_progress_vacuum;
This query shows the real-time progress of vacuum processes, revealing whether the table is actually being cleaned and how much work is required on the indexes. For example, a heap_blks_scanned that grows slowly and a high num_dead_tuples can indicate that VACUUM is doing intensive work and might be subject to cost-based throttling, while a rapid completion with few dead tuples suggests that the intervention is relatively light.
Ultimately, by combining these queries with systematic observation of autovacuum logs, it is possible to build a map of which tables are the primary “sources of bloat” in the PostgreSQL cluster and intervene with targeted tuning instead of applying generic policies that might over- or under-optimize vacuuming behavior.
VACUUM, VACUUM ANALYZE, and VACUUM FULL
Semantics of the VACUUM Command
The modern syntax of VACUUM, as documented in recent PostgreSQL versions (e.g., 16 and 18), allows specifying a set of options in parentheses, followed by a list of tables and, optionally, columns to analyze. A formal example is:
VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ];
or, in the historical syntax:
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ];
In the absence of a table list, VACUUM processes all tables and materialized views in the current database for which the user has vacuum permissions. If a list is provided, VACUUM is limited to the specified tables, allowing targeted maintenance on particularly dynamic or problematic tables. The VACUUM ANALYZE variant combines the garbage collection operation with statistics gathering, making it a convenient form for periodic maintenance scripts.
Standard VACUUM, without the FULL option, performs dead tuple cleanup and makes the internal space available to the table’s page allocation system, but, as already mentioned, it does not perform a complete file rewrite. The operation respects concurrency with other queries: while it is running, commands like SELECT, INSERT, UPDATE, and DELETE continue to function normally, although a portion of the system’s I/O is dedicated to scanning and cleaning heap blocks and indexes. The documentation emphasizes that VACUUM generates significant I/O load and can cause temporary performance degradation for other active sessions, which is why cost-based vacuuming parameters were introduced to limit the impact.
The FREEZE mode of VACUUM (not explored in the results but part of the official syntax) serves to freeze tuples with very old transaction IDs, marking them as permanent, so that they do not need to be considered during future wraparound protection scans. In practice, VACUUM FREEZE performs an aggressive vacuum that forces the “freezing” of tuples, advancing the table’s relfrozenxid and reducing pressure on the XID management system.
VACUUM ANALYZE and Statistics for the Planner
The ANALYZE option adds a crucial dimension to the VACUUM command, as it allows updating data distribution statistics used by the PostgreSQL query planner to determine the most efficient execution plans. In historical documentation, ANALYZE was defined as the option that “updates column statistics used by the optimizer to determine the most efficient way to execute a query,” with statistics representing the data distribution in each column. These statistics are particularly useful when the planner needs to choose between different possible execution paths, for example, between a sequential scan and the use of an index, between hash join and nested loop, or between plans that involve sort and limit.
In modern PostgreSQL, VACUUM ANALYZE is often used as a standard combination in maintenance routines, because it allows, in a single pass, the removal of dead tuples and the updating of statistics. The documentation recommends running ANALYZE regularly, especially on tables subject to frequent insertions, updates, or deletions, because outdated statistics can lead to suboptimal plans, and physical cleanup alone is not enough to maintain predictable performance.
Autovacuum itself integrates both VACUUM and ANALYZE into its activities, activating ANALYZE when the number of inserted, updated, or deleted tuples exceeds the autovacuum_analyze_threshold plus the proportional part established by autovacuum_analyze_scale_factor. This means that, in most installations, the DBA can rely on the system to automatically execute ANALYZE when necessary, only adjusting the thresholds and scale factors to adapt them to the behavior of individual tables. However, in particularly critical scenarios, for example after massive bulk loads or after massive schema and data changes, it may be appropriate to manually execute VACUUM (ANALYZE) on key tables to ensure the planner immediately has reliable statistics.
VACUUM FULL and Table Rewriting
As already mentioned, the VACUUM FULL variant is a special operation that completely rewrites the table’s content into a new disk file, leaving no extra space, allowing unused space to be returned to the operating system. The official documentation highlights that VACUUM FULL is much slower than standard VACUUM and requires an ACCESS EXCLUSIVE lock on each table while it is being processed, preventing concurrent use of the table by other sessions. Furthermore, this form requires additional disk space, because the new copy of the table is completed before the old one can be removed, and the freed space is not available during the initial part of the operation.
For these reasons, the manual suggests using VACUUM FULL only when it is necessary to reclaim a significant amount of internal table space, for example, following massive deletions or a long period of insufficient maintenance that led to marked bloat. The official wiki emphasizes that, unless you need to return space to the operating system or repair tables and indexes “bloated out of control,” you are generally advised to use standard VACUUM and not VACUUM FULL. Read also: Oracle Data Pump: Export & Import with expdp & impdp
It is important to note that VACUUM FULL, in modern versions, shares the progress reporting mechanism with CLUSTER, and is monitored via pg_stat_progress_cluster rather than pg_stat_progress_vacuum. This technical detail underscores the nature of VACUUM FULL: more than a simple cleanup of dead tuples, it is a physical clustering operation of the table, which recreates its layout on disk, potentially improving data locality but at the cost of an exclusive lock and very intense I/O.
Impact on Concurrent Transactions and Locks
The main difference, from a concurrency perspective, between standard VACUUM and VACUUM FULL is the type of lock required and compatibility with other operations. Standard VACUUM can be executed in parallel with other read and write operations, because it does not require an exclusive lock on the table, but only lighter locks that prevent modifications to its schema. The documentation specifies that, during a standard VACUUM, it is not possible to modify a table’s definition, for example with ALTER TABLE, while DML operations (SELECT, INSERT, UPDATE, DELETE) continue to function normally. This makes standard VACUUM compatible with high-concurrency production environments, although its I/O impact must be considered.
Conversely, VACUUM FULL requires an ACCESS EXCLUSIVE lock on the table, which means that no other session can use the table during the operation. This includes both read and write operations, and, in practice, VACUUM FULL “blocks the table” for the entire duration of the rewrite. This behavior is a well-known pitfall: running VACUUM FULL on very large tables during peak hours can freeze significant portions of the application, causing timeouts and queues of requests waiting for the lock.
Proper management of VACUUM FULL, therefore, requires careful planning, typically during scheduled maintenance windows with reduced user load, and a preliminary assessment of table sizes and available disk space. Additionally, in clusters with replication, the effect of VACUUM FULL on replication and standby convergence times must be considered, as massive file rewrites can generate high volumes of WAL.
To mitigate these effects, PostgreSQL offers vacuum cost-based throttling parameters (such as vacuum_cost_delay, vacuum_cost_limit, and their autovacuum equivalents), which can slow down standard vacuum to reduce its performance impact, but these mechanisms are not applied in VACUUM FULL mode, which by definition is an aggressive operation and not subject to throttling. Therefore, VACUUM FULL should be considered a “last resort” and not a routine tool.
Why VACUUM FULL Blocks the Table
ACCESS EXCLUSIVE Lock and Blocks on DML and DDL Operations
The technical reason why VACUUM FULL blocks the table lies in the nature of the operation: the complete rewriting of the content into a new file requires that no other transaction can read or modify the table during the process, to avoid inconsistencies and visibility issues. PostgreSQL implements this protection by requiring an ACCESS EXCLUSIVE lock, which is the most restrictive lock level and prevents even simple reads.
The ACCESS EXCLUSIVE lock is only shared with itself, meaning that while a VACUUM FULL is running, all other operations that require even an ACCESS SHARE lock (typical for SELECTs) or ROW EXCLUSIVE lock (typical for UPDATEs/DELETEs) remain waiting. The perceived result for users is that the table appears “frozen”: queries involving it block or time out until VACUUM FULL finishes.
The routine vacuuming documentation reiterates that while standard VACUUM can operate in parallel with normal operations, VACUUM FULL “requires an ACCESS EXCLUSIVE lock on the table it is working on, and therefore cannot be done in parallel with other use of the table.” This sentence clearly summarizes why VACUUM FULL is considered invasive and unsuitable for routine maintenance. DBAs must be aware of this behavior and avoid running VACUUM FULL on critical tables during production hours.
Another point to consider is that, in the presence of cascading locks, a VACUUM FULL on a table referenced by foreign keys or involved in complex indexes can contribute to extended blocking situations, especially if other transactions attempt to acquire locks themselves. Therefore, planning VACUUM FULL operations must consider not only the individual table but also dependencies and application usage.
Differences Between VACUUM FULL, CLUSTER, and REINDEX
VACUUM FULL is not the only maintenance operation that physically rewrites a table. Commands like CLUSTER and REINDEX have similar effects insofar as they reconstruct structures on disk and indexes, and share many of the same implications in terms of locks. The CLUSTER command, for example, rewrites a table by physically ordering its rows according to an index, producing a new table file; VACUUM FULL, while not imposing a specific order, still performs a physical recreation of the file, and for this reason, its progress is monitored via pg_stat_progress_cluster along with CLUSTER.
The REINDEX command reconstructs an index, eliminating bloat and optimizing its structure, but it does not rewrite the heap; however, REINDEX also requires significant locks on the involved objects and cannot be executed in full concurrency with all operations. In certain situations, extreme index bloat can be mitigated by REINDEX instead of VACUUM FULL, reducing the impact on the heap and the global table lock.
The choice between VACUUM FULL, CLUSTER, and REINDEX depends on the nature of the problem: if the bloat primarily concerns the heap and you want to return space to the operating system, VACUUM FULL may be justified; if you want to improve data locality based on a clustered index, CLUSTER is the natural choice; if the bloat is concentrated on the indexes, REINDEX is often sufficient. In all cases, however, administrators must consider the required locks and plan such operations during appropriate windows, avoiding execution during periods of high load. Read also: logrotate: Configure Log Rotation on Linux
Scenarios Where VACUUM FULL Is Justified
PostgreSQL documentation is clear in recommending avoiding the routine use of VACUUM FULL but acknowledges that there are scenarios where it may be justified or even necessary. The first typical scenario is a table that has undergone massive deletions or updates that have drastically reduced the number of active rows, but whose disk file remains very large because internal space cannot be easily returned to the operating system. In such cases, if the bloat is such that it compromises overall storage utilization or heavily impacts the cache, a VACUUM FULL can restore the table to dimensions proportionate to the data actually present, freeing up space for other uses.
A second scenario is tables that have suffered for a long time from insufficient autovacuum configuration or long transactions that have blocked dead tuple cleanup, leading to progressive growth of the file and indexes. If, after correcting the configuration or application usage (e.g., by reducing idle in transaction), it is found that some tables are now severely bloated, an extraordinary intervention with VACUUM FULL may be the quickest way to restore them to a normal situation.
The PostgreSQL wiki emphasizes that if autovacuum fails to keep tables and indexes free from bloat, the answer should be “tune it, don’t supplement it with manual vacuuming and reindexing,” but admits that there are cases where tables are “bloated out of control due to insufficient autovacuum,” in which a VACUUM FULL or equivalents can be considered to remedy the situation. The key is to recognize that VACUUM FULL addresses the symptom, but the cause must be addressed by tuning autovacuum and application usage to prevent the problem from recurring.
Alternative Strategies: Aggressive Autovacuum, Partitioning, Migrations
In modern environments, the trend among vendors and the community is to consider VACUUM FULL an exceptional operation and to favor alternative strategies for managing bloat and dead tuple accumulation. The first strategy is autovacuum tuning for specific tables, reducing the autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold parameters for highly dynamic tables, so that autovacuum intervenes more frequently and prevents uncontrolled growth of dead tuples. autovacuum_max_workers and autovacuum_work_mem can also be increased to allow more workers to operate simultaneously and manage more dead tuples per cycle, at the cost of increased resource consumption.
A second strategy is partitioning very large tables or those with characteristic access and deletion patterns. For log tables or temporal data, for example, partitioning by time interval or range allows managing the entire deletion of partitions with TRUNCATE or DROP, preventing dead tuple accumulation from concentrating on a single table file. TRUNCATE, in particular, completely empties a table and immediately returns internal space, but it is only applicable when total data deletion is acceptable, typically on “expired” partitions.
A third strategy is the logical or physical migration of data from a bloated table to a new table, for example, with CREATE TABLE AS SELECT of only active rows, followed by renaming tables, in carefully planned operations. This technique, while costly in terms of I/O, can be less invasive than VACUUM FULL if combined with ordered locks and adequate replication, and allows redesigning schema and indexes during the process.
Finally, in managed cloud environments, such as Azure Database for PostgreSQL, the vendor’s recommendation is to carefully monitor autovacuum and primarily intervene on tuning and application practices, using advanced monitoring tools to identify anti-wraparound vacuums and failsafe vacuums that indicate that routine maintenance is not keeping pace. In such contexts, manual interventions like VACUUM FULL are often discouraged or managed through change control procedures, precisely because of their potentially disruptive impact.
Tuning Autovacuum per Table
Global Autovacuum Parameters in postgresql.conf
The autovacuum daemon is an optional but highly recommended component of PostgreSQL, whose purpose is to automate the execution of VACUUM and ANALYZE. In the default configuration of modern PostgreSQL installations, autovacuum is active by default and operates in the background, but its global parameters, defined in the postgresql.conf file, influence behavior across the entire cluster. Among the most important parameters are:
autovacuum = on: enables or disables the autovacuum daemon (defaulton).autovacuum_max_workers: the maximum number of autovacuum processes that can run simultaneously (default 3). Increasing it can help manage clusters with many active tables, but increases resource consumption. A typical output:
SHOW autovacuum_max_workers;
autovacuum_max_workers
------------------------
3
(1 row)
autovacuum_vacuum_cost_delay: the pause in milliseconds between one vacuum cycle and the next when the I/O cost exceedsautovacuum_vacuum_cost_limit(default 2ms). Reducing it makes vacuum more aggressive, increasing it makes it slower and less impactful.autovacuum_vacuum_cost_limit: the amount of I/O “cost” that an autovacuum process can accumulate before pausing (default -1, which means usingvacuum_cost_limit).autovacuum_naptime: the rest time between two database scan cycles for tables needing vacuum or analyze (default 1 minute). Reducing it makes checks more frequent, increasing it spaces them out.
These global parameters establish the basic behavior of autovacuum, but it is crucial to understand that more granular tuning, at the individual table level, is often necessary to optimize performance without negatively impacting the entire cluster. The official PostgreSQL documentation dedicates an entire section to server parameters related to vacuum, indicating how to adjust them to balance aggressiveness and resource impact. Consult the official PostgreSQL documentation on Autovacuum parameters
Per-Table Tuning with ALTER TABLE
While global parameters are a good starting point, tables in a database have very different behaviors: some are static, others undergo millions of UPDATE/DELETEs per day. PostgreSQL allows overriding autovacuum parameters for individual tables, offering granular and targeted control. This is done with the ALTER TABLE ... SET (storage_parameter = value) command. The most common parameters to configure at the table level are:
autovacuum_vacuum_threshold: the minimum number of dead tuples that must accumulate before autovacuum intervenes (default globalautovacuum_vacuum_threshold, typically 50).autovacuum_vacuum_scale_factor: a percentage of the table size that, added to the threshold, determines the effective threshold for vacuum activation (default globalautovacuum_vacuum_scale_factor, typically a significant portion). For very dynamic tables, this value can be reduced. For example:
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02);
This sets the scale factor to a reduced value, making autovacuum more reactive on orders compared to the global configuration.
autovacuum_analyze_thresholdandautovacuum_analyze_scale_factor: similar to vacuum parameters, but control when to execute ANALYZE to update planner statistics. Adjusting them is crucial for maintaining efficient execution plans on tables with high data volatility.autovacuum_enabled = off: completely disables autovacuum for a specific table. This is rarely recommended but can be useful for extremely static tables or those managed with very specific manual maintenance procedures (e.g., partitioned tables that are periodically truncated).
The best approach is to monitor the most active tables using pg_stat_user_tables and pgstattuple, identify those with rapidly growing n_dead_tup or significant bloat, and then apply specific tuning. For example, a log table with millions of daily insertions and deletions might benefit from a very low autovacuum_vacuum_scale_factor (e.g., 0.01) and a high autovacuum_vacuum_threshold, to balance vacuum aggressiveness with I/O cost. Conversely, a rarely modified configuration table might not require particular tuning or, in extreme cases, have autovacuum disabled.
Transaction ID Wraparound: The Risk to Know
What is Transaction ID Wraparound
Transaction ID wraparound is one of the most dangerous and least understood pitfalls in PostgreSQL. As mentioned, every transaction and every tuple in PostgreSQL has a 32-bit transaction ID (XID). This XID is used by the MVCC system to determine tuple visibility. Since XIDs are 32-bit, their maximum number is approximately 4 billion. When the XID counter approaches this limit, if older tuples are not “frozen” by VACUUM, the system risks incorrectly interpreting future transactions as “older” than past ones, leading to potential data corruption. To prevent this, PostgreSQL implements a protection mechanism that, in case of imminent wraparound risk, stops all new writes to the database, putting it in read-only mode until an anti-wraparound VACUUM is completed. This block can last hours or days, depending on the database size and vacuum speed, causing a total service interruption.
Protection and Monitoring Parameters
PostgreSQL offers parameters to monitor and manage wraparound risk:
autovacuum_freeze_max_age: defines the maximum age in transactions that a tuple can reach before being considered a candidate for an aggressive “anti-wraparound vacuum” (default 200 million transactions). If a table contains tuples with XIDs older than this value, autovacuum will force a vacuum on that table, even if it has not reached the normal dead tuple thresholds.vacuum_freeze_min_age: the minimum age of a tuple (in transactions) to be a candidate for “freezing” (default 50 million transactions).
To monitor transaction age and prevent wraparound, it is crucial to query the pg_database view. In particular, the datfrozenxid column of pg_database shows the oldest XID that has not yet been frozen for that database. We can calculate the age of this XID relative to the current XID with the age() function:
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;
A typical output:
datname | age
--------------+-----------
my_app_db | 150000000
postgres | 10000000
template1 | 10000000
template0 | 10000000
(4 rows)
If the output shows an age approaching autovacuum_freeze_max_age (or, worse, exceeding 2 billion, which is the point of no return), it is a red alert signal. In these cases, immediate intervention is necessary to force VACUUM on the affected tables, or to increase autovacuum’s aggressiveness, otherwise the database will block. Read also: Backup 3-2-1-1-0: Rule Explained with Real Example
Monitoring Autovacuum Over Time
Effective monitoring is key to preventing VACUUM and autovacuum-related issues. In addition to the queries already mentioned, it is useful to integrate PostgreSQL monitoring with external tools to track trends over time.
PostgreSQL Logs
PostgreSQL logs are an invaluable source of information. You can configure the log_autovacuum_min_duration parameter (in postgresql.conf) to log all vacuums that exceed a certain duration in milliseconds (e.g., log_autovacuum_min_duration = 250). This allows identifying vacuums that are too slow or fail, providing useful details for tuning or troubleshooting. The logs will also show “anti-wraparound vacuum” or “failsafe vacuum” entries, which are alarm signals of an insufficiently aggressive autovacuum.
External Monitoring Tools
Platforms like Prometheus with Grafana, Zabbix, or equivalent cloud services (e.g., Azure Monitor, AWS CloudWatch) can collect metrics from pg_stat_user_tables and pg_stat_activity and visualize them in dashboards. Monitor charts such as:
n_dead_tupfor the most active tables: to identify growing bloat.last_autovacuumandlast_autoanalyze: to verify the frequency and effectiveness of autovacuum.age(datfrozenxid): to keep the risk of wraparound under control at the database level.autovacuum_countandvacuum_count: to understand how many automatic and manual vacuums are executed.
This data, viewed over time, allows identifying patterns, anticipating problems, and validating the effectiveness of changes to autovacuum parameters. A constant increase in n_dead_tup on a table, despite frequent autovacuum, might indicate the need for more aggressive tuning or an analysis of the application workload.
Common Errors and Troubleshooting
| Error or exact message | Cause | Solution |
|---|---|---|
FATAL: database is in "recovery mode" (due to wraparound) |
The database has reached the transaction ID limit and has blocked to prevent data corruption. | Execute an aggressive VACUUM FREEZE or VACUUM (manually or via autovacuum) to freeze old tuples. Requires downtime. |
ERROR: relation "mytable" does not exist (after VACUUM FULL) |
A VACUUM FULL did not complete correctly or was interrupted, leaving the table in an inconsistent state. | Check PostgreSQL logs for the reason for failure. Restore from backup or attempt a REINDEX TABLE if the problem is with the index. |
| Slow queries on tables with many modifications | Accumulation of dead tuples (bloat) slowing down scans or outdated planner statistics. | Execute VACUUM (ANALYZE) on the table. If the problem persists, tune autovacuum parameters to make it more frequent. |
idle in transaction or idle in transaction (aborted) in pg_stat_activity |
Application sessions opening transactions but not closing them, blocking the VACUUM cutoff and preventing cleanup of dead tuples. | Identify the responsible queries or application processes and correct the code. Manually terminate blocking sessions (pg_terminate_backend(pid)). |
| Tablespace disk space constantly growing | Autovacuum fails to reclaim space to the operating system, or the table is severely bloated. | Verify autovacuum effectiveness. If bloat is extreme, consider a VACUUM FULL during a maintenance window or migration/partitioning strategies. |
Frequently Asked Questions
Should I run VACUUM manually even with autovacuum enabled?
Generally no. Autovacuum is designed to handle most cleanup needs. Manual intervention is recommended only in specific cases: after massive bulk loads, to resolve severe bloat that autovacuum cannot manage, or in emergency situations like the risk of transaction ID wraparound. Good autovacuum tuning minimizes the need for manual interventions.
Should VACUUM FULL always be avoided?
VACUUM FULL is a very invasive operation that completely blocks the table. It should be avoided in routine maintenance. It is justified only when it is strictly necessary to reclaim a significant amount of disk space to the operating system from a severely bloated table and there are no practical alternatives, and only during scheduled maintenance windows with low application load.
How can I tell if a table is “bloated”?
You can use the query SELECT relname, n_dead_tup, n_live_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC; to see the number of dead tuples. For a more detailed analysis, install the pgstattuple extension and use SELECT * FROM pgstattuple('my_table'); to get the exact percentage of dead tuples and free space.
What is the performance impact of autovacuum?
Autovacuum consumes resources (CPU, I/O) during execution. However, its impact is mitigated by cost-based throttling mechanisms that slow down the process if the system load is high. If properly configured, the benefit of a clean database with up-to-date statistics far outweighs the cost of vacuuming, preventing much more severe performance problems caused by bloat.
What happens if I disable autovacuum?
Disabling autovacuum is strongly discouraged. Without it, dead tuples will accumulate indefinitely, leading to extreme bloat, performance degradation, and ultimately, the risk of transaction ID wraparound that will block the database. Manual maintenance would be an enormous and difficult burden to manage in a dynamic environment.
Conclusions with Operational Takeaways
Managing VACUUM and autovacuum is a fundamental pillar of PostgreSQL administration. Understanding the MVCC mechanism and the role of dead tuples is essential for maintaining a performant and stable database. Key operational takeaways are:
- Proactive Monitoring: Use
pg_stat_user_tables,pg_stat_activity, andpgstattupleto promptly identify tables with bloat or growingn_dead_tup. Also monitorage(datfrozenxid)to prevent wraparound. - Granular Autovacuum Tuning: Do not rely solely on global parameters. Adjust
autovacuum_vacuum_thresholdandautovacuum_vacuum_scale_factorat the individual table level for the most dynamic tables, balancing aggressiveness and resource consumption. - Standard VACUUM vs. VACUUM FULL: Always prefer standard VACUUM for routine maintenance, as it does not block DML operations. Reserve VACUUM FULL only for exceptional cases of extreme bloat that require reclaiming space to the operating system, planning it for maintenance windows.
- Beware of Long Transactions:
idle in transactionsessions can block the VACUUM cutoff and cause dead tuple accumulation. Identify and resolve these issues at the application level. - Logs and Alerting: Configure
log_autovacuum_min_durationand integrate PostgreSQL logs with your alerting system to be notified of problematic vacuums or wraparound risk.
By implementing these practices, you can ensure that your PostgreSQL database remains a robust and performant component of your IT stack, avoiding unexpected outages and keeping resource consumption under control.
Sources
- [2] PostgreSQL 16.1 Documentation: VACUUM FULL
- [3] PostgreSQL 16.1 Documentation: Routine Vacuuming
- [5] PostgreSQL 16.1 Documentation: Runtime Configuration – Autovacuum
- [9] PostgreSQL 16.1 Documentation: Transaction IDs
- [10] PostgreSQL 16.1 Documentation: REINDEX
- [11] PostgreSQL 16.1 Documentation: VACUUM
- [12] PostgreSQL 16.1 Documentation: SQL Command VACUUM
- [13] PostgreSQL 16.1 Documentation: pg_stat_progress_vacuum
- [14] PostgreSQL 16.1 Documentation: The Statistics Collector
- [16] PostgreSQL 16.1 Documentation: Monitoring Database Activity
- [18] PostgreSQL 16.1 Documentation: Vacuum Cost Delay
- [19] PostgreSQL 16.1 Documentation: pgstattuple
- [20] PostgreSQL 16.1 Documentation: pgstattuple_approx
Updated: November 2026