VACUUM FULL Execution Planning

Tokens

Latency

Turns

Tool Calls

Schema Calls

Response

vs

I'll help you plan this critical operation. VACUUM FULL on a 500GB table is a major undertaking that requires careful planning. ## Key Planning Facts **Duration Estimate:** - **4-12 hours** for a 500GB table (varies by hardware) - Rough estimate: 10-30 GB/hour on modern SSDs, slower on HDDs - Depends on: disk I/O speed, CPU, indexes, data types, maintenance_work_mem setting **Disk Space Required:** - **~500GB additional free space** (full table size, not just the bloated portion) - VACUUM FULL creates a complete new copy of the table before dropping the old one - You need space for both versions simultaneously, plus indexes **Impact on Queries:** - **EXCLUSIVE LOCK** - blocks ALL reads and writes to the table - The table is completely unavailable during the entire operation - Connection attempts will queue and may timeout - No concurrent queries possible ## Better Production Approach For production, consider **pg_repack** instead: ```bash # pg_repack advantages: # - No exclusive lock (uses triggers for concurrent writes) # - Only brief locks at start/end # - Queries can continue running # Install pg_repack extension CREATE EXTENSION pg_repack; # Run (can take similar time but non-blocking) pg_repack -t your_table_name -d your_database ``` ## If You Must Use VACUUM FULL **Pre-operation checklist:** 1. **Verify disk space:** ```sql -- Check current table + indexes size SELECT pg_size_pretty(pg_total_relation_size('your_table')); -- Check available disk space \! df -h /var/lib/postgresql/data ``` 2. **Plan maintenance window:** - Schedule during lowest traffic period - Communicate downtime to stakeholders - Add 50% buffer to time estimate 3. **Optimize settings temporarily:** ```sql SET maintenance_work_mem = '2GB'; -- Increase for faster operation ``` 4. **Take a backup first:** ```bash pg_dump -Fc -t your_table your_db > backup.dump ``` 5. **Execute:** ```sql VACUUM FULL VERBOSE your_table_name; ``` 6. **Post-operation:** ```sql ANALYZE your_table_name; -- Update statistics REINDEX TABLE your_table_name; -- Rebuild indexes ``` ## Alternative: Avoid VACUUM FULL Entirely **Best practice for production:** ```sql -- Regular autovacuum tuning to prevent 60% bloat: ALTER TABLE your_table SET ( autovacuum_vacuum_scale_factor = 0.05, -- More aggressive autovacuum_analyze_scale_factor = 0.02 ); -- Regular VACUUM (no FULL) - non-blocking VACUUM (VERBOSE, ANALYZE) your_table; ``` ## Decision Matrix **Use VACUUM FULL if:** - You have a scheduled maintenance window - The table can be completely offline for 4-12 hours - You have 500GB+ free disk space - Simplicity is preferred **Use pg_repack if:** - You need near-zero downtime - You have 500GB+ free disk space - You can install an extension **Use regular maintenance if:** - You can wait for gradual space reclamation - Disk space pressure isn't critical - You can't afford the I/O impact now Would you like help with any specific approach, or would you like me to create a detailed runbook for your production execution?

