# 1.5 The Sharpie Ledger (MVCC)
In the **Elephant Cafe**, there is a strict rule: **Never use an eraser.**
If you ask the elephant to update a record—say, changing the cost of 'Saffron'—a high-effort database would attempt an **"In-Place"** update. It would find the exact suitcase on the floor, carefully rub out the old price, and scribble the new one over the smudge.
To a human, this sounds like the "lazy" way. But to an elephant, it is a nightmare of coordination.
### The Nightmare of the Eraser
Why does the elephant despise the eraser? Because it violates the peace of the Cafe:
1. **The Locking Stop**: If you are in the middle of erasing a suitcase, you cannot allow any **Patron** to look at it. If they saw the data while it was half-erased, they would see literal garbage! This requires "locking" the suitcase, which leads to long, grumpy lines of animals waiting for their tea.
2. **The 8KB Glove Problem**: Remember the **[[Chapter 1/1.3 - The Shipping Container (The Page)|8KB Gloves]]**? The elephant doesn't have a pen; he has a massive shipping crane. Even to change one single digit, he has to pick up the entire 8KB container, bring it to his desk, perform the surgery, and drop it back on the floor. Erasing doesn't save him any effort!
3. **The "Ink-and-Power" Risk**: If the power goes out while the elephant is performing surgery on an 8KB container, the suitcase might be left half-finished—a "Torn Page" of digital nonsense.
## The Art of the Sharpie (MVCC)
Instead of the stress of erasing, Postgres uses **Multi-Version Concurrency Control (MVCC)**. It is the ultimate manifestation of the elephant's laziness.
When you tell the elephant to UPDATE a row, he performs a simple, three-step "Sharpie Dance":
1. **Ignore the old one**: He leaves the old physical suitcase sitting exactly where it is.
2. **Write a new one**: He grabs a fresh suitcase, packs the new data comfortably, and drops it in the next available spot in the **[[Chapter 1/1.3 - The Shipping Container (The Page)|Shipping Container]]**.
3. **The Scribble**: He asks the **[[Chapter 1/1.2 - The Physical Suitcase (The Tuple)|Bureaucratic Bird]]** in the old suitcase to mark it with a red "EXPIRED" stamp (the `xmax`).
The result is magical: **Readers never block writers, and writers never block readers.**
Other animals who were already reading the old suitcase can finish their business without being interrupted. They see the version they started with, while new visitors see the fresh one. The Cafe remains peaceful, and the elephant never has to reach for an eraser.
### Watching the Sharpie in Action
Let’s watch the elephant handle an order update in our cafe. Imagine **Babu the Elephant** has a pending order:
```sql
-- Check the current state of an order
SELECT ctid, xmin, xmax, status FROM orders WHERE id = 1;
-- Results:
-- ctid | xmin | xmax | status
-- -------+------+------+---------
-- (0,5) | 600 | 0 | Pending
```
Now, the chef finishes the dish, and we update the status:
```sql
UPDATE orders SET status = 'Served' WHERE id = 1;
-- Now look again...
SELECT ctid, xmin, xmax, status FROM orders WHERE id = 1;
-- Results:
-- ctid | xmin | xmax | status
-- -------+------+------+--------
-- (0,6) | 601 | 0 | Served
```
**Wait, what happened to (0,5)?** If we could look at the "hidden" deleted rows, we'd see that `(0,5)` now has an `xmax` of `601` (the transaction that replaced it), and its `ctid` actually points to the new location: `(0,6)`.
The old suitcase is still there, scribbled on with the invisible Sharpie. How very civil!
> [!NOTE]
> **The Growing Diary**: You can think of Transaction IDs (XIDs) as every-increasing numbers in the Elephant's sequential diary. They always move forward, which allows the elephant to look at a suitcase and instantly know if it was packed "before" or "after" the current moment in time.
### The Physical Reality (Before & After)
Let's look at what the elephant actually sees in the depot. Imagine we have an order for **Babu the Elephant** in our `orders` table.
#### State 1: The Initial Insert
The moment an order is added (Transaction 100), the table looks like this:
| ctid | xmin | xmax | id | animal_id | status | Visibility |
| :--- | :--- | :--- | :--- | :--- | :--- | :--- |
| **(0,1)** | **100** | **0** | 1 | 5 | Pending | **Visible to All (XID 100+)** |
*Notice that **xmax is 0**. This means the order is alive and well!*
#### State 2: After an UPDATE
Now, the chef serves the dish and we update the status (Transaction 101). The elephant **leaves the old row** and **writes a new one**:
```sql
UPDATE orders SET status = 'Served' WHERE id = 1;
```
Now, the physical depot floor has **two** rows for the same order:
The elephant uses the **`xmax`** of the first row to determine it is no longer visible to new visitors. To the outside world, there is only one "Order #1." But in the **database**, the history of the world is written in "Sharpie-marked" luggage!
#### State 3: The Ghost of a ROLLBACK
Now, imagine a messy **Aardvark** tries to place an order (Transaction 102), but suddenly realizes he forgot his wallet and cancels the order (**ROLLBACK**).
The elephant *still wrote the suitcase to the floor* before the Aardvark changed his mind!
| ctid | xmin | xmax | id | animal_id | status | Visibility |
| :--- | :--- | :--- | :--- | :--- | :--- | :--- |
| **(0,3)** | **102** | 0 | 2 | 8 | Salad | **Invisible to All (Aborted)** |
Even though this order was "never real" to the other patrons, the physical suitcase **(0,3)** is now taking up space on the depot floor. This is a **Ghost Tuple**. It is effectively invisible ink—no one can see it, but it still takes up 100 bytes of room in the container.
This is why the **[[Chapter 5/5.3 - The Housekeepers (Vacuum & Freezing)|Roomba (Vacuum)]]** must eventually come by to throw these ghosts into the incinerator!
## The Cost of Garbage
This "Append-Only" lifestyle makes Postgres incredibly fast and consistent, but it leaves the depot floor covered in old, crossed-out suitcases. In technical terms, we call this **Bloat**.
Doing the least work possible today (just crossing items out) ensures the elephant doesn't have to wait around for erasers, even if it means he needs a robotic Roomba to help with the cleanup tomorrow.
---
| ← Previous | ↑ Table of Contents | Next → |
| :--- | :---: | ---: |
| [[Chapter 1/1.4 - The Depot (The Table)\|1.4 The Depot (The Table)]] | [[Learn You a Postgres for Great Good\|Home]] | [[Chapter 1/1.6 - The Separate Trailer (TOAST)\|1.6 The Separate Trailer (TOAST)]] |