Reclaiming Storage Without Locks: Low-Impact Alternatives to VACUUM FULL
- 2 days ago
- 4 min read
When PostgreSQL tables reach multi-terabyte scale, accumulated physical bloat becomes a major operational challenge. Database administrators facing depleted disk alerts often turn to VACUUM FULL to instantly shrink bloated relation files and return unallocated storage blocks back to the host operating system.
However, executing VACUUM FULL on a production database is often an infrastructure risk. VACUUM FULL rewrites the target table by acquiring an AccessExclusiveLock, completely blocking all read (SELECT) and write (INSERT, UPDATE, DELETE) operations for the entire duration of the rebuild. On large datasets, this lock window can persist for hours, triggering application timeout cascading failures.
To maintain continuous 24/7 availability while reclaiming physical storage, DBAs must replace VACUUM FULL with non-blocking, low-impact compaction patterns.
This guide details four production-grade alternatives to VACUUM FULL that reclaim disk space and defragment indexes without application downtime.
1. The Operational Pitfalls of VACUUM FULL
Before exploring alternatives, it is crucial to understand why VACUUM FULL fails modern enterprise SLAs:
+-----------------------------------------------------------------------+
| VACUUM FULL LOCKING BEHAVIOR |
| |
| [ VACUUM FULL Target Table ] |
| Acquires AccessExclusiveLock |
| | |
| +---> SELECT queries : BLOCKED (Queued in lock chain) |
| +---> INSERT queries : BLOCKED |
| +---> UPDATE queries : BLOCKED |
| +---> DELETE queries : BLOCKED |
| |
| * Storage Amplification: Requires 2x total disk space during run! |
+-----------------------------------------------------------------------+
Key Downside Factors:
Total Application Stalls: The AccessExclusiveLock blocks every incoming database connection. Even lightweight read queries get queued behind the VACUUM FULL lock request, exhausting client connection pools (such as PgBouncer).
Double Storage Amplification: VACUUM FULL builds a completely new copy of the target table and its indexes on disk before dropping the old file. If you are vacuuming a 1TB table, you must have at least 1TB of unallocated extra storage available.
2. Alternative 1: Online Table Compaction (pg_repack & pg_squeeze)
Instead of locking the primary table during a rewrite, online compaction extensions utilize shadow tables and trigger-based streaming to copy data concurrently.
1. pg_repack (Shadow Table & Trigger Replay)
pg_repack creates a new physical copy of the target table, builds its indexes, captures incoming application mutations via temporary triggers into a side log table, replays those changes onto the new copy, and swaps catalog pointers during a millisecond-level lock window.
# Compacting a bloated table online with parallel index workers
pg_repack -h localhost -U postgres -d app_db --table target_table --jobs 4
2. pg_squeeze (Logical Decoding Compaction)
Unlike pg_repack, which relies on database triggers, pg_squeeze uses PostgreSQL's internal Logical Decoding architecture to capture concurrent DML activity during the table rebuild, reducing trigger-based write amplification overhead on the primary instance.
3. Alternative 2: Concurrent Index Rebuilding (REINDEX CONCURRENTLY)
In many databases, index bloat consumes significantly more physical disk space than actual table heap bloat. Standard B-Tree indexes suffer from structural page splitting, retaining empty index pages after mass deletion events.
Executing REINDEX TABLE acquires an exclusive lock. Modern PostgreSQL releases support REINDEX CONCURRENTLY, which rebuilds index structures without blocking concurrent writes.
-- Rebuild a specific bloated index online
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;
-- Rebuild all indexes on a target table online
REINDEX TABLE CONCURRENTLY public.orders;
REINDEX CONCURRENTLY Execution Lifecycle:
1. Create new transient index catalog entries (marked as invalid).
2. Perform Pass 1 scan over table heap to populate new index.
3. Perform Pass 2 scan to include transactions initiated during Pass 1.
4. Mark new index as valid and mark old index as dead.
5. Drop the old bloated index.
DBA Rule: Running REINDEX CONCURRENTLY routinely across high-churn tables frees up gigabytes of memory inside shared_buffers without touching table locks.
4. Alternative 3: Replacing Mass Deletes with Declarative Partitioning
When application logic deletes historical data (e.g., deleting audit logs older than 90 days), running continuous DELETE queries generates massive dead tuple bloat, forcing autovacuum to work overtime.
The Zero-Bloat Solution: Partition Pruning
By refactoring monolithic tables into Declarative Range Partitions (e.g., partitioned by month or week), space reclamation becomes instantaneous.
-- Drop an entire month of historical data instantly with zero bloat
DROP TABLE audit_logs_y2025m12;
Mass DELETE Pattern: [ Scan Table ] -> [ Write WAL ] -> [ Generate Dead Tuples ] -> [ Run VACUUM ]
Partition Drop Pattern: [ DROP TABLE ] -> Instant OS File Unlink (Zero Bloat / Zero I/O)
Dropping an isolated partition removes the physical file from the underlying operating system file system immediately, bypassing the Free Space Map (FSM) entirely and requiring zero vacuuming.
5. Alternative 4: Batch Deletion with Incremental Heap Truncation
If refactoring a table into partitions is not immediately feasible, never issue a un-bounded DELETE FROM table WHERE ... query affecting millions of rows. Massive deletions lock rows, flood transaction logs (WAL), and create dense bloat blocks.
The Batching Strategy
Execute deletions in small, controlled batches interleaved with VACUUM calls to encourage tail-page file truncation:
-- Batch deletion loop to minimize WAL and lock footprint
DO $$
DECLARE
rows_deleted INT;
BEGIN
LOOP
DELETE FROM audit_logs
WHERE id IN (
SELECT id FROM audit_logs
WHERE created_at < NOW() - INTERVAL '90 days'
LIMIT 5000
);
GET DIAGNOSTICS rows_deleted = ROW_COUNT;
EXIT WHEN rows_deleted = 0;
-- Brief pause to allow background autovacuum cleanup
PERFORM pg_sleep(0.5);
END LOOP;
END $$;
Architectural Decision Matrix
Method | Lock Level | Reclaims Disk to OS | Application Downtime | Extra Storage Needed |
VACUUM FULL | AccessExclusiveLock | ✅ 100% | ❌ High Downtime | 100% of Table Size |
pg_repack | RowExclusiveLock | ✅ 100% | ✅ Zero Downtime | 100% of Table Size |
REINDEX CONCURRENTLY | Minimal / Concurrent | ✅ Index Space Only | ✅ Zero Downtime | 100% of Index Size |
Partition DROP TABLE | Schema Metadata Lock | ✅ 100% | ✅ Zero Downtime | 0% (Immediate Free) |
Batch Delete + Vacuum | Row-Level Locks | ⚠️ Tail Pages Only | ✅ Zero Downtime | 0% |
Summary
Avoid VACUUM FULL in production unless you have an explicit maintenance window that tolerates complete database locking.
Use pg_repack or pg_squeeze for online, non-blocking table compaction when physical disk reclamation is mandatory.
Leverage REINDEX CONCURRENTLY to clean bloated B-Tree indexes continuously with zero impact on application writes.
Adopt Declarative Partitioning for time-series or logging data to replace expensive DELETE operations with zero-cost DROP TABLE statements.
Comments