How to Run VACUUM in PostgreSQL: Syntax and Examples

To run VACUUM in PostgreSQL, use the command VACUUM; to clean up dead rows and free space. For more thorough cleanup, use VACUUM FULL; which locks the table but reclaims more space.

Syntax

The basic syntax for the VACUUM command in PostgreSQL is:

• VACUUM; - Cleans up dead tuples without locking the table for writes.
• VACUUM FULL; - Rewrites the entire table, reclaiming more space but locks the table during operation.
• VACUUM ANALYZE; - Cleans up and updates statistics for the query planner.
• VACUUM VERBOSE; - Shows detailed progress information.

sql

VACUUM [FULL] [FREEZE] [VERBOSE] [ANALYZE] [table_name];

Example

This example runs a basic vacuum on the users table and then runs a full vacuum with verbose output to see detailed progress.

sql

VACUUM users;

VACUUM FULL VERBOSE users;

Output

INFO:  vacuuming "public.users"
INFO:  index "users_pkey" now contains 1000 row versions
INFO:  "users": found 0 removable, 10 nonremovable row versions in 10 pages
INFO:  "users": 10 dead row versions cannot be removed yet
INFO:  vacuuming "public.users"
INFO:  index "users_pkey" now contains 1000 row versions
INFO:  "users": found 0 removable, 0 nonremovable row versions in 10 pages
INFO:  "users": 0 dead row versions cannot be removed yet

Common Pitfalls

Common mistakes when running VACUUM include:

• Running VACUUM FULL on large tables during peak hours, causing table locks and blocking writes.
• Not running ANALYZE after vacuuming, which can lead to outdated query planner statistics.
• Ignoring autovacuum settings, which automatically maintain tables and reduce the need for manual vacuuming.

Always prefer regular VACUUM or rely on autovacuum for routine maintenance, and reserve VACUUM FULL for special cases.

sql

/* Wrong: Running VACUUM FULL on a busy table without maintenance window */
VACUUM FULL users;

/* Right: Run during low traffic or maintenance window */
-- Schedule VACUUM FULL during off-peak hours

Quick Reference

Command	Description
VACUUM	Clean up dead rows without locking the table
VACUUM FULL	Reclaim more space but locks the table
VACUUM ANALYZE	Clean up and update statistics
VACUUM VERBOSE	Show detailed progress information

Key Takeaways

Use VACUUM regularly to clean dead rows and maintain performance.

Reserve VACUUM FULL for special cases as it locks tables during operation.

Combine VACUUM ANALYZE to update query planner statistics.

Autovacuum usually handles routine vacuuming; manual vacuum is for special needs.

Avoid running heavy vacuum commands during peak database usage to prevent blocking.