Overview

BoxOffice started like most products: one service and one PostgreSQL database holding everything — events, venues, seats, orders, users, even the poster images as bytea columns. It worked for a year. Then the stadium show went on sale at 10:00. Two million fans opened the event page, the search box was hammered with "taylor", the marketing team ran a "sales per minute" report on the same database, and 60,000 seats had to be sold exactly once each. At 10:02 the database CPU was at 100%, the report query held locks for 40 seconds, and two fans received tickets for seat A-12.

Nothing was "wrong" with PostgreSQL. The problem was that different data has different shapes, and one store was asked to be good at all of them at once. This class is about recognizing those shapes and choosing — and scaling — the right store for each.

Roadmap

1. This overview — the data map of BoxOffice, the questions that choose a store, polyglot persistence, and the database scaling ladder.
2. Data Models & Storage Engines — relational, key-value, document, wide-column, graph; the SQL-vs-NoSQL decision guide; B-trees vs LSM-trees; indexes and EXPLAIN.
3. Transactions & Isolation — ACID, isolation levels and their anomalies, the double-booking bug and four ways to fix it, seat holds.
4. Replication — leader-follower, synchronous vs asynchronous, replication lag, failover, multi-leader and leaderless quorums.
5. Partitioning & NewSQL — range, hash and consistent hashing, hot partitions, secondary indexes, resharding, Spanner and CockroachDB.
6. Specialized Stores — search engines, object storage, data warehouses and lakehouses, vector databases.
7. Quiz — check your understanding.

Slides

Click the slides and use ← → to move through them; F goes full screen and P opens the presenter view, with the notes. Open the slides in a new tab

Data has a shape

Before picking a database, describe the data. Four properties decide almost everything:

Applying them to BoxOffice gives a data map. The volumes below are illustrative assumptions for a mid-size ticketing company — the point is the order of magnitude and the contrast between rows, not the exact values.

Notice that the rows differ by orders of magnitude in every column. A store tuned for the first row (small, hot, transactional) is a poor fit for the sixth (huge, append-only, scanned in bulk), and vice versa.

flowchart LR
    gw["gateway"] --> inv["inventory"]
    gw --> ord["order"]
    gw --> cat["catalog"]
    gw --> srch["search"]
    gw --> ast["assistant"]
    inv --> sqlI[("SQL<br/>seats, holds")]
    ord --> sqlO[("SQL<br/>orders, payments")]
    cat --> cache[("Cache")]
    cache -->|miss| sqlC[("SQL<br/>events, venues")]
    sqlC -->|"CDC"| idx[("Search index")]
    srch --> idx
    cat -->|"poster URLs"| obj[("Object store")]
    sqlO -->|"CDC / ELT"| dwh[("Data warehouse")]
    ast --> vec[("Vector DB")]

This is polyglot persistence: each service keeps its data in the store that fits its access pattern, and data flows between stores through change streams rather than shared tables.

Five questions that choose a store

When a requirement arrives — in a design review or in an interview — walk through these questions in order. Most of the time the answer is "a relational database", and you only leave it with a reason.

1. What is the system of record, and what are its invariants? Seats must never be sold twice; an order total must equal the sum of its items. Invariants that span several rows want transactions — a relational database is the default.
2. What are the top 3 queries, and are they known in advance? Known key lookups at huge scale fit a key-value or wide-column store; unknown, ad hoc queries fit SQL.
3. How big, how fast, growing how? Estimate peak QPS and bytes (see Estimation). If one node with replicas can hold it for the next two to three years, you do not need to partition yet.
4. How stale can a read be? Seconds of staleness unlock replicas, caches and asynchronous indexes — the cheapest scaling tools there are.
5. What can the team operate? A managed PostgreSQL that everybody understands beats an exotic store that one person can debug at 3 a.m.

The database scaling ladder

The database is usually the first component to saturate, because it is the one component that is hard to make stateless. The classic mistake is to jump straight to sharding. Climb the ladder one step at a time, and measure before each step²:

