PostgreSQL VACUUM: How to Reclaim Space and Prevent Table Bloat

PostgreSQL can slow down over time even when an application has not added many new records. Tables become larger than expected, indexes take up more disk space, and queries may need to scan more pages than they did earlier.

One common reason is how PostgreSQL handles updates and deletes.

Unlike databases that overwrite a row in place in every situation, PostgreSQL uses a multiversion concurrency control mechanism called MVCC. When a row is updated or deleted, PostgreSQL generally keeps the old row version until it is no longer needed by active transactions. This allows concurrent queries to read a consistent view of the data without blocking every update.

The problem is that old row versions cannot remain forever. PostgreSQL needs to identify when they are no longer visible to any transaction and make their space reusable. That is the job of VACUUM.

Understanding VACUUM helps developers and database administrators control table bloat, prevent transaction ID wraparound problems, and maintain predictable database performance.

What Is VACUUM in PostgreSQL?

VACUUM is a PostgreSQL maintenance command that cleans up obsolete row versions left behind by updates and deletes.

Consider a table containing a customer's address. When the application updates that address, PostgreSQL creates a new row version. The previous version may remain physically present until it is safe to remove it.

A regular VACUUM identifies row versions that are no longer needed, marks their space as reusable, and updates information PostgreSQL uses for visibility and maintenance.

For example:

UPDATE customers
SET address = '12 Lake Road'
WHERE customer_id = 101;

The old address version does not necessarily disappear immediately. PostgreSQL must first determine whether any active transaction could still need to see it.

After obsolete versions become removable, running VACUUM can reclaim their space for reuse by the same table.

However, regular VACUUM usually does not shrink the table file or return its space to the operating system. It primarily makes space available for future writes within the existing relation.

This distinction is important when investigating disk usage. A table can contain substantial reusable space while its file remains the same size.

Why PostgreSQL Needs VACUUM

PostgreSQL uses MVCC to support concurrent reads and writes. This improves concurrency, but it also means that updates and deletes can leave obsolete row versions behind.

There are several reasons why vacuuming is necessary.

Updates create new row versions

When an application updates a row, PostgreSQL usually creates a new version rather than simply overwriting the old version.

For example:

UPDATE orders
SET status = 'Shipped'
WHERE order_id = 5001;

If the order was previously marked as Processing, the old version may remain until PostgreSQL determines it is no longer needed.

Repeated updates to frequently modified rows can create many obsolete versions.

Deletes leave dead tuples

Deleting a row does not immediately remove its physical contents from the table.

DELETE FROM orders
WHERE order_date < DATE '2023-01-01';

The deleted rows become candidates for cleanup once they are no longer visible to any relevant transaction.

A table with frequent bulk deletes can therefore occupy much more space than its current row count suggests.

Indexes can contain references to obsolete rows

PostgreSQL indexes also need maintenance. Regular vacuuming helps remove index entries associated with dead tuples when they are no longer needed.

If vacuuming falls behind, obsolete row versions and index entries can contribute to inefficient storage usage and additional I/O.

Transaction ID wraparound must be prevented

PostgreSQL uses transaction IDs to determine which row versions are visible. Transaction IDs have a finite range, so PostgreSQL must freeze sufficiently old tuples to prevent transaction ID wraparound.

Vacuuming performs this freezing work. If wraparound prevention is neglected, PostgreSQL can eventually restrict database operations to protect data integrity.

For this reason, vacuuming is not just a performance optimization. It is an essential part of PostgreSQL maintenance.

Step 1: Run VACUUM on a Table

You can vacuum a specific table with a simple SQL command.

VACUUM customers;

This asks PostgreSQL to clean up removable row versions in the customers table.

You can also vacuum the entire current database:

VACUUM;

Regular vacuuming can:

Make space from dead tuples reusable.
Update the visibility map.
Remove eligible obsolete index entries.
Help maintain accurate maintenance-related information.
Freeze old tuples when required to prevent transaction ID wraparound.

A normal VACUUM can run alongside ordinary reads and writes. It still consumes I/O and other resources, so a large cleanup operation can affect performance even when it does not block normal operations in the same way as an exclusive table rewrite.

Do not assume that running VACUUM once permanently solves table bloat. Tables with ongoing updates and deletes need continuous maintenance.

Step 2: Understand VACUUM ANALYZE

PostgreSQL also provides ANALYZE, which collects statistics that the query planner uses to choose execution plans.

You can run both operations together:

VACUUM (ANALYZE) customers;

VACUUM handles obsolete row versions, while ANALYZE collects statistics about the table's data distribution.

These tasks solve different problems.

For example, a query may become slow because PostgreSQL estimates that a filter will return 10 rows when it actually returns 100,000. Updated statistics can help the planner choose a more appropriate execution plan.

