# 2.6 The Cost of Fame (Maintenance)
Getting a physical suitcase into the Depot is already a bureaucratic slog. You’ve had to survive the magnifying glasses of the **[[Chapter 2/2.5 - The Meticulous Beavers and the Dominoes|Meticulous Beavers]]** and wait for the **Dominoes** of your triggers to finish falling.
If your table is "famous"—if it has half a dozen indexes and constraints attached to it—the elephant’s work has only just begun. This is **The Cost of Fame**.
In the database cafe, an index is a persistent piece of paparazzi. If you have an index on `name`, an index on `price`, and an index on `scent_notes`, you have three different photographers standing by the door, each waiting for a snapshot of your suitcase so they can update their own specialized record books.
## The Synchronous Squeeze
When the Lazy Elephant decides to `INSERT` a new suitcase or `UPDATE` an old one, he becomes the center of attention. He cannot finish his job and return to his hammock until every single one of these photographers has finished their sketch.
This process is strictly **Synchronous**. The elephant stands there, trunk-deep in flashbulbs, waiting while the B-Tree Librarian, the GIN Archivist, and the BRIN Summarizer all scribble furiously in their notebooks. If you have ten indexes, you have ten different animals holding up the line.
This is the **Synchronous Squeeze**. The more "helpful hints" you give the database to help it read, the more weight you put on its shoulders when it tries to write. The elephant is legally obligated to wait. "Consistency!" the Librarians shout, "The maps must match the world!"
## The Map is the Meal: Index-Only Scans
Sometimes, the Clerk can be even lazier. If you only ask for data that is already written on the index map (e.g., `SELECT name FROM animals`), the Clerk doesn't even have to walk to the depot!
- **Index Scan**: The Clerk looks at the map, finds the coordinates, and then walks to the depot to fetch the suitcase.
- **Index-Only Scan**: The Clerk looks at the map and sees the answer right there. He just shouts it across the room!
> [!TIP]
> **The Visibility Catch**: For an Index-Only Scan to work, the elephant must be 100% sure the data hasn't been crossed out with a red sharpie. He checks the **[[Chapter 1/1.4 - The Depot (The Table)|Visibility Map (_vm)]]**—if the whole shipping container is marked as "Fully Visible," he skips the depot walk entirely.
## The Speedy Pass: HOT Optimization
The elephant, being fundamentally lazy, has found one brilliant way to cheat: **HOT (Heap Only Tuples)**.
Imagine the elephant just wants to change the color of a sock inside a suitcase. If the index only cares about the **Suitcase ID**, and the elephant can fit the new version of the suitcase on the same **[[Chapter 1/1.3 - The Shipping Container (The Page)|Page]]** (the same shipping container), he doesn't tell the map-makers anything. He just keeps his mouth shut!
Instead, he leaves a tiny "Forwarding Address" on the old suitcase. When the Librarian eventually follows his map to the old spot, he sees the note, hops over to the new suitcase, and finds the data. This "HOT update" is lightning fast because none of the map-makers have to touch their notebooks.
> [!CAUTION]
> **The Label Trap**: HOT updates **only** work when you change the *contents* of the suitcase, not the *label* on the outside. If you change a column that is itself indexed (like the `name` column), the elephant is FORCED to tell every map-maker because the alphabetical map is now physically wrong. The shortcut at the old address is useless if the name "Babu" has changed to "Zazu!"
### Checking the Shortcut
You can actually see if the elephant is successfully using the "Speedy Pass" in your Cafe by querying the statistics:
```sql
-- Are we keeping our Map-Makers happy?
SELECT relname, n_tup_upd, n_tup_hot_upd
FROM pg_stat_user_tables
WHERE relname = 'orders';
```
If **`n_tup_hot_upd`** is nearly as high as **`n_tup_upd`**, it means most of your updates are bypassing the map-makers entirely. You’ve successfully achieved peak laziness!
Indexes are miracles for reading, but they are heavy weights for writing. Choose your fame wisely, or you might find yourself with a very large hat and a very small amount of time.
## Summary: The Lazy Ledger
To help you decide which staff members to hire for your Depot, the elephant has prepared a final ledger of the trade-offs:
| Index Type | Read Speed | Write Cost | Storage Size | Best For... |
| :--- | :--- | :--- | :--- | :--- |
| **B-Tree** | ⚡⚡⚡⚡ | 💰 | Medium | Everything (Equality, Ranges, Sorting) |
| **GIN** | ⚡⚡⚡⚡⚡ | 💰💰💰💰 | Large | Arrays, JSONB, Full Text |
| **GiST** | ⚡⚡⚡ | 💰💰💰 | Medium | Geometry, Ranges, Trigrams |
| **BRIN** | ⚡⚡ | 💰 | Tiny | Massive Time-Series (Order-dependent) |
| **HNSW** | ⚡⚡⚡⚡ | 💰💰💰💰💰 | Massive | AI/Vector Similarity ("Smells Like") |
## Chapter 2 Appendix: The Grand Index Decision Tree
If you aren't sure which shortcut to build, follow the Elephant's simple path:
1. **Is it a standard value (ID, Name, Date)?** -> Use **B-Tree**. (Always start here!)
2. **Is it a collection (Array, JSONB)?** -> Use **GIN**.
3. **Is it a shape or a range (GPS, Circles)?** -> Use **GiST**.
4. **Is it a "Dense" list of numbers (Embeddings)?** -> Use **HNSW**.
5. **Is it 100GB+ and sorted by time?** -> Use **BRIN**.
---
Now that the elephant has his maps and his quality control beavers, he's ready for the lunch rush. But who decides *how* to use all these tools? In the next chapter, we'll meet **The Head Chef (The Query Planner)** and watch as he orchestrates the entire kitchen to fulfill your SQL requests!
---
| ← Previous | ↑ Table of Contents | Next → |
| :--- | :---: | ---: |
| [[Chapter 2/2.5 - The Meticulous Beavers and the Dominoes\|2.5 The Meticulous Beavers and the Dominoes (Constraints & Triggers)]] | [[Learn You a Postgres for Great Good\|Home]] | [[Chapter 3/3.0 - The Great Lunch Rush (Planning & Operations)\|Chapter 3 - The Great Lunch Rush (Planning & Operations)]] |