rgoussu.dev
rgoussu@goussu: ~/library/data-storage
❯ ~/library/data-storage cat databases-and-storage-systems.md

Databases and other storage systems

# How databases and storage systems work and how to choose them — storage engines, indexing, transactions, replication, and the relational/NoSQL landscape.

Conceptsaved 2026-08-08 #databases#storage#sql#nosql#system-design

Overview

Storage systems are where system design gets real: every guarantee an application makes ultimately rests on how some engine writes bytes to disk and replicates them. This topic spans the internals (storage engines, indexes, transactions) and the landscape (relational, key-value, document, columnar, graph, object storage) — the goal is to choose and operate storage on understanding, not marketing. Kleppmann's DDIA is the backbone reference.

Key points

  • Storage engines: B-tree (read-optimized, in-place; Postgres, InnoDB) vs. LSM-tree (write-optimized, compaction; RocksDB, Cassandra) — the fundamental fork that explains most performance profiles. WAL and fsync are where durability actually happens.
  • Indexing: primary/secondary, composite (column order matters), covering, partial; and the specialized kinds (full-text, geospatial, vector). Every index taxes writes.
  • Transactions & isolation: ACID precisely; read committed → snapshot/repeatable read → serializable; the anomalies each level permits (dirty/non-repeatable/phantom reads, write skew); MVCC as the dominant implementation.
  • Replication & partitioning: leader/follower and replication lag, failover pitfalls; sharding and rebalancing; consensus-backed stores (Spanner, Cockroach) vs. eventually consistent ones (Dynamo lineage).
  • The landscape: relational (default until proven otherwise), key-value (Redis), document (Mongo), wide-column (Cassandra), search (Elasticsearch), time-series, graph, and object storage (S3) + open table formats (Parquet, Iceberg) for analytics.
  • OLTP vs. OLAP: row vs. column orientation; don't run analytics on the transactional store — that's what warehouses and lakehouses are for.
  • To explore: query planning & EXPLAIN, connection pooling, CDC as the bridge from database to event stream, backup/restore as the real durability test.

Practice

  • PostgreSQL Exercises (source) — work the query sets, then rerun your answers under EXPLAIN ANALYZE and read the plans; teaches indexing and the planner on a schema small enough to reason about.
  • Isolation-anomaly lab (source) — open two psql sessions and reproduce each anomaly (non-repeatable read, phantom, write skew) at each isolation level; teaches what the levels actually permit, from memory of having caused them.
  • Build your own Redis (source) — John Crickett's challenge: a RESP-speaking in-memory key-value server with expiry and persistence; teaches the key-value model and why the single-threaded event loop is fast.
  • Build your own database (exercise) — a SQLite-style engine from REPL to B-tree to WAL; teaches pages, indexes, and durability by making you write them.
  • CMU 15-445 BusTub projects (source) — implement the buffer pool, B+-tree index, query executors, and concurrency control inside a real teaching DBMS, with autograded tests; the substantial internals workout once the from-scratch build has warmed you up.

Related