I'll help you plan this VACUUM FULL operation. This is a high-risk operation that requires careful planning. ## Key Impacts of VACUUM FULL **Locking:** VACUUM FULL takes an **ACCESS EXCLUSIVE lock** on the table for the entire duration. This blocks ALL queries (reads and writes) against the table - making it essentially unavailable. **Duration Estimate:** For a 500GB table (assuming ~300GB after reclaiming 60% bloat): - **Optimistic:** 2-4 hours on modern hardware (fast SSD, 16+ cores) - **Realistic:** 4-8 hours on typical production hardware - **Conservative:** 8-12+ hours on slower disk or high CPU contention Factors affecting speed: - Disk I/O throughput (sequential read/write speed) - CPU cores for sorting/reindexing - Number of indexes (each must be rebuilt) - Row width and data types ## Disk Space Requirements VACUUM FULL needs space to build a **complete copy** of the table: ``` Required free space ≈ Current table size + All indexes Minimum: ~500GB (the full current bloated size) Safer: ~600GB (20% buffer for indexes and temp work) ``` Check current space: ```sql SELECT pg_size_pretty(pg_total_relation_size('your_table')) as total_size, pg_size_pretty(pg_relation_size('your_table')) as table_size, pg_size_pretty(pg_total_relation_size('your_table') - pg_relation_size('your_table')) as indexes_size; ``` ## Better Alternatives for Production Given the full table lock, consider these instead: ### 1. **pg_repack** (Recommended) - Rebuilds table online with minimal locking - Only brief exclusive lock at start/end - Requires 2x disk space temporarily - Much safer for production ```bash pg_repack -t your_table -d your_database --no-order ``` ### 2. **Partitioning + Rolling VACUUM** If not already partitioned, consider: - Create new partitioned table - Migrate data in batches - Drop old table ### 3. **Regular VACUUM + Tuning** Sometimes bloat can be managed without VACUUM FULL: ```sql VACUUM (VERBOSE, ANALYZE) your_table; ``` Then tune autovacuum to prevent future bloat: ```sql ALTER TABLE your_table SET ( autovacuum_vacuum_scale_factor = 0.05, autovacuum_vacuum_threshold = 5000 ); ``` ## If You Must Use VACUUM FULL **Pre-flight checklist:** 1. **Verify disk space:** ```bash df -h /var/lib/postgresql/data ``` 2. **Estimate actual time with test:** ```sql -- Test on a copy/replica first VACUUM (FULL, VERBOSE) your_table; ``` 3. **Schedule maintenance window:** - Plan for 2x your estimate - Off-peak hours - Notify all stakeholders 4. **Monitor during operation:** ```sql -- In another session SELECT pid, query, state, wait_event_type, wait_event FROM pg_stat_activity WHERE query LIKE '%VACUUM%'; ``` 5. **Have rollback plan:** - Can you fail over to a replica? - Can you cancel mid-operation? (Ctrl+C will abort but undo all work) - Backup strategy if disk fills? ## Recommended Approach ```sql -- 1. Check bloat SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size FROM pg_stat_user_tables WHERE tablename = 'your_table'; -- 2. Use pg_repack instead (if possible) -- Install: apt-get install postgresql-xx-repack -- Run: pg_repack -t your_table -d your_db -- 3. If VACUUM FULL is required: -- Set lock timeout to fail fast if blocked SET lock_timeout = '5s'; VACUUM (FULL, VERBOSE, ANALYZE) your_table; ``` **What would you like to do?** Can you install pg_repack, or do you need to proceed with VACUUM FULL? I can help you prepare the specific commands and monitoring strategy based on your choice.

Start by confirming the bloat metrics are accurate — check `postgresql-table-bloat` to validate the 60% bloat claim and `table-size-size` to get the exact current table size. With a 500GB table at 60% bloat, you're looking at roughly 300GB of actual data and 200GB of dead space. This validation is critical because it determines whether the operation is even necessary — the `table-bloat-critical` insight recommends action at 100%+ bloat, so 60% might not require VACUUM FULL at all.

The `vacuum-full-disk-space-exclusive-lock` insight is explicit: VACUUM FULL creates a fresh copy of the table, potentially doubling disk usage temporarily. For your 500GB table, you need at least 500GB of free disk space, ideally more for safety margin. If you're already low on disk space (which is often why people consider VACUUM FULL), running it could exhaust your disk mid-operation and cause catastrophic failure. Check `df -h` on the data partition before proceeding.

Before committing to VACUUM FULL, seriously consider pg_repack extension instead. The insights on `vacuum-full-exclusive-lock`, `vacuum-full-blocks-production`, and `cluster-blocks-all-reads` all recommend pg_repack because it achieves the same space reclamation without taking exclusive locks that block all queries including SELECTs. For production systems that can't afford hours of complete table unavailability, pg_repack is the standard solution — it works online with minimal lock time.

VACUUM FULL execution time is primarily I/O-bound. If your storage can sustain 100 MB/s write throughput, a 500GB table will take roughly 5,000 seconds (1.4 hours) minimum, but realistically expect 2-4 hours depending on index rebuilding. Run a test write benchmark on your storage (`dd` or `fio`) to get actual throughput numbers. Factor in that this entire duration will be exclusive lock time where the table is completely unusable — every query against it will block.

Check `postgresql-locks` during normal operations to understand what queries typically access this table, because VACUUM FULL's ACCESS EXCLUSIVE lock will block everything — all SELECTs, INSERTs, UPDATEs, DELETEs. Review your application code and monitoring to identify which services depend on this table. If any critical user-facing features query this table, they'll timeout or fail for the entire VACUUM FULL duration, which could be hours.

Based on your execution time estimate (likely 2-4 hours for 500GB), schedule a maintenance window that's at least 50% longer than your estimate to account for unexpected issues. The `vacuum-full-blocks-production` insight emphasizes never running this during business hours. Have a rollback plan: if VACUUM FULL fails mid-operation or takes too long, you may need to cancel it (Ctrl+C or `pg_cancel_backend`), which leaves the table in its original bloated state but at least functional.

Before pulling the trigger on VACUUM FULL, the `table-bloat-critical` and `vacuum-full-exclusive-lock` insights both recommend trying regular VACUUM first and tuning autovacuum parameters to prevent future bloat accumulation. At 60% bloat (below the 100% critical threshold), aggressive regular VACUUM might reclaim enough space without the downtime. Adjust `autovacuum_vacuum_scale_factor` and `autovacuum_vacuum_cost_delay` to be more aggressive on this table specifically using per-table storage parameters.