PostgreSQL VACUUM vs VACUUM FULL

In this tutorial, I will break down how standard VACUUM and VACUUM FULL operate in depth, their locking mechanisms and disk space implications, and how to safely maintain high-throughput PostgreSQL environments.

PostgreSQL VACUUM vs VACUUM FULL

What Is Standard VACUUM (and Autovacuum)?

Standard VACUUM (often executed simply as VACUUM or handled in the background by the Autovacuum daemon) is PostgreSQL’s non-blocking maintenance process.

How Standard VACUUM Operates

1. Scans Pages: It traverses table pages and identifies dead tuples whose xmax is older than the oldest active transaction.
2. Marks Space Reusable: It removes dead tuples and unlinks them from indexes, marking their storage space within the page as free for future inserts or updates.
3. Updates the Free Space Map (FSM): It updates the FSM so new incoming INSERT operations can reuse those available slots.
4. Updates the Visibility Map (VM): It updates the VM to record which pages contain only tuples visible to all current and future transactions, accelerating Index-Only Scans.
5. Freezes Old Transaction IDs: It freezes old transaction IDs (xids) to prevent catastrophic Transaction ID Wraparound.

Crucial Rule: Standard VACUUM does not shrink the physical file size on disk in 99% of scenarios. It defragments data internally within the existing 8KB pages so future writes do not require expanding the file on the operating system filesystem. (The only exception is if dead pages sit at the very end/tail of the physical table file).

What Is VACUUM FULL?

VACUUM FULL is a brute-force, complete rewrite of an entire table and all its associated indexes.

How VACUUM FULL Operates

1. Allocates New Disk File: PostgreSQL creates a brand-new, empty table file on disk.
2. Copies Live Data Only: It reads the existing table from start to finish, skipping all dead tuples, and writes only active, live tuples sequentially into the new file.
3. Rebuilds Indexes: Once the new table file is written, PostgreSQL rebuilds every secondary index from scratch.
4. Replaces Old File: It drops the old, bloated table and index files from disk and swaps in the new, compact data files.

The Upside: VACUUM FULL reclaims 100% of dead space and immediately returns physical disk space to the underlying operating system.

The Downside: It takes an ACCESS EXCLUSIVE lock, completely blocking all reads and writes until the entire rewrite is finished.

VACUUM vs. VACUUM FULL: At-a-Glance Comparison

The matrix below highlights the operational differences between standard VACUUM and VACUUM FULL:

Architectural Feature	Standard VACUUM	VACUUM FULL
Lock Level Acquired	SHARE UPDATE EXCLUSIVE	ACCESS EXCLUSIVE
Allows Concurrent Reads (SELECT)?	Yes (Non-blocking)	No (Blocks all queries)
Allows Concurrent Writes (INSERT/UPDATE)?	Yes (Non-blocking)	No (Blocks all writes)
Reclaims Space to Operating System?	Rarely (only trailing empty pages)	Yes (100% of bloat reclaimed)
Disk Space Overhead Required	Minimal (Memory buffer allocations)	High (~2x the active table size)
Rebuilds Indexes?	No (cleans dead index pointers)	Yes (rebuilds all indexes)
Automated via Autovacuum?	Yes (runs continuously)	No (manual execution only)
Execution Speed	Fast / Throttled in background	Slow (Rewrites entire physical file)

Deep Dive: Table Bloat and Space Reclamation

One of the most persistent misunderstandings in PostgreSQL operations is why running a standard VACUUM on a 500 GB table with 300 GB of dead tuples does not reduce the file size shown by df -h or ls -l.

The Fragmentation Problem

Imagine a table consisting of 1,000 pages. If every page contains 40% dead tuples distributed evenly, standard VACUUM frees the space inside each page.

• The operating system still sees a 1,000-page file.
• However, your next 400 pages worth of INSERT statements will fill those internal gaps without increasing the file size on disk.

Standard VACUUM is like emptying specific drawers in a filing cabinet: the cabinet still takes up the same physical footprint in the room, but you now have space to put new folders inside.

VACUUM FULL, on the other hand, throws away the old cabinet, buys a smaller cabinet that fits only your current folders, and discards the empty space entirely.

Locking Modes and Concurrency Impacts

In production environments, locking dictates availability. Understanding lock conflicts prevents accidental database outages.

                    LOCK HIERARCHY CONFLICTS
                    
Standard VACUUM:
[ SHARE UPDATE EXCLUSIVE ] ──► Conflicts with: VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY, ALTER TABLE
                               Coexists with:  SELECT, INSERT, UPDATE, DELETE (Zero Application Downtime)

VACUUM FULL:
[ ACCESS EXCLUSIVE ]       ──► Conflicts with: ALL LOCKS
                               Blocks:         SELECT, INSERT, UPDATE, DELETE, CONNECTIONS (Full Downtime)

The Danger of VACUUM FULL in Production

When you run VACUUM FULL orders;, PostgreSQL requests an ACCESS EXCLUSIVE lock.

1. Any query running on the orders table when VACUUM FULL starts must finish before the lock is granted.
2. While VACUUM FULL waits to acquire its lock, every subsequent query (including simple SELECT queries) gets queued behind it.
3. Within seconds, your database connection pool saturates, application connection queues fill up, and users experience total application timeout outages.

Frequently Asked Questions (FAQs)

Can I cancel a running VACUUM FULL without corrupting my data?

Yes. PostgreSQL is fully ACID-compliant. If you cancel a VACUUM FULL operation (Ctrl+C or pg_cancel_backend(pid)), the transaction rolls back cleanly, the temporary table file is deleted, and your original table remains intact.

Why did my disk space not decrease after running standard VACUUM?

Standard VACUUM does not return physical space to the OS file system. It cleans the internal pages and records the empty space in the Free Space Map (FSM) so subsequent INSERT and UPDATE queries can reuse it without allocating new disk blocks.

Does standard VACUUM lock out read and write queries?

No. Standard VACUUM takes a SHARE UPDATE EXCLUSIVE lock, which allows concurrent SELECT, INSERT, UPDATE, and DELETE statements to execute uninterrupted.

How do I prevent Transaction ID (XID) Wraparound?

Autovacuum automatically runs aggressive emergency vacuuming when tables approach the autovacuum_freeze_max_age threshold (default 200 million transactions). Never disable autovacuum globally, as it protects your database from forced read-only shutdown caused by XID wraparound.

Summary and Key Takeaways

Understanding the architectural distinction between VACUUM and VACUUM FULL is essential for operating high-uptime PostgreSQL databases:

• Use Standard VACUUM (via Autovacuum): For 99% of regular operations. It operates concurrently without blocking reads or writes, defragmenting 8KB data pages and keeping free space available for new transactions.
• Use VACUUM FULL: Only during planned maintenance windows when massive, one-time bulk deletions have left severe bloat and you have both the time and disk headroom to lock the table completely.
• Use pg_repack or REINDEX CONCURRENTLY: When you need to reclaim disk space to the operating system on active, live production tables without incurring application downtime.

You may also like the following articles:

• PostgreSQL JSON vs JSONB
• PostgreSQL System Requirements
• What Is Postgresql Used For