The right question is never which database you like. It is what the data looks like, how it is written, how it is read, and what happens when two writers collide, and the store falls out of those answers. This page cross-references every systems-design problem on this site with the datastore that wins there and the one-line reason it wins, grouped by store family. Each row links to the full write-up, so this doubles as a map of the whole systems section.
The procedure that generates every row is the same. Name the access pattern first, meaning the exact reads and writes on the hot path, with their rates. Name the guarantee the product actually needs, atomicity across rows, per-key ordering, expiry, replay, or nothing at all. Then let the pattern and the guarantee pick the family, and only at the end pick a brand inside the family. Saying "we'll use Postgres for everything" or "we'll use MongoDB because it scales" before naming the access pattern is the classic way to get the storage decision wrong.
Relational, when money or invariants are on the line
A relational store earns its place when several rows must change together or not at all, when an auditor's questions map onto SQL, and when the write rate is modest enough that correctness can be the priority. Every money problem on this site lands here, and so does every problem whose core is a single authoritative decision, like the last hotel room or the last parking spot. The deep dive on internals lives in the PostgreSQL note.
| Problem | What it stores there | Why relational wins |
|---|---|---|
| Payment system | Append-only double-entry ledger, partitioned by month | Money must reconcile provably against the bank, and debits equal credits is a multi-row invariant |
| Hotel reservation | Per-night inventory rows with a version column | Compare-and-set on the version stops two guests buying the last room |
| Rideshare | Trip row plus append-only trip events | Trips are durable, auditable, and carry payments, so they get transactions while locations stay in memory |
| Stock brokerage | Orders sharded by user id | Order state chooses consistency over availability, and per-user listing is a single-partition read |
| Digital wallet | Event-sourced per-account log over a relational baseline | Ordered, replayable money movements per account with an audit trail |
| Notification system | Device tokens, preferences, and a delivery log | A unique key on the delivery log doubles as the idempotency record |
| Parking lot | Tickets and spot state per garage | One conditional UPDATE decides the last spot, correctness over throughput at one request a second |
| Cloud file storage | Sharded metadata by namespace | Version commits are atomic per namespace while the bytes live in object storage |
| Social graph search | Adjacency lists in sharded MySQL behind a write-through cache | One keyed read returns a whole friend list, the TAO shape |
| Proximity service | Replicated businesses table with a geohash index | The source of truth is about 100 GB, small enough to replicate rather than shard |
Wide-column and key-value, when writes are heavy and the query is known
Wide-column stores like Cassandra and managed KV like DynamoDB win when the write rate is high, the access pattern is known in advance, and every hot read is a point lookup or a single-partition range. You give up joins and ad-hoc queries and buy linear write scaling, which is exactly the trade messaging and lookup-table problems want.
| Problem | What it stores there | Why wide-column or KV wins |
|---|---|---|
| Chat system | Messages partitioned by channel id | Append-heavy writes read back as recent ranges inside one partition, no joins on the hot path |
| Ephemeral messaging | Messages with native row TTL by conversation | Every row must expire on its own, so TTL belongs to the store, not to application code |
| URL shortener | Code to URL keyed by short code | A single-key point lookup on a read path a hundred times heavier than writes |
| Key-value store | The Dynamo-style quorum store itself | Point get and put at massive scale, staying writable through partitions |
| Distributed email | Mailbox metadata partitioned by user | Small hot user-scoped rows with transactions, while bodies live in object storage by content hash |
| Collaborative editor | Operation log partitioned by document id | Append-only edit operations replayed in server order rebuild the document |
In-memory, when the data is hot and disposable
Memory is the right home for data that is overwritten in seconds, rebuilt cheaply, or read at rates no disk path can serve. The recurring move is to notice that durability buys nothing for data that expires before a restore would finish, and the Redis note covers the structures underneath these rows.
| Problem | What lives in memory | Why memory wins |
|---|---|---|
| Rate limiter | Token-bucket counters with TTL, atomic via Lua | Sub-millisecond check-and-decrement on every request, expiry tracks only active callers |
| Nearby friends | Live locations under a 60 second TTL, deliberately not durable | Positions overwrite every 30 seconds, and the expiring key doubles as the offline signal |
| Gaming leaderboard | Sorted sets as the rank view, rebuildable from the match log | Exact rank in logarithmic time from skip-list counts, treated as a view rather than the truth |
| Distributed cache | The sharded cache itself | Point lookups at hundreds of thousands of operations per second shielding the database |
| Autocomplete | Prefix to top-K tables held fully in RAM | One bounded read per keystroke under a twenty millisecond budget |
| Stock exchange | The order book, one core per symbol | Microsecond price-time matching over data that fits in cache, with a sequenced log as the truth |
| Rideshare | The driver geo index, sharded by city cell | 1.25 million disposable position writes per second that would crush any durable store |
Object storage, when the bytes are big and immutable
Object storage wins whenever the payload is large, written once, and read by key, and it pairs with a CDN the moment readers are geographically spread. The metadata that describes the bytes always lives somewhere else, which is the two-plane split that shows up in every row here.
| Problem | What it stores there | Why object storage wins |
|---|---|---|
| Video streaming | Encoded segments and manifests at the origin | Egress near 125 Tbps is a byte-placement problem, so cheap origin plus CDN caches carry it |
| Object storage | The system itself, metadata and data planes split | Eleven nines of durability from erasure coding, with hot metadata scaled separately |
| Cloud file storage | Content-addressed four megabyte blocks | Dedup and delta sync fall out of addressing blocks by their hash |
| Web crawler | Raw HTML keyed by content hash | Identical pages dedupe by construction, and the scheduler's bookkeeping stays in a database |
| Google Maps | Immutable map tiles behind a CDN | A trillion tiles fetched by zoom and coordinate key from an edge near the viewer |
| Street View blurring | Petabytes of panoramas, originals access-controlled | An idempotent batch pipeline over an immutable corpus, re-runnable as models improve |
Search indexes, when the query is words
An inverted index maps each term to the documents containing it, which turns keyword search from a corpus scan into a lookup whose cost scales with the result size. The Elasticsearch note covers segments, refresh, and why these indexes are views over a source of truth rather than the truth itself.
| Problem | What it indexes | Why an inverted index wins |
|---|---|---|
| Search service | Documents in sharded immutable segments | Ranked keyword lookup served lock-free from segments, rebuilt behind an alias |
| Video search | Metadata and transcripts, next to a vector index | Text wins exact and rare terms while vectors win paraphrase, so both run and the ranker merges |
| Distributed email | Per-user indexes that never cross accounts | Search space per query is one mailbox, so sharding by user makes every query small |
| Post search | Posts by term, sharded, fed from the write log | Billions of tiny documents where fan-out and freshness dominate the design |
Vector indexes, when the query is similarity
When the question is what is most similar rather than what matches these words, embeddings plus an approximate nearest neighbor index replace the inverted index. The trade is recall against latency and memory, which is the entire subject of my ANN benchmark project, and the vector databases note covers the index families.
| Problem | What it indexes | Why a vector index wins |
|---|---|---|
| Similar listings | Listing embeddings, rebuilt nightly and swapped atomically | Nearest neighbors in behavior space on every page view under a hundred milliseconds |
| Visual search | A billion image embeddings in sharded HNSW | Nearest catalog vectors to a query crop without comparing against everything |
| Video recommendation | Item embeddings from a two-tower model | No ranker can score billions of items per request, so retrieval must be a vector lookup |
| Harmful content detection | Perceptual hashes and media embeddings | Re-shared violating media is caught by index lookups before any expensive model runs |
| People you may know | Graph embeddings as a candidate source | Two-hop traversal finds structure, embeddings find similar people the graph has not connected yet |
Logs and streams, when history must replay
An append-only partitioned log is the right source of truth when consumers need to replay history, when several systems must see the same events in the same order, or when an audit depends on reproducing a computation. The Kafka note covers offsets, consumer groups, and replication.
| Problem | What the log holds | Why a log wins |
|---|---|---|
| Ad click aggregation | Raw clicks retained for ninety days | Billing-grade counts must be reproducible, so the log is the truth and aggregates are views |
| Distributed message queue | The partitioned log itself | Sequential disk writes sustain a gigabyte per second, and retention makes replay free |
| Stock exchange | Every inbound message, globally sequenced | Replicas replaying the same ordered stream stay bit-identical, and failover promotes a correct standby |
| Metrics monitoring | The ingest buffer between agents and storage | Collectors and the TSDB scale and fail independently |
| Top-K heavy hitters | Raw events, replayable and archived | The exact batch count that corrects the sketch needs the full history |
Time-series and OLAP, when the axis is time
Metric workloads append millions of timestamped samples per second and read them back by recency, which random-write B-trees cannot survive. Purpose-built stores compress deltas, roll data into coarser resolutions as it ages, and index series labels separately. The time-series databases note covers Gorilla compression and retention tiers, and ClickHouse covers the columnar side.
| Problem | What it stores there | Why time-series or columnar wins |
|---|---|---|
| Metrics monitoring | A million samples per second across ten million series | Delta-of-delta and XOR compression make append-heavy, recency-read data cheap |
| Ad click aggregation | Per ad, per minute aggregates in a columnar store | Analysts scan a few columns over long ranges, the columnar sweet spot |
| Google Maps | Live segment speeds keyed by edge and window | The traffic model reads recent windows per road segment, nothing older matters |
Graphs, coordination stores, and the honest none
Relationship-walking problems store adjacency lists sharded by user, so one hop is one keyed read, and social graph search, people you may know, and the social platform all take that shape rather than reaching for a dedicated graph database. Coordination stores like ZooKeeper and etcd hold tiny, precious state off the hot path, worker-id leases in the unique ID generator, ID range leases in the URL shortener, and the hash ring in the collaborative editor. And some problems need no datastore where you expect one. The Snowflake generator mints uniqueness from its bit layout with nothing in the write path, top-K counts approximately in a bounded sketch, and the interplanetary design merges state through CRDTs because no store can coordinate across a forty minute round trip. Saying "this piece needs no database, and here is why" is one of the strongest storage answers available.
References
- Kleppmann, Designing Data-Intensive Applications (2017), the book behind most of these trade-offs.
- DeCandia et al., Dynamo: Amazon's Highly Available Key-value Store (2007).
- Chang et al., Bigtable: A Distributed Storage System for Structured Data (2006).
- Bronson et al., TAO: Facebook's Distributed Data Store for the Social Graph (2013).