Dead Tuple Cleanup and Vacuum Mechanics

In this lesson, you will learn how PostgreSQL identifies "dead" data and reclaims the storage space it occupies through the VACUUM process.

The Problem: Dead Tuples

Because PostgreSQL uses Multiversion Concurrency Control (MVCC), when you UPDATE or DELETE a row, the database doesn't immediately overwrite the old data. Instead, it marks the old version of the row—the dead tuple—as invisible to future transactions.

Over time, these dead tuples accumulate, bloating your heap files (tables) and wasting disk space. Because the database must scan these empty slots when performing queries, performance can degrade significantly.

How Autovacuum Identifies Dead Tuples

PostgreSQL tracks visibility using two hidden columns: xmin and xmax.

• xmin: The ID of the transaction that inserted the tuple.
• xmax: The ID of the transaction that deleted or updated the tuple.

When the autovacuum process wakes up, it scans the pages of a table to identify tuples where the xmax is older than the oldest active transaction in the system. If no transaction can possibly see that row anymore, the tuple is officially "dead" and ready for cleanup.

Reclaiming Space

Once the vacuum process identifies these dead tuples, it performs two main actions:

1. Free Space Map (FSM) Update: It marks the space occupied by the dead tuples as "available" in the table's Free Space Map. This allows subsequent INSERT operations to reuse those slots immediately without needing to grow the file on disk.
2. Index Cleanup: It scans the table's indexes to remove entries that point to the now-deleted tuples, ensuring that your B-tree lookups stay lean and efficient.

Consider this sequence:

-- Step 1: Update a row
UPDATE employees SET salary = 60000 WHERE id = 1;

-- The old version (salary 50000) is now a "dead tuple"
-- Autovacuum identifies this based on the transaction ID (xmax)
-- Once cleaned, the slot is marked as free in the FSM

By recycling these slots, VACUUM prevents your table files from growing indefinitely. Without it, PostgreSQL would eventually run out of disk space, even if your actual dataset remained small.

Key Takeaway: Autovacuum reclaims space by identifying tuples that are no longer visible to any active transaction, marking their slots as reusable in the Free Space Map, and pruning obsolete index entries.