Why VACUUM FULL Can Be Dangerous — and What to Use Instead

Summary

This blog explains why using VACUUM FULL in production is dangerous due to its exclusive locking and downtime, and details safer, more efficient PostgreSQL maintenance alternatives, such as routine VACUUM, AUTOVACUUM, and the non-blocking pg_repack extension.

The Misconception Around VACUUM FULL

Database administrators often encounter performance degradation in PostgreSQL over time, often due to table and index bloat. When that happens, the first instinct is usually to run VACUUM FULL to reclaim disk space. While this command appears to solve the problem, it can have unintended consequences if misused.

There is a common misconception that VACUUM FULL is simply a more thorough or “complete” version of VACUUM. In reality, it performs a very different operation.

VACUUM FULL physically rewrites the entire table and its indexes into new files, eliminating all dead tuples and compacting data storage. The newly created files replace the old ones, freeing up unused space for the operating system.

While this achieves the goal of reclaiming disk space, the process comes with significant drawbacks:

• It acquires an exclusive lock on the table, blocking all reads and writes until the operation completes.
• It requires additional disk space to write the new table version.
• It can take considerable time for large tables, leading to extended downtime.
• It causes index rebuilds, which can impact system I/O and increase transaction latency.

For small or inactive tables, this may not be an issue. But for large production environments, an unplanned VACUUM FULL can temporarily disrupt critical workloads.

VACUUM: Routine Table Maintenance

A regular VACUUM command is much less intrusive. It removes dead tuples from tables and indexes, marking the space they occupy as reusable for future inserts or updates. However, it does not reduce the physical file size of the table.

Example Usage

VACUUM mytable;
VACUUM ANALYZE mytable;

The ANALYZE option updates table statistics, helping the PostgreSQL planner choose optimal execution plans.

Regular vacuuming is essential to prevent transaction ID wraparound and maintain stable storage usage over time.

AUTOVACUUM: PostgreSQL’s Automated Maintenance Daemon

AUTOVACUUM automates vacuuming by monitoring table activity and triggering vacuum or analysis operations when specified thresholds are met.

Key configuration parameters include:

• autovacuum_vacuum_threshold
• autovacuum_vacuum_scale_factor
• autovacuum_analyze_scale_factor

The autovacuum process is lightweight and runs continuously in the background, ensuring tables do not accumulate excessive dead tuples. Proper tuning of autovacuum parameters is crucial for large, high-traffic databases to prevent bloat and minimize the need for manual intervention.

VACUUM FULL: When It’s Justified

Despite its risks, VACUUM FULL still has a place in PostgreSQL maintenance. It is beneficial in cases where:

• Large-scale data purges have occurred.
• Tables have experienced heavy updates followed by inactivity.
• Disk space needs to be reclaimed immediately, and downtime is acceptable.

In these scenarios, running VACUUM FULL during a maintenance window can help restore optimal disk usage. However, it should never be used as a routine cleanup command.

pg_repack: Reclaim Space Without Downtime

In production environments where downtime is unacceptable, pg_repack is the preferred alternative to VACUUM FULL.

What pg_repack Does

pg_repack is an open-source PostgreSQL extension that reorganizes tables and indexes online, allowing concurrent read and write operations during the process. It achieves this by:

• Creating a shadow copy of the target table.
• Copying live tuples from the original table into the new one.
• Applying incremental changes using triggers while the copy is being built.
• Swapping the tables once the rebuild is complete, with minimal lock time.
• Rebuilding indexes for optimal performance and storage efficiency.

This method ensures that the database remains accessible, thereby avoiding the blocking behavior associated with VACUUM FULL.

Benefits of pg_repack

• Non-blocking operation: Users and applications can continue reading and writing during the reorganization process.
• Reclaims disk space: Physically reduces the table and index size, similar to VACUUM FULL.
• Improves I/O efficiency: Rebuilt tables and indexes are compact and contiguous on disk, enhancing sequential scan performance.
• Reduces fragmentation: Eliminates internal table and index fragmentation caused by frequent updates or deletes.

Limitations and Considerations

• Requires installation of the pg_repack extension.
• Needs sufficient free disk space for the temporary copy.
• Should be used with caution on tables with very high write concurrency, as trigger-based synchronization may add overhead.
• Must be executed with appropriate privileges, usually as a superuser or table owner.

Example Usage

pg_repack -t mytable -d mydatabase

You can also reorganize entire databases or specific schemas:

pg_repack -d mydatabase --schema public

Comparison Summary

Operation	Table Lock	Returns Disk Space	Blocking Behavior	Ideal Use Case
VACUUM	Minimal	No	Non-blocking	Routine maintenance
AUTOVACUUM	Minimal	No	Non-blocking	Continuous background cleanup
VACUUM FULL	Exclusive	Yes	Fully blocking	Scheduled downtime maintenance
pg_repack	Minimal	Yes	Online operation	Production space reclamation

Best Practices

• Keep autovacuum enabled and tune it according to workload patterns.
• Regularly monitor table bloat using pg_stat_user_tables and custom queries.
• Use VACUUM FULL only when immediate disk reclamation is required and downtime is acceptable.
• Use pg_repack for live systems to reorganize large tables and indexes without service interruption.
• Schedule regular health checks to monitor autovacuum efficiency, dead tuples, and table growth.

Conclusion

Effective vacuuming strategies are essential for PostgreSQL performance and reliability.

While VACUUM FULL can reclaim space quickly, it comes with heavy locking costs that can disrupt live workloads. The combination of regular VACUUM, a well-tuned AUTOVACUUM, and pg_repack for online reorganization provides a comprehensive, low-risk approach to maintaining a healthy and efficient PostgreSQL environment.

Need Professional Database Maintenance Support?

If you would like a team of experienced database specialists to manage vacuuming, tuning, and overall database health checks for your PostgreSQL environment, contact us. We provide proactive monitoring, optimization, and maintenance to ensure your databases remain performant, stable, and always production-ready.

Related Articles

• PostgreSQL Vacuum Best Practices for a Clean Database
• Top 15 Items to Monitor in a PostgreSQL Database