Cruxly › System design › Databases: Choosing One

Choosing the database: seven worked case studies

same method, seven systems, seven different answers

Seven systems come up again and again in design interviews. Each has a different access pattern, so each ends up with a different store. This lesson runs one method on all seven: numbers, store, consistency, the push-back, the trap, and a script to say out loud. The method is the point. The stores are what it produces. It assumes you know ACID, isolation and storage engines from the earlier lessons and does not re-teach them.

Payments and ledger: boring on purpose

1. Access patterns and numbers. Assumption for the example: 3,000 payments per second at peak, each writing two ledger entries (a debit and a credit). Reads are by account and by payment, often joined to invoices. For scale, Stripe's public ledger write-up states 5 billion events a day, which is about 58,000 a second on average (our arithmetic), and describes Ledger as "an immutable log of events" based on double-entry bookkeeping.

2. The choice. A relational ACID database, with an append-only ledger_entries table, a unique constraint on the idempotency key, and balances derived from entries. Shard by account or merchant if one primary saturates; most ledger traffic partitions that way, but a transfer between accounts on two shards then needs a cross-shard plan, so keep one primary as long as the numbers allow. Stripe does not name its storage engine, so do not claim one.

3. Consistency, and what breaks. Strong, per account, with no lost updates. Get it wrong and you see a double charge, a negative balance, or money that exists on one side only. This is the case for real transactions with strong isolation (the isolation lesson covers the anomalies), not eventual consistency.

4. The push-back. "A NoSQL store scales better." Answer: the ledger is not short of scale tricks, it is short of tolerance for error. Partition by account and keep Postgres or MySQL. Reach for another store only when you can name the number one primary cannot meet.

5. The trap. Three of them: a mutable balance column with no entry rows (no audit, no replay), floats for money (use integer minor units), and writing the database then publishing to a bus in app code (use an outbox, covered in the search section).

Idempotency, concretely. Stripe's API v1 stores the status and body of the first request made with a key and returns it on retries, even for a 500. Keys can be pruned once at least 24 hours old, and a reused key with different parameters is rejected. Copy the shape: one unique key per operation, saved result.

Corrections. Never edit an entry. Post a reversing entry. Stripe's write-up uses a "clearing" measure (how much of the ledger is zeroed out at steady state) to spot problems such as a missing or mismatched event, and says it fixes data by reverting and reprocessing prior operations.

:::key Say this out loud: "Money is an append-only double-entry ledger in a relational database. Every write carries an idempotency key under a unique constraint. Balances are derived, and I would shard by account before I would give up transactions." :::

Chat messages: write-heavy, read by channel

1. Access patterns and numbers. Assumptions: 40,000 message inserts per second, 10 TB and growing, and one dominant read: the latest 50 messages in a channel, then older pages on scroll. Discord's public numbers: 12 Cassandra nodes in 2017 with billions of messages; 177 nodes with trillions at the start of 2022.

2. The choice. A wide-column or LSM store keyed by (channel, time bucket) with rows clustered by a time-sortable message ID. Discord partitions by channel plus a static time window (the post does not state the window size) and uses Snowflake IDs, which sort chronologically. One page of history is then one partition read. Writes are appends, the shape LSM storage absorbs well.

3. Consistency, and what breaks. Order inside a channel (by ID) and read-your-writes for the sender. Across channels, eventual is fine. Lose per-channel order and conversations scramble; lose the sender's own message and users resend.

4. The push-back. "Why not sharded Postgres?" It is defensible: Notion sharded Postgres by workspace, and Figma has also horizontally sharded Postgres. The honest answer: the only query here is a range by channel, writes are huge and append-heavy, and there are no cross-row transactions, so you give up little by leaving relational. If the team has no wide-column experience and writes are lower, sharded relational wins.