But if the main problem is a table full of dead tuples, running ANALYZE alone will not clean them up.

Command	Main purpose	Removes obsolete row versions?	Updates planner statistics?
`VACUUM`	Clean up dead tuples and maintain visibility information	Yes, when eligible	Not a replacement for `ANALYZE`
`ANALYZE`	Collect statistics for the query planner	No	Yes
`VACUUM (ANALYZE)`	Perform both maintenance tasks	Yes, when eligible	Yes
`VACUUM FULL`	Rewrite a table to compact its storage	Yes, through rewriting	Run `ANALYZE` separately if needed

For routine maintenance, VACUUM (ANALYZE) is often useful after a large data change, although PostgreSQL autovacuum should normally handle ongoing maintenance automatically.

Step 3: Understand VACUUM FULL

Sometimes regular vacuuming is not enough to meet a storage requirement.

Suppose a table once contained 50 million rows, but an archival operation deleted most of them. Regular VACUUM may make the freed space reusable within the table, but the relation file can remain large.

VACUUM FULL rewrites the table into a more compact form.

VACUUM FULL customers;

This operation can return unused space to the operating system by creating a compacted table file.

However, it has important operational costs:

It requires an ACCESS EXCLUSIVE lock on the table, blocking other access while the operation runs.
It needs additional disk space while the rewritten table is being created.
It can generate significant I/O.
It can take a long time on large tables.
It may require additional maintenance for indexes and related database objects.

For these reasons, VACUUM FULL should not be used as an automatic response to every large table.

Use it when reclaiming physical disk space is necessary and you can schedule an appropriate maintenance window. For many production workloads, ordinary vacuuming and better management of data retention are safer first steps.

Step 4: Check Table Bloat and Dead Tuples

Before changing maintenance settings, inspect the table's statistics.

The pg_stat_user_tables view provides useful information about table activity, including estimated live and dead tuple counts.

SELECT
    schemaname,
    relname AS table_name,
    n_live_tup,
    n_dead_tup,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

This query helps identify tables with large estimated dead-tuple counts and shows when vacuuming or analysis last occurred.

Pay attention to:

n_dead_tup: An estimate of dead tuples.
n_tup_upd: The number of updated tuples recorded by the statistics system.
n_tup_del: The number of deleted tuples recorded by the statistics system.
last_autovacuum: The last recorded autovacuum time.
last_autoanalyze: The last recorded automatic analysis time.

These values are useful indicators, not exact measurements of physical bloat. Statistics can lag, and dead-tuple counts do not directly tell you how many bytes can be reclaimed.

A table with a high dead-tuple estimate may need investigation, but do not automatically conclude that it requires VACUUM FULL.

Check table and index sizes

You can also inspect the physical size of a table and its indexes.

SELECT
    schemaname,
    relname AS table_name,
    pg_size_pretty(
        pg_relation_size(
            format('%I.%I', schemaname, relname)::regclass
        )
    ) AS table_size,
    pg_size_pretty(
        pg_indexes_size(
            format('%I.%I', schemaname, relname)::regclass
        )
    ) AS index_size,
    pg_size_pretty(
        pg_total_relation_size(
            format('%I.%I', schemaname, relname)::regclass
        )
    ) AS total_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(
    format('%I.%I', schemaname, relname)::regclass
) DESC;

The query reports table storage, index storage, and total relation storage, including associated storage such as TOAST data.

A large table is not necessarily bloated. It may simply contain a large amount of valid data. Compare its size with row counts, update/delete activity, historical size trends, and query behavior before deciding what to do.

Step 5: Understand Autovacuum

PostgreSQL includes an automatic maintenance process called autovacuum. It launches workers that vacuum tables and collect statistics when configured thresholds are reached.

For many applications, autovacuum is the primary mechanism that keeps routine table maintenance under control.

The trigger for vacuuming is influenced by configuration settings such as:

autovacuum_vacuum_threshold
autovacuum_vacuum_scale_factor
autovacuum_analyze_threshold
autovacuum_analyze_scale_factor
autovacuum_vacuum_insert_threshold
autovacuum_vacuum_insert_scale_factor

The exact behavior depends on the PostgreSQL version, table activity, and applicable configuration.

You can inspect some relevant settings with:

SHOW autovacuum;

SHOW autovacuum_vacuum_threshold;

SHOW autovacuum_vacuum_scale_factor;

SHOW autovacuum_analyze_scale_factor;

If autovacuum is disabled or cannot keep up with write activity, dead tuples may accumulate. Disabling it without a well-tested maintenance plan is dangerous because it also affects transaction ID freezing.

Why default thresholds may not suit every table

Autovacuum thresholds commonly combine a fixed threshold with a percentage of the table's estimated row count.

