Skip to the content
Software Made Clear
About

Persistence · 7 nodes

How a database keeps your data

How a database actually keeps your data, in the order that makes each step explain the next. Seven steps from a row on a page to a deadlock in production.

Browse the reading order

A running example

One little bookshop. Seven database problems.

Follow the same shop from its first order to a production deadlock. Each new problem exposes another part of the database underneath it.

The shop is fictional. Each linked article stands on its own.

Is this path right for me?

ASSUMES

Only that you have written SQL before. It starts at what a row physically is.

GIVES YOU

Enough of the storage layer to read an execution plan and know which part of it is a guess.

LEADS TO

Distributed basics, where the same questions come back with a network in the middle.

Nothing is tracked and nothing is locked — the map exists so you can see where an article sits.

Suggested start Written Not written yet Optional or subtopic

  1. THE BOOKSHOP · CHAPTER 01

    The first order

    A customer buys a book. Follow the order row into a database page and see what happens when a value does not fit there.

    WHAT CHANGESThe shop can store an order. Now it has to keep its promise to the customer.

    NODE 01 · REQUIRED Suggested start

    Pages, heaps and tuples

    See how PostgreSQL stores row versions inside pages, when HOT can avoid index work, and why reusable space is different from a smaller table file.

    • Row layout on a page
    • Overflow and TOAST

    Try this after reading: Sketch one database page with two rows. Mark the row pointers and one value stored off-page; what extra read would retrieving that value need?

  2. THE BOOKSHOP · CHAPTER 02

    The lights go out

    Checkout says 'confirmed'. Seconds later, the database server loses power. Which parts of that order must already be durable?

    WHAT CHANGESA success response is a promise about recovery, not a promise that every data page has been written.

    NODE 02 · REQUIRED Written

    The write-ahead log

    See why the log commits before the data file does, so a crash mid-write costs you a replay instead of a corrupted table and a very bad on-call night.

    • fsync and durability
    • Checkpoints

    Try this after reading: Put WAL flush, commit acknowledgement and dirty-page write in durability order. Which of these may still be pending when the client hears success?

  3. THE BOOKSHOP · CHAPTER 03

    The orders pour in

    A promotion brings sustained writes while customers still look up earlier orders. The storage engine has to pay for both.

    WHAT CHANGESWhere the engine pays for reads and writes becomes a workload decision.

    NODE 03 · REQUIRED Written

    B-Tree vs LSM-Tree

    Compare B-tree and LSM-tree write, read and compaction costs through two visual models, then choose what to measure for your workload.

    • Page splits
    • Compaction strategies

    Try this after reading: For a workload with point reads and sustained writes, name one reason to test a B-tree and one to test an LSM-tree before choosing either.

  4. THE BOOKSHOP · CHAPTER 04

    The owner wants answers

    The owner asks for daily sales trends while checkouts continue. A report scans many orders; a checkout changes a few.

    WHAT CHANGESThe same data now serves two very different access patterns.

    NODE 04 · REQUIRED Written

    OLTP vs OLAP

    Compare a checkout and a sales report on the same orders. See when row or column layout helps, and why a separate reporting workload may be useful.

    • Row vs column storage
    • When to split reporting work

    Try this after reading: Classify a checkout update and a daily sales aggregation as OLTP or OLAP. Which access pattern makes each classification useful?

  5. THE BOOKSHOP · CHAPTER 05

    The lookup slows

    An order lookup gets slower even though an index exists. The execution plan chooses a scan.

    WHAT CHANGESAn index is an option for the planner, not a command to use it.

    NODE 05 · REQUIRED Written

    Why PostgreSQL is not using your index

    Use EXPLAIN to check why PostgreSQL chose a scan: inspect index conditions, row estimates and costs before changing the query or statistics.

    • Statistics and histograms
    • Sargable predicates

    Try this after reading: Use the article's example of 41 estimated rows versus 8,940 actual rows, or inspect a query plan from your own database. Which estimate or predicate would you check before adding another index?

  6. THE BOOKSHOP · CHAPTER 06

    Only one copy left

    Two customers check out at once while a staff dashboard reads the stock count. What can each transaction see?

    WHAT CHANGESThe answer depends on transaction timing and the database's isolation guarantees.

    NODE 06 · REQUIRED Written

    Isolation levels

    Stop treating isolation levels as a safety dial and start treating them as a list of anomalies you have agreed to tolerate, so surprises stop being surprises.

    • MVCC snapshots
    • Phantom reads

    Try this after reading: Write a two-transaction timeline that produces a surprising result. Name the anomaly and the isolation guarantee you would need to prevent it.

  7. THE BOOKSHOP · CHAPTER 07

    Two workers wait

    One worker locks stock for book A, then book B. Another locks B, then A. Neither can continue.

    WHAT CHANGESThe database breaks the cycle by aborting one transaction; the application must retry the whole unit of work safely.

    NODE 07 · REQUIRED Written

    Deadlocks, seen

    Watch two transactions grab the same two locks in opposite order, so the next deadlock in your logs reads as a diagram instead of an unreadable stack trace.

    • Lock wait graphs
    • Retry policy

    Try this after reading: Draw a wait-for cycle between two transactions. If the database aborts one, which whole unit of work must the application retry?