VACUUM and its importance in PostgreSQL

Introduction

VACUUM cleans up old data in the database to keep it fast and efficient.

After deleting many rows from a table to free space.

When you want to prevent the database from slowing down over time.

To update statistics that help the database choose the best way to run queries.

When you want to avoid running out of disk space due to leftover data.

Before backing up the database to ensure data is tidy.

Syntax

PostgreSQL

VACUUM [FULL] [VERBOSE] [table_name];

FULL makes VACUUM clean more deeply but takes longer and locks the table.

VERBOSE shows detailed information about what VACUUM is doing.

Examples

Clean all tables in the current database quickly without locking.

PostgreSQL

VACUUM;

Clean the table named my_table and show details about the process.

PostgreSQL

VACUUM VERBOSE my_table;

Clean my_table deeply, reclaiming space but locking the table during the process.

PostgreSQL

VACUUM FULL my_table;

Sample Program

This example creates a table, inserts three rows, deletes one row, then runs VACUUM VERBOSE to clean up and show details.

PostgreSQL

CREATE TABLE test_vacuum (id SERIAL PRIMARY KEY, data TEXT);
INSERT INTO test_vacuum (data) VALUES ('row1'), ('row2'), ('row3');
DELETE FROM test_vacuum WHERE id = 2;
VACUUM VERBOSE test_vacuum;

Important Notes

Regular VACUUM runs automatically in PostgreSQL, but manual VACUUM can help after big changes.

VACUUM FULL locks the table, so use it when you can afford downtime.

Running VACUUM helps keep your database fast and prevents disk space from filling up.

Summary

VACUUM cleans up old, deleted data to keep the database efficient.

Use VACUUM after large deletes or updates to free space and improve speed.

VACUUM FULL reclaims more space but locks the table during the process.