Raj's Notes

Data systems

Storage engines, indexes, transactions, relational databases, document stores, key-value systems, columnar files and table formats, search engines, and change data capture.

Scope#

How a database turns bytes on storage into an acknowledged write and a query result. The sequence starts with access patterns, pages, logs, trees, indexes, execution, and concurrency. Product notes then trace PostgreSQL, MySQL/InnoDB, MongoDB, Redis, DynamoDB, Cassandra, ClickHouse, Arrow, Parquet, Iceberg, and OpenSearch through their read, write, replication, maintenance, and recovery paths. The final notes connect those systems through change data capture and compare them against one workload without treating a product category as the answer.

This collection separates three levels that are often mixed together:

The levels interact, but none substitutes for another. JSON syntax does not explain MongoDB replication. SQL does not tell you whether rows live in a heap or clustered primary-key tree. A partition key does not state the consistency or durability of an acknowledged write.

Reading order#

The foundation notes establish terms used by every product-specific note:

  1. Translate product operations into bounded access patterns and invariants.
  2. Follow a write through a log, memory, pages or immutable files, and background maintenance.
  3. Follow a query through indexes, statistics, planning, execution, memory, and I/O.
  4. Separate atomicity, isolation, durability, locks, versions, deadlocks, and distributed commit.

After those foundations, PostgreSQL and MySQL show two different relational storage paths. MongoDB shows how document boundaries affect indexes, replication, and sharding. Redis and DynamoDB start with key-based APIs but make different durability, execution, and distribution choices. Cassandra develops the wide-column and LSM path, while ClickHouse develops the columnar analytic path. Arrow, Parquet, and lakehouse table formats then separate in-memory layout, durable files, object storage, and committed table state. OpenSearch develops text and vector retrieval from mappings through Lucene segments, distributed shards, and rebuilds. Change data capture joins authoritative stores to derived search, cache, and analytic state, and the last note compares the full set against one workload.

Each product note uses the same evidence order: vocabulary, one write, one read, access paths, concurrency, replication or sharding, maintenance, recovery, limits, and operator signals. That order makes differences visible without forcing unlike databases into one feature table.

Distributed-systems depth by stage#

Data notes Distributed background Why to use it
DB1–DB4 None required The foundation defines the local storage and transaction terms
DB5–DB7 DS7; DB6 also uses DS5 Separate replica history, writer authority, and promotion
DB8–DB11 DS3 and DS5–DS7 Follow membership, election, partitioning, consistency, and repair
DB12–DB14 DS6–DS8 when sharding or dataflow is unfamiliar Follow distributed ownership, reduction, and checkpoints
DB15–DB16 DS1, DS2, and DS7 Reason about ambiguous outcomes, ordering, replication, and commit

Difficulty labels describe the depth inside a note, not another reading order. Read DB1–DB4 before the product notes even when application code already uses one of those products.

Useful background#

Low-level storage and I/O explains files, block devices, page cache, direct I/O, fsync, memory mapping, and io_uring. Distributed systems derives time, replication, consensus, partitions, and repair. Neither is required before starting the first note; follow the links when the database explanation reaches an operating-system or distributed boundary that needs a slower treatment.

Notes

  1. Data Models Begin with Access Patterns and Invariants Turn product operations into keys, bounded reads, mutation scopes, consistency rules, and lifecycle requirements before selecting a database.
  2. Storage Engines Turn Writes into Recoverable State Trace records through pages, logs, trees, immutable runs, compaction, and recovery while accounting for amplification, corruption, and write stalls.
  3. Indexes, Query Planners, and Execution Follow a query from parsed expression to access path, join, sort, memory use, and measured execution without treating an index name as a performance explanation.
  4. Transactions, Concurrency, and Recovery Separate atomic commit, isolation, durability, locks, versions, retries, deadlocks, and distributed transaction boundaries by following concurrent writes through a database.
  5. PostgreSQL Storage, Query, and MVCC Internals Follow a PostgreSQL statement through a backend process, parser, planner, executor, heap pages, indexes, MVCC visibility, WAL, vacuum, and crash recovery.
  6. PostgreSQL Replication, Backups, and Schema Change Trace PostgreSQL write-ahead logs across physical and logical replicas, recover a cluster from archived logs, promote without split brain, and change large schemas through explicit lock and compatibility states.
  7. MySQL and InnoDB from Clustered Rows to Replication Trace MySQL 8.4 statements through the server layer and InnoDB clustered indexes, buffer pool, redo, undo, locks, purge, binary log, replication, and online schema change.
  8. MongoDB Documents, Replication, and Sharding Follow a Binary JSON document through schema boundaries, WiredTiger, indexes, replica-set acknowledgment, transactions, change streams, shard routing, migration, and recovery.
  9. Redis Data Structures, Persistence, and Cluster Trace Redis commands through the event loop, in-memory encodings, expiration, persistence, replication, Sentinel, Cluster, and operator failure boundaries.
  10. DynamoDB Partitions, Indexes, and Global Tables Turn DynamoDB access patterns into partition and sort keys, capacity units, conditional writes, indexes, transactions, streams, backups, and explicit multi-Region consistency.
  11. Cassandra Partitions, Compaction, and Repair Follow one Cassandra partition through token ownership, replicas, the local log-structured merge engine, quorum reads and writes, compaction, repair, and topology changes.
  12. ClickHouse Columnar Parts, Sparse Indexes, and Merges Trace analytical rows through columnar encoding, MergeTree parts, sparse indexes, vectorized queries, background merges, materialized views, replication, and distributed execution.
  13. Columnar Files and Lakehouse Table Formats Trace analytical data through Arrow buffers, Parquet files, object storage, and Iceberg metadata, then compare Delta Lake and Hudi commit, update, maintenance, and recovery paths.
  14. Search Engines, Inverted Indexes, and OpenSearch Build a search projection from documents and analyzers through Lucene segments, OpenSearch shards, ranked queries, pagination, recovery, and safe rebuilds.
  15. Change Data Capture, Snapshots, and Rebuildable Projections Build a change stream from database commit logs without losing the snapshot boundary, transaction order, schema history, deletes, retries, or the ability to rebuild a sink.
  16. Selecting and Combining Data Systems Carry one multi-tenant commerce and support workload from requirements and capacity math through authority, projections, failures, recovery, cost, and migration triggers.