# 5.4 The Housekeepers (Vacuum & Freezing)
In the Elephant Cafe, data never truly vanishes. Old row versions (dead tuples) remain on the page, and Transaction IDs must be "frozen" to prevent the 32-bit identity crisis of **[[Chapter 4/4.6 - The Infinite Calendar (Transaction ID Wraparound)|XID Wraparound]]**.
These maintenance tasks are handled by a dedicated crew of **Background Workers**.
## The RAM Managers: BGWriter vs. Checkpointer
There are two primary workers responsible for managing the flow of data between the **[[Chapter 5/5.2 - The Warming Rack (Shared Buffers)|Warming Rack (RAM)]]** and disk. They serve different, essential masters:
### 1. The Background Writer (Utility)
The **Background Writer** is a proactive cleaner. Its master is **Efficiency**. It scans the Warming Rack for "dirty" pages and gently flushes them to disk *before* anyone actually needs the slot. This ensures that when a new Staff Member arrives with a fresh plate, they don't have to wait for a long, slow disk-write before they can sit down.
### 2. The Checkpointer (Safety)
The **Checkpointer** is a formal foreman. Its master is **Durability**. At regular intervals, it ensures that every promise written in the **[[Chapter 4/4.1 - The Pocket Diary (WAL & fsync)|Pocket Diary (WAL)]]** has been physically etched into the Filing Cabinet. Once finished, it marks the diary so old logs can be recycled.
> [!NOTE]
> **The Difference**: The BGWriter moves data to make **RAM** faster; the Checkpointer moves data to make the **WAL** safe.
## The Cleanup Crew: Autovacuum
The most complex housekeeper is **Autovacuum**. Its job is to reclaim space from "Dead Tuples" left behind by updates and deletions. It relies on two physical maps stored alongside your table:
- **Visibility Map (VM)**: A bitset indicating the status of each page. Unlike a simple true/false toggle, the modern VM uses **Two Bits** per page:
- **Bit 0 (All-Visible)**: If set, every suitcase in the container is visible to everyone. The bouncer at the door (The Planner) uses this to perform **Index-Only Scans** without opening the table.
- **Bit 1 (All-Frozen)**: If set, every suitcase in the container has been officially frozen. The Housekeepers (Vacuum) use this to skip the page entirely during anti-wraparound runs, saving massive amounts of I/O.
- **Free Space Map (FSM)**: A record of exactly how much reusable space remains on each 8KB page, helping future inserts find a home.
### The Shared Lock Crowding (Multixacts)
A tuple header is small; it only has room for a single `xmin` and `xmax`. What happens if ten different patrons all want to place a "Shared Lock" (`SELECT FOR SHARE`) on the same dish?
Instead of trying to stuff ten IDs into one 4-byte slot, Postgres moves the coordination to a separate side-file called **`pg_multixact`**. Postgres puts a single "MultiXact ID" in the suitcase header, which acts as a claim check for a list of lockers in the side-file.
> [!WARNING]
> **Maintenance Tax**: While this allows infinite shared locking, it creates a new maintenance burden. The Housekeepers must now clean up the old `pg_multixact` files as well. If your application uses excessive shared row-locking, your `autovacuum` might spend more time cleaning the "Overflow Pockets" than the actual tables.
### The Two-Phase Vacuum
Vacuuming a table is not a single swipe; it is a mechanical two-phase operation:
1. **Phase 1: Heap Scan**: Autovacuum scans the table's "Heap" (the pages of suitcases). It collects the physical addresses (**TIDs**) of all dead tuples and stores them in memory (`maintenance_work_mem`).
2. **Phase 2: Index Cleanup**: Once the "Master List" of dead TIDs is collected, Autovacuum visits every **[[Chapter 2/2.1 - The Balanced Bookshelf (The B-Tree)|Index (Map)]]** associated with the table. It removes the pointers that point to the dead TIDs.
Only after the indexes are clean can the original space in the heap be marked as "Free" in the FSM.
> [!IMPORTANT]
> **Maintenance Budget**: If `maintenance_work_mem` is too small, Autovacuum can't fit enough dead TIDs in memory. It is forced to stop the heap scan, go clean the indexes, and then *come back* to restart the heap scan. This causes a massive performance drag.
```sql
-- Is the vacuum crew keeping up?
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
```
If you see a high `n_dead_tup` count on any table, the crew is falling behind. By default, autovacuum triggers when dead tuples exceed **20%** of live tuples (`autovacuum_vacuum_scale_factor = 0.20`). For high-write tables, you'll want to lower that threshold:
```sql
-- Tell the vacuum crew to be more aggressive on this busy table
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01, -- trigger at 1% dead tuples
autovacuum_vacuum_cost_limit = 1000 -- give them a bigger broom
);
```
## The Freeze Ritual (FREEZE)
Beyond mere cleaning, the vacuum crew has a second, critical duty: **Freezing old rows**.
As we learned in **[[Chapter 4/4.6 - The Infinite Calendar (Transaction ID Wraparound)|Chapter 4.6]]**, every row is stamped with a 32-bit transaction ID (`xmin`). With only ~4 billion available IDs, a long-lived database will eventually cycle back to the beginning—causing Postgres to confuse "ancient history" with "this afternoon."
To prevent this, the vacuum crew performs a **Freeze** on rows that are old enough to be considered permanent. A frozen row has its `xmin` replaced with a special `FrozenTransactionId` marker, which tells every future transaction: *"This row has always been visible. Don't question it."*
```sql
-- How close are our tables to the XID wraparound danger zone?
SELECT relname, age(relfrozenxid) AS xid_age, relpages
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 10;
```
The older the `xid_age`, the more urgently the table needs a vacuum pass to freeze its rows. The danger threshold is ~**2 billion transactions**. You can also force a full freeze on any table at any time:
```sql
-- Manual emergency freeze for a specific table
VACUUM FREEZE orders;
```
> [!WARNING]
> Never disable `autovacuum`. It is not optional. Disabling it is a common mistake on "optimized" systems that leads to XID wraparound panics, ballooning table sizes, and eventually a Postgres emergency shutdown. The housekeepers must always be allowed to work.
## The Checkpoint Spike
If the Checkpointer is too aggressive—or if writes are extremely heavy—the moment of every checkpoint can flood the I/O elevator with dirty pages, causing a visible performance spike. This is why `checkpoint_completion_target` (default 0.9) exists: it tells the Checkpointer to *spread* the flush over 90% of the checkpoint interval, rather than trying to write everything at once.
Without these housekeepers, Postgres would eventually be buried in a mountain of dead suitcases, face an identity crisis from XID exhaustion, and bring the entire Cafe to a grinding, catastrophic halt. And that would be a very poor tea party indeed.
---
| ← Previous | ↑ Table of Contents | Next → |
| :--- | :---: | ---: |
| [[Chapter 5/5.3 - The Private Desk (Work Mem)\|5.3 The Private Desk (Work Mem)]] | [[Learn You a Postgres for Great Good\|Home]] | [[Chapter 5/5.5 - The Great Overflow (Tuple bloat)\|5.5 The Great Overflow (Tuple bloat)]] |