System Design Data
SQL vs NoSQL
Think about one Instagram upload. The account and billing facts behind it must be durable and exactly right. The personalized feed that shows the photo must load fast for millions of people. Hashtags need a search path, and the next refresh wants a hot cache. One database label rarely covers all of that well, and that is the whole question this page answers.
usually means relational tables, joins, and transactions. is an umbrella for document, , , and similar engines that each optimize a different (the specific reads and writes an application performs most). Mature products often end up on purpose: several stores, each owning the workload it is good at.
Decide with these questions
What must never break, such as money, uniqueness, or ownership? Those facts want transactions.
Which reads and writes must stay fast at peak? They decide the data shape.
Can each request touch one partition (one slice of data on one machine)? Fan-out hides latency.
Freshness
How stale can an answer be before users notice? Lag budgets unlock replicas and caches.
What actually happens
Go back to that Instagram upload. The account and billing facts carry rules that must never be violated, so they want a store that enforces a (the declared shape and rules of the data) and can change several related rows in one transaction. The feed is different: it is one question asked millions of times, "give me the next twenty posts for this user." That is an , and a store that lays data out for exactly that question answers it faster than one that assembles it from joins on every request.
keeps facts normalized (each fact stored once, in its own table) so you can ask new questions later with a query. Many designs instead bake today's important questions into keys, documents, or partitions so a read touches one place. This is why slogans fail in design reviews. Argue from measured QPS (queries per second), consistency needs, and team skill, not from product names. A feed timeline may want a or model; orders and payouts often stay relational for (atomic, consistent, isolated, durable transactions).
When one primary plus indexes and replicas is no longer enough, you do not always "switch to NoSQL." You often add a specialized engine beside the relational one, choose a careful so related rows stay on one machine, or consider if you still need SQL semantics across shards. The rest of this page walks through each of those options, then sizes a million-user app to show which limit you actually hit first.
How to choose SQL or NoSQL
With the two ideas above in hand (facts that must hold, and questions that must be fast), three questions decide the store for any given workload.
1. Do correctness and flexible queries dominate? Payments, inventory, admin reporting, and joins that keep evolving usually start with , because the database enforces the rules and new questions do not need a new data layout.
2. Is the access pattern stable and huge? Single-key reads, append-heavy streams, and documents with highly variable shape may fit a , , or store, because those engines partition the data around that one known question.
3. Can one engine own everything? Often no. Mature products go on purpose, but only after measuring latency and write fan-out, and each extra engine needs an owner, a backfill plan, and a repair path.
Simple rule of thumb
Default to relational SQL until latency, cardinality (how many distinct rows or keys you hold), or geography breaks its assumptions. Then add a specialized store with evidence, not slogans. In simple terms: model writes in the smallest consistent place, denormalize reads where profiling demands it, and replicate derived data on purpose.
Schema vs access patterns
Every database has a data model, even if it does not enforce a rigid table schema. A document application still assumes field meanings, relationships, and valid state transitions. Relational databases can store denormalized data and JSON; non-relational products can offer transactions. Compare the specific query model, transaction scope, indexing, and distribution behavior rather than treating SQL and NoSQL as mutually exclusive sets of guarantees.
The three questions above lean on one distinction, so it is worth making explicit before the engine tour: a describes what must be true, and an describes what must be fast.
Both paths start from the same product requirements and end at the same place: measuring latency, cost, and operational load. They differ in what you design first.
Product requirements
Schema path
Invariants and consistency
Relational schema, constraints, joins
Derived read models when needed
Access-pattern path
Read and write access patterns
Key, document, or wide-column model
Indexes, shard keys, conflict handling
Measure latency, cost, and operational load
In a design review, each path comes with its own questions. Ask both sets before naming an engine.
Schema-first questions
- Which invariants must never break: uniqueness, balances, inventory, ownership? Those need constraints.
- Will teams need ad hoc reports and joins that nobody has written yet?
- Can schema migrations be planned without blocking product iteration for weeks?
Access-pattern-first questions
- What are the top reads and writes by QPS (queries per second), latency target, and payload size?
- Can each request hit one partition, or must it fan out to many machines?
- How stale can the answer be before a user or business process notices?
Quick contrast
With those questions in mind, here is the stereotype view of a typical OLTP database (online transaction processing, the live database behind an app) next to a typical family.
| Topic | Typical SQL OLTP | Typical NoSQL family |
|---|---|---|
| Schema | Strong upfront; migrations are first-class | Flexible documents or implicit schema via keys |
| Queries | Ad hoc joins, aggregations, constraints | Keyed lookups and pre-planned access paths |
| Transactions | Multi-row ACID common | Single-partition strong; cross-partition varies |
| Scale-out story | Read replicas easier than write scaling | Partitioning often built into the storage model |
The NoSQL families, one at a time
The right-hand column of that table hides five quite different engines. Each one below is described by what it optimizes, what it gives up, and where it usually fits, so you can match it to an rather than a brand name.
Document stores
A classic holds JSON-like documents, shards them horizontally through routers (a layer that sends each request to the right shard), and coordinates writes at the level of one document. Multi-document transactions exist for bounded use cases, but most designs still favor writes that stay inside one partition. Common fits are catalogs, content management systems, and user profiles whose attributes vary from user to user.
A second flavor combines a document store with a layer: a memory-oriented caching tier plus JSON persistence, SQL-like querying over documents, and sync patterns for mobile clients. Its multi-master replication (several nodes accepting writes at once) introduces conflict policies you must understand before adopting it. Common fits are sessions, edge-heavy mobile backends, and teams that want one engine for both key lookups and queries.
Key-value access patterns
The in-memory cache is the fastest member of the family: in-memory speed, rich structures (hashes, streams, sorted sets), and a cluster mode for horizontal scale. Its persistence modes trade durability for latency, so in most architectures it stays a cache or coordination layer rather than the source of truth.
The managed key-value table is the durable member: managed partitions with provisioned or on-demand capacity. Holiday-scale peaks are stress tests, not your default sizing assumption. Its signature technique is single-table design, where many entity types share one table and the key encodes the relationship, instead of mapping each entity from an entity-relationship (ER) diagram to its own table. The sketch below uses PK for the partition key (which machine) and SK for the sort key (order within that partition).
Single-table sketch
| PK | SK | Payload |
|---|---|---|
| USER#123 | PROFILE | name, email, … |
| USER#123 | ORDER#456 | total, status, … |
| ORDER#456 | ITEM#1 | product, qty, … |
Composite keys encode entity relationships. A global secondary index (GSI, a copy of the table sorted by a different key) unlocks alternate lookups at extra storage and write cost.
Wide-column stores
The classic database runs as a masterless ring (every node is a peer; there is no single coordinator to fail) on LSM storage (log-structured merge trees, an append-friendly on-disk layout), and it scales out linearly as long as partitions avoid hotspots. Tunable consistency means each query picks how many replicas must agree, and multi-DC (multiple data center) replication is built in. Public talks cite very large fleets; your takeaway is the data-model discipline, not a node-count arms race.
The sparse wide-table variant follows the Bigtable design: rows can have wildly different columns, consistency is strong within one row, and it integrates well with batch or stream analytics paths that scan wide tables.
Graph and specialized engines
A graph database stores each node's neighbors as a native adjacency list, so following a hop is cheap where recursive joins get expensive at depth. Distributed graphs still demand careful partitioning, because supernodes (nodes with an enormous number of edges) hurt every query that touches them.
Time-series engines compress temporal data and answer queries scoped to a time range. Vector stores serve retrieval workloads with ANN indexes (approximate nearest neighbor, finding the closest embeddings quickly) rather than the B-tree equality lookups a relational index is built for.
NewSQL (distributed SQL)
is the family for teams that need semantics but have outgrown one primary. Underneath, data ranges move between machines under a consensus protocol (replicas vote on the order of writes so all agree). Three shapes are common. Externally consistent SQL uses global shards with bounded clock uncertainty so transactions order correctly across regions in the managed-service story. Consensus SQL keeps SQL compatibility with automatic rebalancing and consensus ranges behind the scenes. Hybrid transactional/analytical SQL keeps SQL protocol compatibility while running separate row and column stores, so one deployment serves both live transactions and analytics.
Million-user sizing walkthrough
Storage size alone rarely chooses the engine. Estimate the working set that must stay fast, the read and write rates, the number of indexes updated per write, and the largest individual tenant or partition. A dataset that fits easily on disk may still overload a single hot key. Conversely, a large archive with rare scans may need cheap storage and batch processing rather than a high-throughput transactional cluster.
Knowing the families is not enough to pick one; you need to know which limit you hit first. This walkthrough turns a vague scale target into rows, bytes, and QPS so the choice falls out of the arithmetic.
Two estimates run in parallel. The storage path multiplies rows by row size, adds index overhead, then multiplies by the replication factor. The traffic path splits reads from writes and asks one question: is a single primary enough?
1M registered users
Storage path
Rows per entity
Row size estimate
Index overhead
Replication multiplier
Total storage footprint
Traffic path
Read/write QPS split
Single primary enough?
Yes
SQL primary + replicas
No
Partitioned model or specialized store
Here is the storage and traffic path worked for one million registered users. Notice how small the read QPS is compared with the index footprint.
| Estimate | Example math | Design implication |
|---|---|---|
| User row size | 1M users x ~1 KB profile row = ~1 GB base table | A single relational primary is usually fine for core users. |
| Indexes | Primary key + email + org_id indexes at 20-60% each = ~1.6-2.8 GB logical footprint | Index count can matter more than the table size. |
| Replication | Primary + 2 replicas = roughly 3x data and index storage | Replicas buy read scale and failover, not free storage. |
| QPS split | 100K daily active users x 20 reads/day ≈ 23 avg read QPS; peak 10x ≈ 230 | Many million-user apps are read-replica problems before shard problems. |
Real products have several entity families, and each one gives a different database signal. Scale the same method to ten million users and size each family on its own.
| Entity family | Sizing inputs | Database signal |
|---|---|---|
| Users | 10M rows x 1 KB base row = 10 GB; 3 secondary indexes at ~40% each = 12 GB extra | Relational is still simple if writes and joins fit one primary plus replicas. |
| Sessions | 10M active sessions x 500 B value + TTL index + memory overhead | Key-value cache or table fits because lookup is by session id and expiry matters. |
| Orders / ledger | 100M order rows x 2 KB + strict indexes on user_id, status, created_at | Use SQL when ACID (Atomicity, Consistency, Isolation, Durability) invariants are core. |
| Events / feed items | 50K writes/sec x 1 KB x retention days, partitioned by user, tenant, or time bucket | Append-heavy workloads may justify wide-column or log-shaped storage. |
Numbers to know
Take the Users row above and finish the bill. Table plus indexes comes to 22 GB. With a primary plus 2 replicas, it becomes 66 GB before retained logs, backups, and growth headroom. TTL in the Sessions row means time to live: each key expires automatically after a set period, which is why a cache fits.
Entity inventory (multi-table sizing)
The same four families, now read for their consistency needs. This is the column that decides whether a family can leave the relational primary.
| Entity | Volume / shape | Consistency |
|---|---|---|
| Users | 10M rows x ~1 KB + 3 indexes | Strong for profile writes |
| Sessions | TTL keys, ~2 KB x active users | Eventual OK with short TTL |
| Orders / ledger | Append + strict invariants | ACID transactions |
| Events / feed | High append rate, partition by user/time | Eventual with lag budget |
Whatever the arithmetic says, three modeling decisions follow it and are hard to undo later.
Primary key
Use stable, immutable ids. Avoid putting mutable email, region, or username in the primary identity, because changing them later rewrites every reference.
Only choose one after naming the dominant entity in queries. User or tenant keys often preserve locality; timestamps usually pile every new write onto one hot partition.
Read replicas
Route profile reads, dashboards, and history to replicas. Keep read-after-write paths (where a user must see their own change immediately) on the primary or a lag-aware route.
Normalized vs denormalized
Normalization reduces duplicated facts. If an author changes their name, one authoritative row changes and joins retrieve it wherever needed. Denormalization copies the name into a feed item so a read needs fewer lookups, but now name changes require updating copies or accepting historical values. Decide whether the copied value is an intentional snapshot or a current projection; those are different product meanings with different repair requirements.
The sizing walkthrough keeps mentioning "derived read models" and "feed copies." Those are denormalization, and it is the decision that most often turns one store into several.
Normalized means each fact lives in exactly one row. Denormalized means the same fact is copied into whatever shape a screen needs. CQRS (command query responsibility segregation) is the pattern of keeping a normalized write model and separate denormalized read models, kept in sync by events. The three shapes below are the ones you will meet.
Normalized (SQL-shaped)
Win: single writer per fact. Cost: joins under load.
Denormalized document
order: {
id, buyer_email,
items: [{ title, qty }],
total
}Win: one fetch per screen. Cost: duplicate data and careful updates.
Hybrid (common)
Denormalization write bill
The hybrid shape looks free on a slide. Follow one profile edit through it to see the bill.
Source-of-truth user row
Search document
Feed card copy
Profile cache entry
Analytics event
Freshness lag and repair jobs
One logical profile update becomes four physical writes. That may be worth it for fast reads, but you now own stale derived views, replay and backfill jobs, validation sampling, and rollback criteria, plus repair work whenever one copy drifts. Count those copies before calling cheaper.
Polyglot persistence and how teams migrate to it
Once you accept derived copies, several stores in one product stops being a smell and becomes a plan. Here is what that plan looks like in three familiar product shapes, and how teams move data between stores without a risky cutover.
Each split below keeps a relational core for money and identity, then hands one specialized workload to each extra engine.
Consumer social split (illustrative)
Marketplace split (illustrative)
Media platform split (illustrative)
Migration strategies
Keep one source of truth during migration. Backfill a new store from a known point, capture changes made during that backfill, and compare representative results before shifting reads gradually. Independent application writes to two databases can diverge when either write fails. An outbox or change-data-capture stream gives a replayable change path, while reconciliation finds missing or incorrect derived records. Define rollback before switching authoritative writes.
None of those splits started that way. Moving a live dataset from one store to another is its own project, and four patterns cover most of it.
Sequence the risk-reducing steps (CDC, then shadow reads) before turning off the legacy writer, because that last step is the one you cannot easily undo.
Decision framework
Choose from a concrete transaction. An order may need an atomic change to inventory and payment intent, while a public feed may tolerate delayed projections. Put each invariant inside a storage boundary that can enforce it, then add a specialized read store only if its measured benefit pays for replication, monitoring, backfills, and repair. Operating two databases includes keeping their meanings aligned, not merely connecting to two APIs.
Everything so far compresses into one lookup table: name the use case, and the fit and its reason follow.
| Use case | Often fits | Why |
|---|---|---|
| Accounts / ledger | Relational SQL | ACID rows and constraints |
| Shopping cart | Key-value table or in-memory cache | Keyed ttl-ish workloads, HA primitives |
| Social graph | Graph DB or cache graph service | Hop-heavy traversals |
| Activity feed | Wide-column or key-value table | Partitioned timelines |
| Session store | In-memory key-value cache | Sub-ms RAM tiers |
| Full-text relevance | Search index | Inverted indexes |
| Embeddings | Vector database | ANN queries |
| Global SQL semantics | Distributed SQL | Distributed consensus ranges |
Reading the "Why" column: HA means high availability (survives a node failure); ttl-ish means entries that expire on their own; an inverted index maps each word to the documents containing it; ANN is approximate nearest neighbor search over embeddings.
How to say it in an interview
The table gives you the answer; this gives you the sentence. "I'll start relational for the source of truth, then add cache, search, or read models after measuring p95 latency (the time the slowest 5% of requests take), write throughput, and query fan-out." Then back it with the metrics you would actually ask for.
- QPS split between reads and writes, plus peak-to-average ratio and hot-key skew, sizes the primary.
- Query shapes (lookup by id, range scan, join, search, aggregation, graph traversal) pick the data model.
- Freshness needs: read-your-writes (you see your own update immediately), eventual-consistency budget, and conflict policy.
- Operations: backup restore time, schema migration process, multi-region needs, and team expertise all constrain the choice.
Try it: stress-test SQL vs NoSQL for one product slice
Pick the dominant query shape, the freshness bar, and the scaling signal. The lab does not replace measurement; it shows which default you should defend in an interview before proposing polyglot storage.
Ad hoc joins and foreign keys across entities dominate the read path.
SQL default
selectedOne primary, explicit schema, transactions, and indexes until metrics prove otherwise.
NoSQL specialization
Partition-first modeling when queries stay local and you accept repair or conflict policies.
Polyglot slice
Source of truth plus derived search, cache, or feed stores with measured freshness budgets.
- Logical writes per user edit
- 2
- Illustrative 10M-user footprint (GB, 3 copies)
- 6
- Interview lean
- SQL
Relational storage fits best here: multi-row invariants and flexible joins are cheaper to enforce in one primary than in application repair jobs.
Start relational when invariants live in joins. Flip to key-lookup with hot-partition risk and eventual tolerance to see when a specialized store earns its operational tax.
Count denormalized copies. Each extra derived document is another write on every profile edit. That cost often exceeds the savings from avoiding a join.
Choosing and explaining when SQL is enough vs when NoSQL helps
A complete explanation connects the mechanism to a requirement. Describe what improves when you introduce it, which new failure cases become possible, and how you would know that the trade-off is worthwhile.
Explain the core idea
SQL is enough when one primary plus indexes, read replicas, and caching meet the measured workload.
NoSQL helps when the top access pattern is stable, partition-local, and too large or too latency-sensitive for flexible relational queries.
For a million-user app, prove write QPS, storage, and hot-key pressure before jumping to sharding or a new datastore.
When it belongs
The interviewer asks for the database choice before giving enough workload detail.
The design has user profiles, orders, inventory, timelines, feeds, sessions, or counters.
A single table looks simple now but may become a hot row, hot partition, or write amplification problem.
How it fails
Choosing NoSQL too early loses joins, constraints, and ad hoc recovery queries.
Choosing SQL without access-pattern review can create huge secondary indexes, replica lag, and one overloaded writer.
Dual-writing to two stores without an outbox, CDC, or reconciliation creates silent divergence.
Trade-offs to weigh
SQL optimizes correctness and flexibility; NoSQL optimizes known access paths and horizontal distribution.
Read replicas improve read QPS but add consistency lag; sharding improves write/storage scale but adds routing and migration cost.
Denormalized read models reduce latency while increasing storage, backfill, and repair work.
Common misunderstandings
Do not say SQL cannot scale; say which dimension is failing.
Do not say NoSQL has no schema; the schema moves into keys, documents, and application code.
Do not shard before asking whether indexes, caching, replicas, or archive tables solve the actual bottleneck.
Wrap-up
You have seen the two ways to think about data, the engine families, the arithmetic, and the cost of copies. Here is the one idea to keep, followed by the papers the field keeps citing.
What to remember
versus is really several workload decisions, not one. Model writes in the smallest consistent place, denormalize reads where profiling demands it, and replicate derived data with CDC (change data capture) or events instead of accidental dual writes.
Primary sources
- DeCandia et al.: Dynamo (SOSP 2007): the AP (availability over consistency) key-value trade-offs that shaped NoSQL.
- Chang et al.: Bigtable (OSDI 2006): the wide-column store model behind the sparse wide-table engines above.
- PostgreSQL Docs: JSON Types: document patterns inside a relational engine, for when you need both.
Practice and further reading
Use these exercises to connect the lesson to a design decision. State your assumptions, trace what happens to one request, and explain the rule that must remain true even during a failure. That rule is the design's correctness invariant.
Focus for this lesson: Select storage from access patterns, constraints, transaction scope, consistency, scale path, operability, and migration cost.
Explain the decision
Why is “NoSQL scales, SQL does not” a weak answer?
Read the reasoning
Modern SQL systems scale in multiple ways; the real decision is data model, query/transaction needs, consistency, operational maturity, and partitionability.
Apply it to a production scenario
A product needs account balances plus flexible activity documents. One database or two?
Read one approach
Keep ledger invariants in a transactional relational model; add a document/read store only when its query and scaling benefit justifies synchronization.
Work through the numbers
A table grows by 80 million 600-byte rows/month with two indexes adding 70% overhead. Estimate monthly raw growth.
Read the worked calculation
80M × 600 × 1.7 ≈ 81.6 GB before replication, WAL, page slack, and backups.
Reason through a failure
What invariant drives the primary-store choice for money movement?
Read the recovery reasoning
Every transfer is atomic and balanced in the authoritative ledger; derived stores may lag but cannot become the source of truth.
Sources and further study
Continue with Database Indexing.