Database Internals

A 8-part series.

  1. Part 1

    Database Normalization: 1NF through 5NF Explained

    A practical guide to database normalization — 1NF through 5NF, when to denormalize, and the pitfalls teams hit. With concrete data examples.
  2. Part 2

    ACID, Read Phenomena, and Isolation Levels: What to Use When

    A practical guide to database transactions — ACID, five concurrency anomalies, isolation levels, MVCC vs locks, and choosing the right level.
  3. Part 3

    How Databases Actually Store and Find Your Data

    A bottom-up look at how databases physically store data — pages, heaps, B-trees, clustered indexes, and row IDs.
  4. Part 4

    B-Trees and B+ Trees: From Binary Trees to Database Indexes

    If you know binary search trees, you are halfway to understanding how databases find your data. B-trees are BSTs that got wide on purpose — here is why.
  5. Part 5

    PostgreSQL Indexing: Internals, Types, and Trade-offs

    A practical guide to PostgreSQL indexes: B-tree, GIN, GiST, BRIN, hash — when to use each, composite key ordering, and CONCURRENTLY pitfalls.
  6. Part 6

    PostgreSQL EXPLAIN: Reading and Understanding Query Plans

    How to read PostgreSQL EXPLAIN output: scan types, join algorithms, and the cost model — turn opaque query plans into actionable insights.
  7. Part 7

    Postgres Partitioning: Strategies, Real Advantages, and Pitfalls

    A practical guide to PostgreSQL partitioning — RANGE, LIST, HASH, pruning, local indexes, real advantages, and the pitfalls that bite in production.
  8. Part 8

    Postgres Concurrency Internals: Connections, Locks, and MVCC

    A practical guide to how Postgres handles many concurrent clients — process-per-connection, pooling, lock manager, MVCC tuples, deadlocks, and pg_locks.