# 5.3 The Housekeepers (Background Workers)
In **[[Chapter 1/1.5 - The Sharpie Ledger (MVCC)|Chapter 1.4]]**, we learned that the elephant never erases anything. Every time you update a row, the old version is marked "dead" but remains on the page, taking up space. And in **[[Chapter 4/4.6 - The Infinite Calendar (Transaction ID Wraparound)|Chapter 4.6]]**, we saw that transaction IDs are a finite resource that must periodically be "frozen" to prevent an apocalyptic identity crisis.
Both of these problems are solved by the same dedicated crew: the **Housekeepers**.
## The Three Workers
The Cafe employs three dedicated background workers to keep the depot from collapsing under its own weight.
### 1. The Stealth Cleaner (Background Writer)
The **Background Writer (BGWriter)** is a quiet elephant who tip-toes around the **[[Chapter 5/5.1 - The Warming Rack (Shared Buffers)|Warming Rack]]** at night. His entire job is to look for "dirty" pages (pages that have been modified in memory but not yet written to disk) and gently flush them to the **Frozen Pantry** *before* anyone actually needs the space.
By doing this proactively, he ensures there are always clean, empty slots on the rack ready for the next staff member. He is the ghost of the tea party—invisible, thankless, essential.
### 2. The Grand Foreman (Checkpointer)
While the Background Writer is stealthy, the **Checkpointer** is the foreman. At regular intervals (controlled by `checkpoint_timeout`, default 5 minutes), he rings a massive bell.
_"Everybody stop! We're doing a total sync!"_
He ensures that every change mentioned in the **[[Chapter 4/4.1 - The Pocket Diary (WAL & fsync)|Pocket Diary (WAL)]]** has been safely flushed to the Frozen Pantry. Once complete, he draws a line in the diary: *"Everything before this mark is safe. You can recycle these diary pages now."*
### Why Two Housekeepers?
It might seem redundant to have both the BGWriter and the Checkpointer, but they serve two very different goals:
- **The BGWriter (The Waiter)**: He is like a waiter who clears empty glasses from your table throughout the night. He does this so you always have **"Room to Breathe"** on your table. He doesn't care about the diary; he just wants the rack to be clean for the next guest.
- **The Checkpointer (The Janitor)**: He is the janitor who mops the entire floor once an hour. He does this for **"Safety for the Diary."** He ensures that *nothing* is left on the tables so that the Logbook can be recycled.
One is for the comfort of the guests; the other is for the legal requirements of the Cafe!
```sql
-- How many checkpoints were forced vs scheduled?
-- High checkpoints_req means WAL is filling faster than checkpoints can clear it.
SELECT checkpoints_timed, checkpoints_req, buffers_checkpoint, buffers_clean
FROM pg_stat_bgwriter;
-- Literal Output:
-- checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean
-- -------------------+-----------------+--------------------+---------------
-- 240 | 1 | 1598 | 0
```
If **`checkpoints_req`** is high, the elephant is filling his Pocket Diary too fast. Consider increasing `max_wal_size`.
The most important housekeeper is **Autovacuum**. His job is to walk through every table, identify dead tuples (the "shredded paper" left behind by MVCC updates and deletes), and mark those pages as reusable. He uses two special maps to save time:
- **Visibility Map (VM)**: A quick checklist that tells him which pages are already perfectly clean. If a page is marked "all-visible," the vacuum crew just skips it!
- **Free Space Map (FSM)**: A record of where the empty spots are on each page. This helps the next Food Runner find a place to put their new record.
Without Autovacuum, the depot slowly fills with unreachable ghost suitcases and performance collapses.
```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 the elephant 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, the elephant 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.2 - The Private Desk (Work Mem)\|5.2 The Private Desk (Work Mem)]] | [[Learn You a Postgres for Great Good\|Home]] | [[Chapter 5/5.4 - The Great Overflow (Tuple bloat)\|5.4 The Great Overflow (Tuple bloat)]] |