5. The trap. Hot and unbounded partitions, and operations cost. Discord moved from 177 Cassandra nodes to 72 ScyllaDB nodes, disk per node from about 4 TB to 9 TB, historical-read p99 from 40-125 ms to 15 ms, and insert p99 from 5-70 ms to 5 ms. Their stated pains were hot partitions, compaction backlogs and garbage-collection pauses. They also added Rust data services that coalesce requests for the same row and route by channel. And deletes leave tombstones: the migration stalled at 99.9999% on them. Do not pick this store at 10 GB, and do not say "NoSQL" as the reason; say "latest messages per channel".

:::key Say this out loud: "Partition by channel and time bucket, cluster by time-sortable ID, so history is one partition read. Order is per channel, eventual across channels. The cost I own is hot partitions and compaction." :::

News feed and timeline: read-heavy, precomputed

1. Access patterns and numbers. Assumptions, not Twitter's figures: 5,000 posts per second at peak, 200 followers on average, and 100,000 timeline reads per second. Fan-out on write then costs about 1,000,000 timeline inserts per second (5,000 x 200), against 100,000 cheap reads. The usual public reference is Twitter's 2012 "Timelines at Scale" talk, whose exact figures I could not confirm from a primary source, so none are quoted here. Its widely reported shape: timelines cached in Redis, a post's ID added for each follower, and a few very large accounts handled differently.

2. The choice. Home timelines as capped lists of post IDs in an in-memory store (Redis), built by fan-out on write. Posts live in a durable store (wide-column or relational), the follower graph in its own service. Accounts above a follower threshold are not fanned out; their recent posts are merged in at read time (a hybrid).

3. Consistency, and what breaks. Eventual. A post appearing seconds late is invisible to users. What breaks: a timeline that never contains your own post, or one that lists a deleted post. Hydrate IDs from the post store so deletes are respected.

4. The push-back. "Fan-out on read is simpler." It is, and it fits when reads are rare. Here reads outnumber posts and each read would gather followees' posts at request time. Move the work to the rarer event. The reverse holds for a 50-million-follower account: one post would mean 50 million inserts, so that account is pulled.

5. The trap. Fan-out on write for everyone; storing full post bodies in every timeline (store IDs, hydrate from a cache); keeping inactive users' timelines in memory (build on next login instead); no cap on list length.

:::key Say this out loud: "Reads dwarf posts, so I precompute timelines of IDs in Redis on write. Big accounts are merged at read time. Seconds of staleness are fine; a lost or ghost post is not, so I hydrate from the post store." :::

Product catalog and search: one record, one derived index

1. Access patterns and numbers. Assumptions: 20 million products, 1,000 searches per second, price and stock changing constantly, and two very different reads: ranked, faceted text search, and "give me product 123's current price".

2. The choice. Two stores with different jobs. The source of truth is relational or document (price, stock, attributes). A search engine (Elasticsearch or OpenSearch; Postgres full-text search can be enough for a small catalog) is a derived index, fed through an outbox or change data capture, with a cache for hot product pages. Ranked, faceted search over millions of products is what a search engine is built for; a typical OLTP schema is not.

3. Consistency, and what breaks. Search results: eventual. Elasticsearch's documentation says it refreshes indices every second by default, but only indices that received a search in the last 30 seconds, so changes usually become visible within about a second, plus the lag of your own pipeline. Price and stock at checkout: strong, read from the source. A stale price at checkout is a legal and trust problem; a stale search snippet is not.

4. The push-back. "Why not make the search engine the database?" Because it is a derived index: you must be able to rebuild it from the source (snapshot, replay changes, swap an alias). Without a source of truth you cannot. "Why not Postgres full-text search?" Postgres has GIN-indexed full-text search with ranking, and it is often enough. Move when you need relevance tuning, facets and fuzzy matching at a scale where a dedicated engine is easier to run.