Steps 1–5 are cheap and reversible; steps 6–8 change the architecture. In 2026 a single well-tuned relational node with a few replicas comfortably serves a large majority of products; sharding is for the few that outgrow it, and managed distributed SQL (see NewSQL) has made that last step less painful than it used to be.

Try it. The scenario below is the one-node starting point: client → app → SQL. Raise the load until the database saturates and watch the p99 latency bend upward (the "knee" from Queueing). Then think about which rung of the ladder you would climb first — and check it in the db-replicas scenario on the Replication page.

The full simulator, with every storage component in the palette, is in the Playground.

One database per service, or one database for everything?

Two opposite pieces of advice coexist, and both are right in their context.

Database per service. In a microservice architecture each service owns its data and nobody else reads its tables (Compute & Services). The catalog can change its schema without breaking inventory; a slow analytics query cannot lock the order tables. The price: no joins and no transactions across services — you need events, sagas and the transactional outbox (Messaging patterns).

"Just use Postgres". Modern PostgreSQL covers a surprising range³: JSONB documents with GIN indexes, full-text search (tsvector), geospatial (PostGIS), time series (partitioned tables, TimescaleDB), queues (SELECT … FOR UPDATE SKIP LOCKED), and vector search (pgvector). Fewer systems means fewer things to operate, back up, secure and learn.

The pragmatic path for BoxOffice: start with PostgreSQL per service (owned schemas, separate databases or at least separate users), and add a specialized store when a measured need appears — the search relevance team needs facets and typo tolerance, analysts need to scan two years of clicks, the assistant needs tens of millions of vectors.

The databases concept map

Each storage component is built for one family of access patterns; the system of record feeds the derived stores through change streams. The map links each palette component (green) to the concepts it relies on (blue):

flowchart LR
    cache["cache"]:::comp -->|"miss"| sql["sql"]:::comp
    sql -.- c1["transactions, isolation, indexes,<br/>replication, failover"]:::tech
    nosql["nosql"]:::comp -.- c2["key-value, document, wide-column,<br/>LSM-trees, partition keys"]:::tech
    nosql -.- c3["consistent hashing, hot partitions,<br/>quorums, NewSQL"]:::tech
    sql -->|"CDC"| search["search"]:::comp
    search -.- c4["inverted index,<br/>ranking"]:::tech
    sql -->|"CDC, ELT"| dwh["dwh"]:::comp
    dwh -.- c5["OLAP, columns,<br/>star schema"]:::tech
    objstore["objstore"]:::comp -.- c6["objects by key,<br/>presigned URLs, CDN"]:::tech
    vectordb["vectordb"]:::comp -.- c7["embeddings,<br/>ANN, HNSW"]:::tech
    sql -->|"keys only"| objstore
    classDef comp fill:#1e9e5a22,stroke:#1e9e5a
    classDef tech fill:#2f6fed22,stroke:#2f6fed

Connections

• Data Models & Storage Engines continues this page with the SQL-vs-NoSQL decision guide.
• Distributed Systems — replication and partitioning rely on its consistency models, quorums and consensus.
• Caching — step 4 of the ladder, and the next class.
• Messaging & Event-Driven — CDC and the transactional outbox keep derived stores in sync.
• AI Systems — the vector database inside a RAG pipeline.

Related: Hands-on 6 — Data at scale (seat holds, read replica, PgBouncer, EXPLAIN) · Hands-on 1 — First service (catalog on PostgreSQL + Flyway) · Exercise: cache + database · Case study: BoxOffice ticketing · Case study: URL shortener · Interview prep · Component catalog: Storage.

Key takeaways

What breaks next

The seat is now safe: a row lock or a conditional update decides, and lab 6 shows that only the naive version sells a seat twice. But the event pages are read about 100 times more than they change, and every one of those reads still reaches PostgreSQL or a read replica: two million fans refreshing at 09:59 ask the same question millions of times. A replica copies the whole database, lag included, to answer reads that could come from memory. Keeping a copy of the answer, and knowing what must never be answered from a copy, is class 7 — Caching, the next rung of the scaling ladder.