PostgreSQL databases require periodic maintenance to reclaim storage occupied by outdated rows that are no longer visible in queries. The vacuum command in PostgreSQL serves this purpose by marking storage used by dead tuples as reusable. This prevents table and index bloat, freeing up space and improving overall database performance.
Understanding PostgreSQL Storage
PostgreSQL uses disk pages of typically 8KB size as the basic storage block. Tables and indexes consist of chains of pages storing data in tuples. Tuples are marked as visible/invisible via commit timestamp columns xmin/xmax tracking transaction ids.
Autovacuum daemon and manual vacuuming help recycle storage by verifying tuple visibility and marking the space from invisible tuples as reusable. If vacuuming is not done properly, outdated tuples accumulate and database bloats – this can also lead to transaction ID wraparound issues.
When to Run VACUUM
Vacuum should be run:
-
On tables that are frequently updated or bulk inserted into – this generates significant obsolete tuple accumulation needing cleanup. Check pgstat* tables.
-
When overall database disk usage is high and space needs to be reclaimed. Monitoring tools like check_postgres can identify this condition.
-
If business logic markers indicate – like number of customer records deleted crossing a threshold.
-
Prior to major operations like bulk data loads, index rebuilds etc. Vacuuming beforehand improves performance.
Too frequent vacuuming can have performance impact due to increased I/O. Finding optimal frequencies requires studying database access patterns and change rates. For analytics databases, vacuum runs can be scheduled during daily ETL batch windows.
Here is an example query to check database size metrics – useful for analyzing vacuum cleaning effectiveness:
SELECT
datname AS "Database",
pg_size_pretty(pg_database_size(datname)) AS "Size",
pg_size_pretty(pg_database_size(datname) - pg_database_size(datname, true)) AS "External Size"
FROM pg_database; VACUUM Command Syntax and Options
The basic syntax for VACUUM is:
VACUUM [ FULL | FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_name ]Key options:
-
FULL – more aggressive vacuuming, reclaims all space including that occupied by rolled-back and uncommitted transactions. Requires AccessExclusive lock on each table while processing.
-
FREEZE – similar to FULL, but also sets flags to "freeze" old tuple visibility details. Achieves snapshot stability in case of transaction ID wraparound issues.
-
VERBOSE – prints progress details like number of dead tuples removed and pages affected. Useful for logging and monitoring.
-
ANALYZE – updates statistics after vacuuming each table. Important for query performance.
If no table name is specified, the entire database is vacuumed:
VACUUM VERBOSE ANALYZE;Here is sample VERBOSE output with details on transaction visibility and tuple stats:
INFO: vacuuming "abc.lines"
INFO: scanned index "lines_fk_product" to remove 378 row versions
DETAIL: CPU 0.01s/0.08u sec elapsed 0.15 sec.
INFO: "lines": removed 927 delete row versions in 88 pages
DETAIL: 0 index row versions were removed by the vacuum operation. 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "lines": found 2000 removable, 8000 nonremovable row versions in 1000 out of 1300 pages
DETAIL: 0 dead row versions cannot be removed yet. There were 44 unused item identifiers. Skipped 0 pages due to buffer pins.Manual Vacuuming vs Autovacuum Daemon
The autovacuum daemon handles routine vacuuming like removing expired rows. But manual vacuuming is still needed:
- After large batch DELETE/UPDATE to immediately reclaim space
- To vacuum optimizer statistics via ANALYZE
- Before major operations like data loads requiring contiguous free space
- To aggressively vacuum rarely-accessed tables not touched by autovacuum
- To test different vacuum strategies like performance of FULL
Tunable parameters like autovacuum_vacuum_threshold determine how often autovacuum runs. This can be adjusted if autovacuum is falling behind on space reclamation.
Storage Reclamation Methods
While VACUUM is the standard method for storage recovery, alternatives like CLUSTER, table truncation and REINDEX can also be considered:
| Method | Description |
|---|---|
| CLUSTER | Physically rewrites tablepages based on an index ordering. More aggressive space reclaiming. Use for static reference tables. |
| VACUUM FULL | Marks pages as reusable after shrinking but does not physically defragment pages. Quicker than CLUSTER. |
| REINDEX | Rebuilds an index using fresh table statistics. Can reclaim index bloat. |
| Truncating tables | Deletes all rows and frees up used storage. Fast way to reclaim entire tablespace.losing all table data. |
Monitoring Vacuum Progress
Vacuum operations can be monitored by:
- Enabling VERBOSE mode as shown earlier
- Logging output via script
- Parsing pg_stat_progress_vacuum view
- Checking pg_stat_all_tables – n_dead_tup stats indicate tuples marked dead
- Monitoring changes in database size
This data can help establish optimal vacuum frequencies.
Vacuum Best Practices
Aggressively vacuum volatile tables: Tables being updated heavily causing significant bloat should be vacuumed frequently – even nightly.
Avoid vacuuming during peak hours: Schedule vacuum runs during daily ETL windows or overnight during low load.
Vacuum in stages: Multi-stage vacuum runs via multiple parallel workers can utilize multiple CPUs and reduce chances of conflicts.
Monitor I/O patterns: Use iotop tool to verify if vacuum operations are skewed towards certain tablespaces generating intense I/O load.
Test with different options: Evaluating FULL/FREEZE options can help gauge if more aggressive vacuuming is beneficial.
Autovacuum Daemon Tuning
If autovacuum daemon is unable to keep pace with database changes, parameters like autovacuum_vacuum_threshold can be tuned per table. Threshold determines number of updated/deleted tuples triggering vacuum.
Lower thresholds lead to frequent vacuum runs – useful for volatile tables prone to bloat. This balances autovacuum overhead vs storage reclamation needs.
Tools for Online Vacuuming
The main drawback of standard VACUUM FULL is taking an exclusive lock on the table being processed. This can stall updates for lengthy operations. pg_repack is an extension that enables full vacuuming online avoiding locks:
pg_repack [options] <db>.<table>Advantages:
- Vacuum runs are non-blocking for updates/queries
- New indexes can also be efficiently added in same rebuild process
Limitation is temporary 2X disk space is required.
Conclusion
The PostgreSQL vacuum command serves the important role of storage recovery and bloat prevention. Periodic manual vacuuming along with autovacuum daemon is needed to tune the delicate balance between space reclamation and overhead. Production databases require extensive monitoring and analysis to determine optimal vacuum frequencies and reclamation approaches.
