Databases & Storage

A database is a set of promises about your data: what you write stays written, concurrent users do not corrupt each other’s changes, and queries come back fast enough to use. Databases and storage is the study of how those promises are kept, what each one costs, and which ones you can relax when the data outgrows a single machine.

Core ideas and trade-offs

• Transactions and isolation define “correct.” An ACID transaction applies a group of writes all together or not at all. Isolation levels decide which anomalies concurrent transactions can observe, from dirty reads to lost updates and write skew; stronger levels prevent more of them and cost more throughput.
• Indexes decide speed. A B-tree index turns a full table scan into a few page reads, but every index slows writes and takes space, and the query planner uses one only when it estimates that doing so is cheaper.
• Storage engines suit different workloads. B-trees update data in place and favor reads; LSM-trees append writes and compact them in the background, which favors heavy write loads at some cost to reads.
• Rows or columns. Row-oriented OLTP databases fetch and update individual records cheaply; column-oriented OLAP stores scan and aggregate one column across millions of rows cheaply. Running heavy analytics on the production database tends to slow both jobs.
• Scaling out changes the guarantees. Replication adds read capacity and survives failures but introduces lag; partitioning spreads data across machines but makes cross-partition queries and transactions harder.

How the formats fit together

The handbooks build the mental model: SQL and what happens beneath a query, PostgreSQL internals, concurrency and isolation, partitioning and replication, and focused comparisons such as SQL versus NoSQL and OLTP versus OLAP. The labs make two invisible things visible, the query planner’s choice and the B-tree versus LSM-tree trade, and an algorithm game builds an LSM-tree step by step. The system designs show where storage decisions are the hardest part of a product: payments, wallets, ticketing, file sync, object storage, time-series data and search. The tools estimate index size and storage growth before you commit.

Common mistakes

• Assuming the default isolation level prevents every race. PostgreSQL defaults to Read Committed, where a read-then-write in application code can lose an update unless you use an atomic update, a row lock or a stricter level.
• Indexing every column, then wondering why writes slowed down.
• Choosing a NoSQL store for scale the product does not have yet and giving up joins and transactions it does need.
• Storing money as a mutable balance instead of an append-only ledger with idempotent writes.