PostgreSQL VACUUM: How to Reclaim Space and Prevent Table Bloat

PostgreSQL databases handle frequent data changes efficiently, but those changes can create a problem over time. When an application updates or deletes rows, PostgreSQL does not immediately remove every old version of the data. These old row versions can remain in the table until PostgreSQL determines that they are no longer needed.

This is where VACUUM becomes important.

The PostgreSQL VACUUM process cleans up dead row versions, makes space available for reuse, and helps prevent unnecessary table growth. When vacuuming does not keep up with database activity, tables can become bloated, queries may read more data than necessary, and storage usage can increase.

In this article, you will learn how PostgreSQL VACUUM works, how to check for table bloat, how to run vacuum operations safely, and how to troubleshoot situations where automatic cleanup is not keeping up.

What Is VACUUM in PostgreSQL?

VACUUM is a PostgreSQL maintenance command that cleans up row versions that are no longer visible to any active transaction.

PostgreSQL uses Multi-Version Concurrency Control (MVCC) to manage concurrent database operations. Instead of overwriting a row immediately during an update, PostgreSQL generally creates a new row version and leaves the old version behind.

This approach allows transactions to read a consistent view of the data without unnecessarily blocking updates.

However, the old row versions eventually need to be cleaned up.

Consider this example:

UPDATE employees
SET salary = 75000
WHERE employee_id = 101;

PostgreSQL generally creates a new version of the employee row. The previous version becomes obsolete once no active transaction needs it.

Similarly, when you delete a row:

DELETE FROM employees
WHERE employee_id = 101;

The row is marked as deleted from the perspective of transactions that can see the change, but its physical storage might remain until vacuum can safely reclaim it.

These obsolete row versions are commonly called dead tuples.

Without effective vacuuming, dead tuples can accumulate and contribute to table bloat.

Why PostgreSQL Needs VACUUM

Regular vacuuming helps PostgreSQL maintain database performance and storage efficiency.

Here are the main reasons it matters.

1. Clean Up Dead Tuples

Frequent UPDATE and DELETE operations can leave many obsolete row versions behind.

VACUUM identifies versions that are no longer needed and makes their space available for reuse.

2. Prevent Table Bloat

Table bloat occurs when a table occupies more storage than its useful data reasonably requires.

For example, an application that updates order statuses thousands of times a day may generate many obsolete row versions. If cleanup falls behind, the table can grow even when the number of current orders remains almost unchanged.

Regular vacuuming helps prevent this accumulation from becoming a long-term problem.

3. Maintain Query Performance

A bloated table may require PostgreSQL to read more pages to retrieve the same amount of useful data.

Vacuuming can reduce the impact of dead tuples and help keep tables manageable. However, it does not automatically compact every table or index, so it is not a complete solution for every performance issue.

4. Support Transaction ID Maintenance

PostgreSQL also uses transaction IDs to determine which row versions are visible.

Vacuuming performs important maintenance to prevent transaction ID wraparound, a condition that can threaten database availability if it is allowed to progress too far.

PostgreSQL can trigger aggressive automatic vacuuming when transaction ID safety requires it.

How VACUUM Works in PostgreSQL

Understanding the cleanup process helps you choose the right maintenance command.

At a high level, PostgreSQL follows this process:

Applications insert, update, or delete rows.
Updates and deletes can leave obsolete row versions.
PostgreSQL determines whether those versions are still needed by active transactions.
Vacuum identifies versions that can safely be removed from the table's visibility and storage structures.
The space becomes available for future database activity.

The important detail is that regular VACUUM usually makes space reusable inside the table rather than returning that space to the operating system.

For example, a table may occupy 8 GB before vacuuming. After dead tuples are cleaned up, the table might still occupy roughly 8 GB on disk, but PostgreSQL can reuse the freed space for future rows.

This is normal behavior, not necessarily a sign that vacuuming failed.

How to Run VACUUM Manually

PostgreSQL provides several maintenance commands for cleaning up dead tuples and updating planner statistics.

Run VACUUM on a Single Table

Use the following command to vacuum a specific table:

VACUUM employees;

This cleans up eligible dead tuples in the employees table and makes space available for reuse.

Regular VACUUM can generally run while normal queries and data modifications continue. It still consumes system resources and can increase disk I/O, so large maintenance operations should be monitored on busy production databases.

