PostgreSQL VACUUM Explained: How to Control Table Bloat

PostgreSQL databases constantly create and update rows as applications insert, modify, and delete data. Unlike some database systems, PostgreSQL does not immediately remove every old row version when a record is updated or deleted.

Instead, PostgreSQL uses a mechanism called Multi-Version Concurrency Control (MVCC). It allows transactions to work with consistent snapshots of data while other transactions modify the same tables. This improves concurrency, but it also means that old row versions can remain in the database until they are no longer needed.

The VACUUM process cleans up row versions that are no longer visible to active transactions. When vacuuming cannot keep up with database activity, tables and indexes can accumulate unnecessary space, a problem commonly called table bloat.

Understanding VACUUM helps developers and database administrators maintain query performance, control storage growth, and prevent transaction ID wraparound problems.

Why PostgreSQL Needs VACUUM

When a PostgreSQL application updates a row, the database generally creates a new row version instead of overwriting the existing version in place. The old version remains available while transactions may still need to see it.

Consider this table:

CREATE TABLE products
(
    product_id BIGINT PRIMARY KEY,
    product_name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

Insert a product:

INSERT INTO products
    (product_id, product_name, price)
VALUES
    (1, 'Keyboard', 100.00);

Now update its price:

UPDATE products
SET price = 120.00
WHERE product_id = 1;

PostgreSQL creates a new row version for the updated record. The old version becomes obsolete once no active transaction needs it.

A similar situation occurs when a row is deleted:

DELETE FROM products
WHERE product_id = 1;

The deleted row's storage cannot necessarily be reclaimed immediately because another transaction might still need to see the earlier version.

VACUUM identifies row versions that are no longer needed and makes their space available for reuse. Without effective vacuuming, obsolete row versions can accumulate and increase the amount of data PostgreSQL must manage.

What Is Table Bloat?

Table bloat occurs when a table or its indexes occupy more storage than their useful data requires. Dead row versions, inefficient page utilization, and repeated updates can contribute to this problem.

A bloated table may require more disk reads to retrieve the same amount of useful information. Indexes can also become larger and less efficient.

For example, an application that updates order status thousands of times per day may create many obsolete row versions. If vacuuming falls behind, the table can continue consuming storage even when the number of current orders remains relatively stable.

However, a large table is not necessarily bloated. A table containing millions of active rows may legitimately require substantial storage. Diagnose bloat using workload history, table statistics, query performance, and suitable inspection tools rather than relying on table size alone.

Understand the Different VACUUM Operations

PostgreSQL provides several maintenance operations. Choosing the right one depends on whether you need routine cleanup, updated statistics, or a physical rewrite.

VACUUM

A standard VACUUM cleans up dead row versions whose storage can be reclaimed. It generally makes the space available for reuse within the same table rather than returning it to the operating system.

VACUUM products;

This is suitable for routine cleanup when the table has accumulated obsolete row versions.

Standard VACUUM can run alongside ordinary database reads and writes, although it still consumes system resources. It does not generally shrink a table file simply because some rows were deleted.

VACUUM ANALYZE

VACUUM ANALYZE combines cleanup with an update of planner statistics.

VACUUM (ANALYZE) products;

The cleanup removes reclaimable dead row versions, while ANALYZE collects statistics that help PostgreSQL estimate how many rows a query will return.

Those estimates influence decisions such as index scans, sequential scans, and join strategies.

For example, a query may perform poorly because PostgreSQL has inaccurate statistics about the number of rows matching a condition. Updating statistics can help the planner choose a more appropriate execution plan.

However, VACUUM ANALYZE does not guarantee that every slow query will become faster. Missing indexes, inefficient SQL, locking, and other factors may still be responsible.

VACUUM FULL

VACUUM FULL rewrites a table into a compact form and can return unused space to the operating system.

VACUUM FULL products;

Unlike standard VACUUM, VACUUM FULL requires an ACCESS EXCLUSIVE lock on the table while it operates. That blocks other access to the table and can make it unsuitable for busy production systems.

It also requires additional disk space during the rewrite.

Use VACUUM FULL only when you have confirmed that reclaiming disk space is necessary and can schedule the operation appropriately. It is not a replacement for regular vacuuming.

How Autovacuum Works

PostgreSQL includes an automatic maintenance process called autovacuum. It monitors database activity and launches VACUUM and ANALYZE operations when configured thresholds are reached.

For many applications, autovacuum is the main mechanism that keeps dead row versions under control.

You can inspect whether autovacuum is enabled:

SHOW autovacuum;

You can also inspect relevant settings:

SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_analyze_threshold;
SHOW autovacuum_analyze_scale_factor;

These settings help determine when automatic maintenance is triggered. The exact behavior depends on the PostgreSQL version, table-specific settings, and other configuration.

For large tables, default scale-factor settings may allow many changes to accumulate before vacuuming begins. A table with millions of rows can therefore need more deliberate tuning than a small table.

Do not disable autovacuum simply because it uses CPU or disk I/O. Doing so can allow dead tuples to accumulate and eventually create more serious performance and transaction ID maintenance problems.

Check Whether VACUUM Is Keeping Up

PostgreSQL exposes table statistics that help you understand update activity and vacuum behavior.

The following query lists tables with the largest reported numbers of dead tuples:

SELECT
    schemaname,
    relname AS table_name,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

The n_dead_tup value is an estimate, not an exact count of all obsolete row versions. Use it as a diagnostic signal rather than treating it as a precise measurement of bloat.

Pay attention to tables with many estimated dead tuples and old last_autovacuum timestamps. Also consider how frequently the table is updated or deleted and whether query performance has changed.

If a table is receiving frequent updates but autovacuum rarely runs, inspect its table-specific settings and the database-wide autovacuum configuration.

Tune Autovacuum for Large Tables

PostgreSQL allows autovacuum settings to be adjusted for individual tables. This can be useful when a busy table needs more frequent maintenance than smaller tables.

For example:

ALTER TABLE products
SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_threshold = 1000
);

