VACUUM, ANALYZE, and VACUUM FULL command in PostgreSQL DBA

In PostgreSQL, VACUUM, ANALYZE, and VACUUM FULL are essential maintenance operations that help optimize performance and manage database storage. Here's a concise explanation with examples tailored for a PostgreSQL DBA:

What is MVCC in PostgreSQL?

MVCC stands for Multi-Version Concurrency Control. It's a core concept in PostgreSQL that allows multiple users to read and write to the database at the same time—without locking each other out.

MVCC enables concurrent access to the database while maintaining data consistency and isolation as defined by the ACID properties.

Instead of locking rows when a user accesses them, PostgreSQL:

• Keeps multiple versions of a row (called tuples) in the database.
• Each row has hidden metadata:

           xmin = ID of the transaction that created the row

           xmax = ID of the transaction that deleted or updated the row

PostgreSQL uses MVCC (Multi-Version Concurrency Control) to manage concurrent transactions by not overwriting rows. Instead, it creates a new version of a row every time a row is updated or deleted.

• The old version becomes a dead tuple (no longer visible to any active transaction).

• These dead tuples remain on disk until they're cleaned up.

These dead tuples are  cleaned up by command below :

• VACUUM → Marks space as reusable

• VACUUM FULL → Physically compacts the table

VACUUM

In PostgreSQL, when a row is updated or deleted, the old version isn't immediately removed—it becomes a dead tuple. These dead tuples take up space and, over time, can bloat the table, making queries slower and increasing disk usage.

• VACUUM scans the table to mark dead tuples as reusable, making space for future inserts/updates.

• It does not shrink the table file size on disk.

• Usually handled automatically by the autovacuum daemon, but can be run manually.

VACUUM my_table;

ANALYZE

ANALYZE helps PostgreSQL’s query planner make smart decisions.

• It collects statistics about the distribution of data in tables and columns (e.g., number of distinct values, nulls, histogram of values).

• These stats are used to choose the best execution plan for a query (like whether to use an index or not).

• Without up-to-date stats, PostgreSQL may choose suboptimal plans, slowing down performance.

• It use After bulk INSERT, UPDATE, or DELETE.

ANALYZE my_table;

VACUUM FULL

VACUUM FULL goes a step further than regular VACUUM.

• It rebuilds the entire table, compacting it by removing all dead space.

• It actually shrinks the physical file on disk.

• However, it takes an exclusive lock on the table, meaning no reads or writes can happen during the process.

• Reclaims all space and compacts the table.

VACUUM FULL my_table;

Combined Usage:

VACUUM ANALYZE my_table;