Run VACUUM with ANALYZE

In many situations, you should also refresh the statistics used by the PostgreSQL query planner.

VACUUM (ANALYZE) employees;

This command performs vacuuming and analyzes the table.

ANALYZE collects statistics about the data distribution, helping PostgreSQL estimate how many rows a query will return and choose an appropriate execution plan.

For example, after a large data import or deletion, the planner's statistics may no longer represent the table accurately. Running VACUUM (ANALYZE) can help address both cleanup and outdated statistics.

Vacuum an Entire Database

You can run vacuuming across the current database:

VACUUM (ANALYZE);

This is useful for general maintenance, although it may take considerable time on a large database.

For production environments, consider the workload, available I/O capacity, and maintenance requirements before running broad manual operations.

Run VACUUM FULL When Necessary

PostgreSQL also supports:

VACUUM FULL employees;

Unlike regular vacuuming, VACUUM FULL rewrites the table to compact it and can return unused space to the operating system.

However, it has important disadvantages:

It requires an ACCESS EXCLUSIVE lock on the table.
Normal reads and writes against that table are blocked while the operation runs.
It needs additional disk space for the rewrite.
It can generate significant I/O and take a long time on large tables.

Use VACUUM FULL only when reclaiming physical disk space is necessary and the locking and storage costs are acceptable. It should not be your default response to every bloated table.

Regular VACUUM vs. VACUUM FULL vs. ANALYZE

These commands solve different problems, so choosing the correct one matters.

Feature	VACUUM	VACUUM FULL	ANALYZE
Cleans up eligible dead tuples	Yes	Yes, during rewrite	No
Makes table space reusable	Yes	Yes	No
Can return table space to the OS	Usually not	Yes	No
Rewrites the entire table	No	Yes	No
Blocks normal table access with an exclusive lock	No, not in the same way	Yes	No
Updates planner statistics	Only with `ANALYZE`	Only with `ANALYZE`	Yes

Use regular VACUUM for routine maintenance, ANALYZE when statistics need refreshing, and VACUUM FULL when physical compaction is justified.

How to Check for Dead Tuples and Vacuum Activity

Before changing PostgreSQL maintenance settings, check what is happening in the database.

Check Dead Tuples in Tables

The pg_stat_user_tables view provides useful statistics about user tables.

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;

This query helps you identify tables with a high estimated number of dead tuples and see when they were last vacuumed or analyzed.

Pay attention to:

n_dead_tup: Estimated number of dead tuples.
n_live_tup: Estimated number of live tuples.
last_vacuum: Time of the last manual vacuum.
last_autovacuum: Time of the last automatic vacuum.
last_analyze and last_autoanalyze: Times of the latest manual and automatic statistics collection.

These values are statistics estimates, not exact counts. A high dead-tuple count is a reason to investigate, but it does not automatically mean that a table needs VACUUM FULL.

Check Whether VACUUM Is Running

For a long-running vacuum operation, inspect its progress:

SELECT
    pid,
    datname,
    relid::regclass AS table_name,
    phase,
    heap_blks_total,
    heap_blks_scanned,
    heap_blks_vacuumed
FROM pg_stat_progress_vacuum;

This view reports progress for active vacuum operations. If it returns no rows, there may be no vacuum operation running at that moment.

The progress information can help you distinguish a slow operation from a vacuum process that is not running at all.

What Is Autovacuum?

Autovacuum is PostgreSQL's automatic maintenance mechanism. It monitors database activity and starts background workers to vacuum tables and collect statistics when configured thresholds are reached.

For most applications, autovacuum should remain enabled.

You can check its setting with:

SHOW autovacuum;

If the result is on, autovacuum is enabled at the configuration level. That does not guarantee that every table is being cleaned up quickly enough.

Autovacuum decisions depend on factors such as table activity, configuration thresholds, worker availability, and transaction visibility.

How Autovacuum Decides When to Run

PostgreSQL commonly uses settings such as these:

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

The vacuum threshold combines a fixed number of changed rows with a proportion of the table's estimated size.

For example, the general vacuum trigger is based on a calculation similar to:

vacuum threshold + vacuum scale factor × estimated table rows

The exact behavior depends on the PostgreSQL version, table-level settings, and applicable configuration.

This default approach works well for many tables. However, a large, frequently updated table may accumulate substantial dead data before reaching a scale-factor-based threshold.