5. The trap. Dual write from app code to the database and the index: a crash between the two, or two writers racing, leaves them diverged with no ordering. Write the product row and an outbox row in one transaction; a relay (Debezium's outbox event router is the standard example) publishes them. Delivery is at least once, so index writes must be idempotent. Also: no alert on relay lag.

:::key Say this out loud: "The database is the record; the index is derived and rebuildable. I feed it by outbox, accept about a second of search lag, and read price and stock from the source at checkout." :::

Shopping cart and session: always writable beats always right

1. Access patterns and numbers. Assumption: 1 million active carts, each read and written by a single key (cart or session ID), a few KB each, abandoned within days. No joins, no ad hoc queries.

2. The choice. A key-value store with TTL. Redis (with persistence and replication, not a single bare instance) for sessions; a durable key-value store such as DynamoDB for carts that must survive. Postgres is also fine at moderate scale. The origin story is the Dynamo paper (2007): Amazon's cart needed an "always writeable" store, so concurrent versions are merged on read. The paper reports the cart service handled tens of millions of requests resulting in well over 3 million checkouts in a single day, and that in a 24-hour profile 99.94% of requests saw exactly one version. Divergence is rare, but design for it. Dynamo is not DynamoDB: DynamoDB has a 400 KB item limit and offers strongly consistent reads and transactions at extra cost.

3. Consistency, and what breaks. Availability over strictness. With add-wins merging, an add is never lost, but a deleted item can reappear (the paper says so). Users accept that; they do not accept an empty cart. Inventory is not a cart concern: in this design the cart does not reserve stock; check it and decrement at checkout in the order database. (Some retailers do hold scarce stock briefly with an expiry; that is a separate reservation service, not the cart.)

4. The push-back. "Why not the main relational database?" You can, and at moderate scale you should. The key-value store earns its place when cart traffic is large, expiry matters, and you want carts to stay writable when the primary is unavailable.

5. The trap. Sticky sessions on app servers (a deploy empties carts); one non-persistent Redis instance (a restart loses everything); relational joins to build a cart page; holding stock in carts, which strands inventory.

:::key Say this out loud: "A cart is a key lookup with a TTL, so a key-value store. I favour writes always succeeding and merge on read, and I accept that a deleted item may come back. Stock is checked at checkout, not in the cart." :::

IoT and metrics: append-only time series

1. Access patterns and numbers. Assumptions: 1 million sensors reporting every 10 seconds, which is 100,000 points per second, never updated, read as "this sensor over this time range" and as aggregates ("average per 5 minutes"). At an assumed 16 bytes per raw point that is about 138 GB a day (100,000 x 16 x 86,400). For comparison, Prometheus documents an average of 1-2 bytes per sample after its compression, so the same stream would be roughly 9-17 GB a day there.

2. The choice. A time-series or columnar store behind a queue. TimescaleDB makes ordinary Postgres tables ("hypertables") that are automatically partitioned into time chunks. ClickHouse is columnar and recommends inserting in batches of at least 1,000 rows, ideally 10,000 to 100,000, because many small inserts create many storage parts and merge load. Prometheus-style stores fit infrastructure metrics, but its local storage is documented as not clustered or replicated, with 15 days of retention by default. I found no primary source comparing these engines at scale, so choose among them on team skill and query shape, not on a benchmark.

3. Consistency, and what breaks. Eventual, with at-least-once ingest. Make writes idempotent by (series, timestamp) so a replayed batch overwrites rather than doubles. Lose that and retries inflate your aggregates.

4. The push-back. "Postgres is fine." It is at modest rates, say low thousands of points per second (my rule of thumb, not a benchmark). At 100,000 per second with retention, a plain table needs partitioning, batching and aggressive vacuum, which is what a time-series extension automates. "Why a queue?" It absorbs bursts and lets you batch inserts.

5. The trap. One row per reading in a plain OLTP table with no partitioning or retention; high-cardinality labels (a user ID as a metric label multiplies series); no downsampling; deleting old rows (dead rows to clean up) instead of dropping whole old partitions; querying raw data for a year-long chart instead of the rollup.

:::key Say this out loud: "It's append-only and read as ranges and aggregates, so I use a time-series or columnar store behind a queue, with batched idempotent inserts, drop-whole-partition retention and rollups for old data." :::

Analytics and reporting: keep scans off the primary

1. Access patterns and numbers. Assumptions: 2 TB of order history, dashboards that scan 90 days and group by region and product, a nightly finance export, and 50 analysts running ad hoc queries. Each query touches millions of rows but only a few columns.

2. The choice. A separate columnar analytics store (ClickHouse, BigQuery, Snowflake or similar), loaded from the operational database by change data capture or batch ELT. Columnar layout reads only the columns a query needs and compresses well, the reverse of the row layout that storage engines optimise for transactions. Pre-aggregate the dashboards that everyone opens.

3. Consistency, and what breaks. Eventual, with a stated freshness target, say "within 5 minutes" (an assumption). Fine for reports, not for a balance shown to a customer; that stays on the primary. The analytics copy is derived, so losing recent data is survivable: rebuild from the source. (ClickHouse replicates asynchronously by default, acceptable for a derived copy and not for a system of record.)

4. The push-back. "Just use a read replica." It helps, but Uber's 2016 post on leaving Postgres describes streaming replicas that can lag by seconds and where long-running transactions block or get cancelled during replication replay. That was written about Postgres 9.2, and later versions give settings (such as hot_standby_feedback and max_standby_streaming_delay) that trade lag against cancelled queries. A replica also keeps the row layout, so scans stay expensive. Fine for a small team and modest data; past that, use the columnar copy.

5. The trap. Ad hoc analytics on the primary; "just add an index" to serve a reporting query (it slows every write); no pre-aggregation, so a dashboard rescans 90 days on every load; no freshness SLA, so nobody knows if the numbers are stale.

:::key Say this out loud: "Reports scan, orders write, so they get different stores. Change capture feeds a columnar copy with a freshness target. The copy is rebuildable, and the primary never sees an analyst's query." :::

Comparing the seven: what each one optimised for

Read it as three groups. Exact records: the ledger (and checkout price) must be right, so they get transactions. Throughput stores: chat and metrics are append-heavy, so they get partitioning by key and time. Derived views: the feed, the search index and the warehouse are rebuildable copies, so they get eventual consistency and a freshness target.

The method, again. (1) State the access patterns and numbers. (2) Name the default, then the store that beats it on a stated number. (3) Say what consistency the hot query needs and what breaks without it. (4) Name the push-back and your answer. (5) Name the trap. (6) Say it in three lines.

Where I could not verify a public figure, this lesson uses a labelled assumption instead: Twitter's timeline numbers, product-search sizing and IoT sizing. No primary source was found for Square's ledger or for Airbnb's search, so they are not used.

:::key Say this out loud: "Every answer came from the same question: what is the hot query, and what does it need to be right about? Exact data gets transactions, appends get partitioning, and views get rebuilt from a source of truth." :::

How to say it in the interview

"I won't name a database until I've written the access pattern and the numbers. Then I pick the store that serves the hot query, say what consistency that query needs and what breaks without it, and name what I'd put in front of or behind it. For each system the answer changes because the access pattern changes, not because I like a product."

Checklist

• For each system, I state the access pattern and numbers (or labelled assumptions) before naming a store.
• For money I choose an append-only double-entry ledger in an ACID database, with idempotency keys and derived balances.
• For chat I partition by channel and time bucket, cluster by time-sortable ID, and name hot partitions and compaction as the cost.
• For a feed I precompute timelines on write, pull huge accounts at read time, and label my fan-out numbers as assumptions.
• I keep search as a derived index fed by outbox or CDC, and read price and stock from the source at checkout.
• I put carts in a key-value store with TTL, accept merge-on-read, and keep inventory out of the cart.
• For time series I batch idempotent inserts behind a queue, expire by dropping partitions, and roll up old data.
• I keep reports off the primary: columnar copy via CDC, with a freshness SLA and pre-aggregated dashboards.

Related lessons

• How to choose a database
• ACID, precisely
• Isolation levels, in depth
• Data models: what each family is built for, and where it fails
• Storage engines: B-trees, LSM-trees, row vs column
• NoSQL modelling in practice: DynamoDB, Cassandra, MongoDB