How does the VACUUM command work?

The VACUUM command in PostgreSQL is used to reclaim storage and maintain database performance. When you delete or update rows, the old tuples remain on disk until VACUUM runs. It marks dead tuples as free and updates statistics for the query planner. There are two variants: VACUUM and VACUUM FULL. The regular VACUUM cleans up dead tuples but keeps the table size unchanged, while VACUUM FULL rewrites the entire table, compacts it, and reduces its physical size. In Snowflake, VACUUM is used to drop obsolete data from a table after a time‑to‑live (TTL) policy or manual purge. It removes data files that are no longer needed, freeing storage and improving query performance. Both systems use VACUUM to prevent table bloat, keep statistics accurate, and ensure efficient query execution. The command can be run manually or scheduled as a maintenance job. It is essential for long‑running databases that experience frequent updates or deletes.