Vacuum vs Vacuum Full in PostgreSQL: Key Differences and Usage

In PostgreSQL, VACUUM reclaims space from deleted rows without locking the table, while VACUUM FULL fully rewrites the table to compact it and requires an exclusive lock. VACUUM FULL frees more disk space but is slower and blocks access during its operation.

Quick Comparison

This table summarizes the main differences between VACUUM and VACUUM FULL in PostgreSQL.

Feature	VACUUM	VACUUM FULL
Purpose	Reclaims space from dead tuples	Rewrites entire table to compact it
Locking	Does not block reads/writes	Requires exclusive lock, blocks access
Disk Space Reclaimed	Frees space for reuse, not always returned to OS	Returns space to OS by shrinking file size
Performance Impact	Lightweight and fast	Heavy and slow operation
Use Case	Routine maintenance	When table bloat is severe
Frequency	Run regularly	Run occasionally as needed

Key Differences

VACUUM cleans up dead rows left by updates and deletes so that space can be reused by new data. It does this without locking the table, allowing normal database operations to continue uninterrupted. However, it does not reduce the physical size of the table file on disk.

In contrast, VACUUM FULL rewrites the entire table into a new disk file, compacting it and physically reducing its size. This operation requires an exclusive lock on the table, blocking all reads and writes until it finishes. Because of this, VACUUM FULL is slower and more disruptive but is effective at reclaiming disk space when a table has grown large due to many deletions.

In summary, VACUUM is a lightweight cleanup tool for routine maintenance, while VACUUM FULL is a heavy-duty operation used to recover disk space and reduce table bloat.

Code Comparison

Here is how you run a standard VACUUM command on a table named my_table:

sql

VACUUM my_table;

VACUUM FULL Equivalent

To perform a full vacuum that compacts the table and returns space to the operating system, use:

sql

VACUUM FULL my_table;

When to Use Which

Choose VACUUM for regular database maintenance to keep tables healthy without interrupting users. It is fast and safe to run frequently.

Choose VACUUM FULL only when you notice significant table bloat and need to reclaim disk space urgently, understanding it will lock the table and impact availability. Use it sparingly to avoid downtime.

Key Takeaways

Use VACUUM regularly to clean dead rows without locking tables.

VACUUM FULL rewrites tables to reclaim disk space but locks the table during operation.

Avoid frequent VACUUM FULL as it is slow and blocks access.

Choose VACUUM FULL only when disk space recovery is critical.

Routine VACUUM keeps your database performant and responsive.