SQL vs NoSQL: The System Design Interview Decision Guide

You're designing a ride-sharing app. The interviewer asks where you'd store trip data. You say "PostgreSQL." They nod. Or you say "Cassandra." They also nod. Both can be right. Both can be wrong. Neither answer is free.

The SQL vs NoSQL question in a system design interview isn't trivia. It's a probe for how you reason about consistency, scale, and the requirements the system actually has.

The candidate who just says "I'd use MongoDB" without a reason gets a lower score than the candidate who picks MySQL and explains exactly which tradeoff they're accepting. The database is almost secondary. The reasoning is everything.

---

The One Tension That Decides It All

Every database question reduces to the same tradeoff: consistency vs availability at scale.

SQL databases give you strong consistency. Every read reflects the latest write. Every transaction either fully succeeds or fully rolls back. That guarantee costs you something: coordination overhead, write bottlenecks, and limited horizontal scalability.

NoSQL databases trade some of that consistency for availability and throughput. More nodes, more writes per second, more geographic distribution. But your data might be stale for a few hundred milliseconds, and multi-entity transactions are often impossible or painfully limited.

You're choosing which cost to accept. Pick based on requirements, not habit.

Not "what does Twitter use." Not "what did I learn in my last job." What does this specific system need.

---

What SQL Actually Buys You

Relational databases (PostgreSQL, MySQL, Oracle) enforce ACID properties: Atomicity, Consistency, Isolation, Durability. When you debit one account and credit another, both happen or neither does. When you read a row, you see its current committed state. No surprises.

The reason to choose SQL isn't just "structured data." It's the transaction guarantee.

SQL also gives you joins across tables, foreign key enforcement, and ad hoc queries via a mature query language. You can ask questions you didn't anticipate at schema design time. That matters more than it sounds once your data model gets complex and your product manager starts asking for reports at 4pm on a Friday.

Scaling SQL typically means vertical scaling (more CPU, RAM, faster disks) and read replicas for read-heavy workloads. A well-tuned PostgreSQL instance handles millions of OLTP transactions per day. For many products, that's more than enough. Write scaling is harder. Sharding adds coordination complexity, and distributed transactions across shards are expensive. But crossing that threshold takes real scale. Most products never get there.

---

Four NoSQL Families, Four Completely Different Problems

NoSQL is a category, not a database. The four families have almost nothing in common except the absence of rigid relational tables. Picking the wrong one is a different kind of failure from picking NoSQL over SQL at the wrong scale, and it happens just as often.

Type	Examples	Best for	Key tradeoff
Document	MongoDB, Firestore	Variable-shape data, CMS, catalogs	Schema drift in application code
Key-value	Redis, DynamoDB	Session storage, caches, simple lookups	Limited query expressiveness
Wide-column	Cassandra, HBase	Time-series, IoT, activity logs, chat	Must model around access patterns upfront
Graph	Neo4j	Social graphs, fraud detection, recommendations	Niche; heavy for non-graph use cases

Picking the wrong NoSQL family is as bad as picking NoSQL over SQL for the wrong reason. A graph database for a leaderboard is overkill. Cassandra for a user profile store with no clear partition key is a disaster. Both will cost you the interview.

One of these is not a real database. Your data is probably in it anyway.

Document stores

MongoDB and Firestore store JSON-like documents with flexible schemas. You can add fields without migrations. This sounds amazing until you have a collection where some documents have email, some have email_address, and some have neither, because three teams shipped features over eighteen months without talking to each other.

Schema flexibility doesn't eliminate enforcement. It moves it from the database to your application code. That's not a free lunch. It's a loan with variable interest.

Key-value stores

Redis operates in memory with sub-millisecond latency, making it the obvious choice for caches, session storage, leaderboards, and rate limiting. It's not a primary data store unless durability is genuinely optional for your use case.

DynamoDB is a fully managed AWS service with p99 latency in the 10-20ms range. It supports millions of requests per second and scales automatically. The catch: query flexibility is limited. You must know your access patterns at design time, because you design the table around them, not the other way around.

Wide-column stores

Cassandra's architecture is built for write throughput. Writes hit an in-memory memtable first, then flush to an immutable SSTable on disk. This Log-Structured Merge tree approach means writes almost never wait for disk seeks. Netflix runs approximately 1 trillion requests per day on Cassandra across its viewing history and recommendation pipelines.

The catch: every query must include the partition key. You can't scan a Cassandra table for arbitrary conditions. You design data models around the queries you'll run. If you don't know those queries upfront, you have a very large, very expensive problem.

Graph databases

Neo4j stores nodes and relationships natively. Traversing a social graph to find "friends of friends who bought product X" is multiple orders of magnitude faster than the equivalent recursive SQL join. If your core product feature is traversal-heavy relationship queries, graph databases are compelling. If relationships are incidental, they're not worth the operational overhead.

---

ACID vs BASE: It's Not a Lifestyle Choice