For a very large table, a scale factor of 20% can mean that a substantial number of tuple changes occur before the regular vacuum threshold is reached.

For a small, frequently updated table, the fixed threshold may be more influential.

This is why a single global setting may not be ideal for every workload.

Configure autovacuum for a busy table

For a large table that experiences frequent updates and deletes, you may decide to lower its vacuum scale factor.

For example:

ALTER TABLE public.orders
SET
(
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_threshold = 1000
);

This configures a lower scale factor and a fixed threshold for that table. The effective trigger depends on the applicable PostgreSQL settings and table statistics.

A smaller scale factor can make vacuuming happen more frequently, but it may also increase maintenance activity. Test changes against the table's write rate, available I/O, and query workload.

Do not copy these values blindly into every production database. The right configuration depends on table size, transaction patterns, hardware, and maintenance capacity.

Step 6: Find Transactions That Prevent Cleanup

One common reason vacuuming cannot remove old row versions is a long-running transaction.

A transaction may keep an older snapshot alive, meaning PostgreSQL must preserve row versions that could still be visible to it.

You can inspect active transactions with:

SELECT
    pid,
    usename,
    application_name,
    state,
    xact_start,
    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 have been open for an unusually long time.

An application may accidentally leave a transaction open while waiting for a user interaction, making an external API call, or processing a large workload.

A long-running transaction does not always mean there is a problem, so investigate the owner and purpose before terminating it.

PostgreSQL also supports prepared transactions, replication-related horizons, and other mechanisms that can hold back cleanup. If vacuuming is not removing expected dead tuples, investigate those conditions rather than repeatedly running the same command.

Step 7: Monitor Vacuum Progress

For large tables, vacuuming may take a significant amount of time. PostgreSQL exposes progress information through pg_stat_progress_vacuum.

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

This view helps you determine which table is being vacuumed and which phase the operation has reached.

The reported progress fields depend on the current phase, so not every field advances continuously. The view also does not provide a simple, universally accurate completion-time estimate.

For production troubleshooting, combine vacuum progress with database logs, transaction activity, I/O metrics, and table statistics.

Common VACUUM Mistakes

Assuming VACUUM returns disk space to the operating system

Regular vacuuming primarily makes space reusable within the table. If the goal is to shrink the physical relation file, evaluate VACUUM FULL or another suitable table-rewriting approach, taking locking and disk-space requirements into account.

Disabling autovacuum to reduce load

Disabling autovacuum may temporarily reduce background activity, but it allows dead tuples to accumulate and interferes with routine freezing work. Investigate why maintenance is expensive before changing it.

Running VACUUM FULL during peak traffic

VACUUM FULL requires an exclusive table lock and rewrites the relation. Running it during a busy period can block application requests and consume substantial resources.

Treating dead-tuple estimates as exact bloat measurements

Statistics views provide estimates. Use them as signals and validate suspected bloat with size trends, query plans, workload data, and suitable inspection tools.

Ignoring long-running transactions

Repeated vacuuming may not solve the problem if an old transaction prevents cleanup. Find the transaction or other cleanup horizon that is holding back progress.

Tuning autovacuum without monitoring the results

Changing thresholds can improve cleanup frequency, but it can also increase I/O and CPU usage. Track dead-tuple trends, vacuum duration, transaction ID age, and application latency after making changes.

Best Practices for PostgreSQL VACUUM

Keep autovacuum enabled unless you have a carefully designed alternative maintenance strategy.
Monitor dead-tuple estimates, table sizes, vacuum history, and long-running transactions.
Use VACUUM (ANALYZE) after substantial data changes when fresh planner statistics are needed.
Tune autovacuum at the table level when a specific workload needs different maintenance behavior.
Reserve VACUUM FULL for cases where physical compaction is necessary and the locking impact is acceptable.
Monitor transaction ID age and freezing activity to reduce the risk of wraparound problems.
Avoid holding database transactions open while waiting for external services or user input.
Test maintenance changes in a representative environment and verify their effect on both database performance and application latency.

Summary

PostgreSQL VACUUM is essential for managing obsolete row versions created by updates and deletes. It makes space reusable, supports index cleanup, updates visibility information, and contributes to transaction ID wraparound prevention.

For most applications, autovacuum should handle routine maintenance. Developers and database administrators should monitor its effectiveness, investigate long-running transactions, and adjust settings when a table's workload requires different behavior.

Remember the difference between regular VACUUM and VACUUM FULL: the first makes eligible space reusable, while the second rewrites the table to reclaim physical space at the cost of stronger locking and additional resources.

With proper monitoring, sensible autovacuum settings, and careful transaction management, PostgreSQL tables can remain healthier without resorting to disruptive maintenance operations.