These settings adjust the conditions under which vacuuming is triggered for the table. The scale factor and threshold work together, so the actual trigger depends on the table's size and maintenance rules.

A lower scale factor can make vacuuming start after a smaller proportion of the table has changed. This may help prevent large numbers of dead row versions from accumulating.

However, a lower threshold can also cause maintenance to run more frequently. The appropriate values depend on update volume, table size, available I/O capacity, and how quickly dead tuples are generated.

Monitor the results after changing settings. Look at vacuum frequency, dead-tuple estimates, database resource usage, and query latency rather than tuning based on a single metric.

Investigate Long-Running Transactions

One reason dead row versions remain unavailable for cleanup is that an old transaction may still need them.

Inspect active sessions and transaction ages:

SELECT
    pid,
    usename,
    state,
    xact_start,
    now() - xact_start AS transaction_age,
    query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

A long-running transaction is not automatically a problem. Reporting queries and other legitimate operations may run for an extended period. However, transactions that remain open while the application waits for user input or performs unrelated work can prevent vacuum from removing row versions that might still be visible to them.

Look for sessions with unusually old transaction start times, especially when dead-tuple counts continue to grow.

Applications should keep transactions as short as practical and avoid holding database transactions open across long external operations.

Understand Transaction ID Wraparound

PostgreSQL uses transaction IDs to determine which row versions are visible to transactions. Because transaction IDs have a finite range, PostgreSQL must manage their age carefully.

If old transaction IDs are not frozen before they become too old, the database can approach transaction ID wraparound. PostgreSQL has safeguards that can force aggressive vacuuming and, in extreme circumstances, prevent normal database operations to protect data correctness.

This is more serious than ordinary table bloat.

Database administrators should monitor transaction ID age and respond to wraparound warnings promptly. Do not rely only on dead-tuple counts, because a table with relatively few dead tuples can still require freezing maintenance.

For example, inspect the transaction ID age of database relations:

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    age(c.relfrozenxid) AS xid_age
FROM pg_class AS c
JOIN pg_namespace AS n
    ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
ORDER BY xid_age DESC
LIMIT 20;

This query helps identify relations with older frozen transaction ID horizons. The results should be evaluated against your PostgreSQL version and the database's configured wraparound safeguards.

VACUUM vs. VACUUM FULL

The two operations serve different purposes.

Feature	VACUUM	VACUUM FULL
Removes reclaimable dead row versions	Yes	Yes
Usually returns space to the operating system	No	Yes
Rewrites the table	No	Yes
Blocks ordinary table access with an exclusive lock	No, not in the same way	Yes
Suitable for routine maintenance	Yes	Usually not
Additional disk space needed during operation	Typically lower	Can be substantial

Standard VACUUM is generally the right choice for ongoing maintenance. VACUUM FULL is a more disruptive operation intended for cases where reclaiming physical disk space is necessary.

If you suspect index bloat, remember that vacuuming a table does not necessarily compact every index in the way you might expect. Investigate table and index behavior separately before choosing a corrective action.

Common VACUUM Mistakes

Running VACUUM FULL as a routine task

This can block application traffic and require substantial additional disk space. Prefer standard VACUUM and properly configured autovacuum for regular maintenance.

Assuming VACUUM always shrinks database files

Standard VACUUM generally makes space available for reuse within the relation. It does not normally shrink the file on disk.

Ignoring long-running transactions

Long-running transactions can prevent old row versions from becoming removable. Fixing autovacuum settings alone may not solve the problem.

Disabling autovacuum to reduce load

Disabling automatic maintenance can cause dead tuples to accumulate and increase wraparound risk. Investigate why autovacuum is consuming resources and tune it carefully instead.

Changing settings without monitoring the results

More frequent vacuuming can help, but it also consumes I/O and CPU. Evaluate the actual workload after changing maintenance settings.

Conclusion

PostgreSQL VACUUM is essential for maintaining healthy tables and indexes under MVCC. It cleans up obsolete row versions, makes space available for reuse, and helps prevent transaction ID wraparound.

For most applications, standard VACUUM and autovacuum should handle routine maintenance. Use VACUUM ANALYZE when cleanup and refreshed planner statistics are both needed, and reserve VACUUM FULL for situations where reclaiming physical disk space justifies its locking and storage costs.

Monitor dead-tuple estimates, autovacuum activity, long-running transactions, and transaction ID age. These signals help identify maintenance problems before they turn into slow queries or serious database availability issues.