SQL gives you ACID. Many NoSQL systems operate on BASE: Basically Available, Soft state, Eventually consistent.

Under eventual consistency, a user who updates their profile might see the old value for a few hundred milliseconds because their read hit a replica that hasn't caught up yet. For most social features, that's invisible. For a bank balance, it's a lawsuit.

This maps directly to the CAP theorem: in a distributed system, during a network partition you must choose between consistency and availability. SQL databases typically choose consistency (CP). Cassandra and DynamoDB let you tune which you prefer, but default toward availability.

The interview signal: anywhere money, inventory, or authorization changes hands, the answer is almost always "strong consistency, SQL."

For a deeper treatment of what each consistency model actually guarantees in practice, see Eventual vs Strong Consistency: The Trade-off System Design Interviews Test.

---

When SQL Wins (and When It Doesn't)

The requirements tell you. You just have to ask.

Choose SQL when:

• Financial transactions appear anywhere (payments, payouts, subscriptions, escrow)
• Inventory needs to prevent overselling (ticket purchases, hotel bookings)
• Multi-entity writes must be atomic (transfer funds, create order and reserve stock simultaneously)
• Your data model is naturally relational with many join paths
• You need ad hoc analytics without a separate data warehouse
• Your write volume fits within a single well-tuned instance or master-replica setup

Choose NoSQL when:

• Write volume exceeds what vertical scaling can handle (hundreds of thousands of writes per second)
• Your data is sparse, variable-shape, or schema-free by nature
• You need geographic distribution with local-region latency
• You know your access patterns upfront and can optimize your data model around them
• Time-series or event log data with append-only patterns
• You need sub-millisecond latency and are willing to trade durability for it (Redis)

---

How to Decide in the Interview Room

The decision framework, in order. Go through it out loud. That's the whole point.

1. Clarify the consistency requirement. Before you pick a database, ask what happens if two users see different values. If the answer involves money, safety, or authorization, you want strong consistency.

2. Estimate write volume. If the interviewer gives you 10,000 writes per second, SQL is under serious pressure. At 100 writes per second with a good schema and indexes, it's probably fine with vertical scaling.

3. Ask about access patterns. Can you enumerate every query you'll run? If yes, a NoSQL model tuned to those patterns is a good fit. If you need flexible queries, SQL wins. You can't have both.

4. Check for multi-entity transactions. "A user can transfer credits to another user" requires atomicity across two rows. SQL handles this natively. Cassandra has lightweight transactions, but they're limited and expensive. MongoDB added multi-document transactions in 4.0, but you're now fighting the document model.

5. State your choice with the tradeoff. Don't just say "I'd use Postgres." Say "I'd use PostgreSQL for orders because we need ACID guarantees, and I'd accept the write bottleneck since orders won't exceed 1,000 per second at this scale. If we hit 10 million users I'd revisit sharding or moving to a distributed SQL database."

Interviewers aren't looking for the right answer. They're looking for the right reasoning process.

---

In Practice, You Use Both

The "SQL vs NoSQL" framing implies you pick one. Production systems rarely work that way.

Netflix uses Cassandra for viewing history and recommendation signals. It uses PostgreSQL for billing and user account management where transactions matter. Discord started on MongoDB, migrated to Cassandra because MongoDB's random-access read pattern caused latency spikes under load. Facebook runs MySQL for its transactional core and Cassandra for its messaging backend.

The modern answer to "SQL or NoSQL" is almost always "both, scoped to what each handles best."

This is what happens when you forget Postgres can do JSON documents and key-value lookups too.

In an interview, saying "both" isn't hedging. It's demonstrating you understand that different subsystems have different requirements. Orders need ACID. Activity feeds don't. User profiles need consistency. View counters don't. Name the subsystems, name the databases, name the tradeoff for each. That's the answer that gets you the hire.

---

Practice Picking Under Pressure

The decision sounds clean on paper. Under interview conditions, with an interviewer staring at your half-drawn architecture diagram, it feels different. A lot of people blank. A lot of people default to whatever they used at their last job and can't explain why.

SpaceComplexity runs realistic voice-based mock system design interviews where you have to make and defend these calls out loud, the same way you would in an actual interview. The rubric-based feedback tells you whether your database rationale landed or whether you skipped the tradeoffs that would have gotten you the hire.

---

Key Takeaways

• The deciding factor is consistency. Anything involving money, inventory, or authorization points toward SQL. Everything else, check write volume and access patterns first.
• Polyglot is the real-world answer. Most systems at scale use SQL for transactional subsystems and NoSQL for high-volume, eventually-consistent ones. Picking one for the whole system is almost always wrong.
• State the tradeoff, not just the choice. "I'd use Cassandra for writes because I need linear horizontal scaling, accepting eventual consistency for this feature" is the answer that gets you hired.

---

Further Reading

• ACID properties (Wikipedia)
• BASE model and eventual consistency (GeeksforGeeks)
• Apache Cassandra documentation
• Amazon DynamoDB developer guide
• PostgreSQL official documentation