Instead of disabling autovacuum, investigate whether the affected table needs lower thresholds, a lower scale factor, more available workers, or better transaction management.

Why VACUUM Sometimes Cannot Remove Dead Tuples

Running vacuum does not guarantee that every dead tuple can be removed immediately.

Long-Running Transactions

An open transaction may still need to see older row versions. PostgreSQL must preserve versions that remain visible to relevant transactions.

Check for long-running transactions:

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

Look for transactions that remain open much longer than expected.

An idle in transaction session can be particularly problematic because it may hold a transaction open while doing no useful work.

Review the application logic, connection handling, and transaction boundaries before terminating a session. Ending a transaction can roll back work or disrupt an application request.

Replication Slots and Standby Feedback

Replication slots and standby feedback can hold back the cleanup of older row versions.

If a replication consumer falls behind, PostgreSQL may need to retain information that would otherwise be eligible for cleanup.

When vacuum cannot keep up despite reasonable settings, investigate replication lag, inactive replication slots, and standby feedback alongside ordinary transactions.

Do not drop a replication slot simply to force cleanup without understanding its purpose. Doing so can cause a replica or downstream consumer to require a new data synchronization.

Best Practices for Preventing Table Bloat

The most effective approach is to prevent cleanup from falling behind rather than waiting for a table to become very large.

1. Keep Autovacuum Enabled

Autovacuum handles routine maintenance without requiring a scheduled manual vacuum for every table.

Monitor its behavior and adjust it only when you have evidence that the defaults are insufficient.

2. Monitor High-Write Tables

Tables that receive frequent updates and deletes often need closer attention than tables that mostly receive inserts.

Review dead-tuple estimates, autovacuum timestamps, table growth, and query performance for these tables.

3. Investigate Long Transactions

Keep transactions short whenever possible. Avoid holding database transactions open while waiting for external APIs, user input, or lengthy application processing.

4. Tune Table-Level Settings Carefully

For a table that repeatedly accumulates dead tuples, you can adjust its vacuum scale factor:

ALTER TABLE orders
SET (
    autovacuum_vacuum_scale_factor = 0.05
);

This sets the table's vacuum scale factor to 5%. It can cause vacuuming to trigger earlier than the common default, depending on the other threshold settings.

This is an example, not a universal recommended value. Choose settings based on the table's size, update rate, vacuum duration, and available resources.

5. Refresh Statistics After Significant Data Changes

Use ANALYZE or VACUUM (ANALYZE) when significant changes make planner statistics less representative.

Better statistics can improve query plans, although they do not fix every slow query.

6. Investigate Index Bloat Separately

Table bloat and index bloat are related but different problems.

Regular VACUUM performs index cleanup as needed, but it does not generally compact indexes in the same way that a full table rewrite compacts a table.

If evidence points to an oversized or inefficient index, investigate its usage and maintenance requirements separately. Rebuilding an index may help in specific cases, but it also consumes resources and can require locks depending on the method used.

Common VACUUM Mistakes to Avoid

Running VACUUM FULL as routine maintenance: It can block table access and require significant extra disk space.
Assuming vacuuming will shrink the database files: Regular VACUUM usually makes space reusable inside the table.
Disabling autovacuum to reduce background load: This can allow dead tuples to accumulate and increase transaction ID wraparound risk.
Ignoring long-running transactions: Vacuum may be unable to remove row versions while older snapshots still need them.
Changing thresholds without measuring results: Aggressive vacuuming can compete with application queries for CPU and I/O resources.
Treating every slow query as a vacuum problem: Poor indexes, outdated statistics, inefficient SQL, and locking can also cause slow queries.

Conclusion

PostgreSQL VACUUM is an essential part of database maintenance. It cleans up obsolete row versions, makes table space available for reuse, and helps PostgreSQL maintain healthy database performance.

For most applications, regular vacuuming and properly configured autovacuum are the right starting points. Use monitoring views to identify tables that need attention, investigate long-running transactions and replication-related cleanup delays, and adjust maintenance settings based on actual workload data.

Use VACUUM FULL only when you specifically need to compact a table and can accept the exclusive lock, extra disk requirements, and operational impact.

The main goal is not to run vacuum commands as often as possible. It is to ensure that PostgreSQL can clean up obsolete data before it becomes a storage, performance, or transaction maintenance problem.