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.

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. NODE 01 · REQUIRED Suggested start

    Pages, heaps and tuples

    See what a database row physically is on disk, what happens when an update no longer fits its page, and why that single fact explains half of table bloat.

    • Row layout on a page
    • Overflow and TOAST

  2. 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

  3. NODE 03 · REQUIRED Written

    B-Tree vs LSM-Tree

    Take the B-tree when reads must stay predictable and the LSM-tree when writes outrun where you can place them — one bill now, one bill later, your choice.

    • Page splits
    • Compaction strategies

  4. NODE 04 · REQUIRED Written

    OLTP vs OLAP

    Tell the two workloads apart by what they are judged on, so you stop trying to make one schema serve a checkout and a year-end report at once.

    • Row vs column storage
    • Why one schema cannot serve both

  5. NODE 05 · REQUIRED Written

    Why your index is not being used

    Find the exact row count where Postgres stops trusting your index, fix the statistics instead of forcing a plan, and stop guessing why the query slowed down.

    • Statistics and histograms
    • Sargable predicates

  6. 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

  7. 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