SQL vs NoSQL: choosing a database in system design
SQL versus NoSQL is the wrong question; the right one is which data model fits your access patterns. Relational databases give you transactions, joins and flexible queries; key-value, document, wide-column, graph and time-series stores each give up some of that to do one kind of access very well at scale.
By Evin JohnPublished 7 min read
1. What the choice is about
"SQL" usually means a relational database: data in tables with a fixed schema, queried with SQL, with ACID transactions across rows and tables. "NoSQL" is a loose label for everything else: key-value, document, wide-column, graph and time-series stores, which differ from each other as much as from relational databases. The real decision is which data model, which consistency guarantees and which scaling approach match the way your application reads and writes.
2. Why interviewers ask about it
Storage is the part of a design that is hardest to change later, so interviewers want to see the choice reasoned rather than reflexive. "I would use MongoDB because it scales" is a weak answer. "Messages are always read by conversation in time order, at a high write rate, so a wide-column store partitioned by conversation and sorted by time fits" is a strong one. The follow-up is usually about what you gave up: transactions, joins, or a flexible query.
3. The six families
| Family | Data model | Strong at | Weak at | Examples |
|---|---|---|---|---|
| Relational | Tables of rows, fixed schema, foreign keys | Transactions, joins, constraints, ad hoc queries | Horizontal write scaling needs sharding or a distributed SQL system | PostgreSQL, MySQL |
| Key-value | An opaque value per key | Very fast lookups by key, simple to partition | Any query not by key | Redis, DynamoDB, etcd |
| Document | JSON-like documents, nested, flexible fields | Reading or writing a whole record in one go; varied shapes | Joins across documents, many-to-many data | MongoDB, Couchbase, Firestore |
| Wide-column | Rows grouped by partition key, sorted by clustering columns | High write rates; range reads within a partition, such as by time | Queries that do not include the partition key | Cassandra, HBase, Bigtable, ScyllaDB |
| Graph | Nodes and edges with properties | Multi-hop relationship queries | Bulk aggregation; very large scale-out | Neo4j, Amazon Neptune |
| Time-series | Timestamped points per series | Ingesting metrics, time-window queries, downsampling, retention | General-purpose updates and lookups | TimescaleDB, InfluxDB, Prometheus |
Two neighbors complete the picture: a search engine (Elasticsearch, OpenSearch) for full-text and faceted search, usually as a secondary index fed from the primary store, and object storage for large files, with their metadata kept in a database.
4. Start from the access patterns
- List every queryWrite them as sentences: "get a user's last 50 messages in a conversation", "find links created by this user", "charge a card and record it".
- Attach a rate and a sizeReads and writes a second, and how much data each touches. The dominant query decides most of the design.
- Mark the consistency needMust this read see the latest write? Must two writes succeed or fail together? See consistency and CAP.
- Pick the model that answers the dominant query directlyOne key lookup suggests key-value; one partition read in sorted order suggests wide-column; joins and transactions suggest relational.
- Check the restFor each remaining query, can the chosen store serve it, or does it need a secondary index, a cache or a second store?
Relational databases let you design the schema first and write queries later. Most NoSQL stores reverse that: you design the tables around the queries, often storing the same data more than once in different shapes. That is why the access patterns have to come first.
5. Transactions, joins and schema changes
Transactions. Relational databases provide ACID transactions across any rows and tables, with configurable isolation levels. Most NoSQL stores guarantee atomic updates to a single item, document or partition, and some add more: MongoDB supports multi-document transactions, DynamoDB offers transactions over a limited number of items, and Cassandra has lightweight transactions for compare-and-set on one partition. If a business rule spans several records (move money between two accounts, reserve a seat and record the booking), a relational store makes it simplest to get right.
Joins. In a relational database, joins are part of the query. In most NoSQL stores you either embed related data (a post document containing its first comments), denormalize (copy the author's name into each post), or join in application code with several reads. Denormalizing makes reads fast and writes more complicated, because one change must update several copies.
Schema evolution. A relational schema changes through migrations. The safe pattern is expand and contract: add the new column, backfill it, move reads and writes over, then drop the old one. A document store is "schemaless" only in the sense that the database does not enforce one; the application still has to read old and new shapes, often migrating each document lazily when it is next written.
6. Scaling, and a decision table
- Vertical first. One large server handles more than many candidates assume. Say when you would outgrow it, with a number.
- Read replicas spread read load across copies of a single primary. Replication is usually asynchronous, so replicas lag, and a user may not see their own write unless those reads go to the primary.
- Sharding splits data across several primaries by a key. Relational databases can be sharded in the application or with tools such as Vitess (MySQL) or Citus (PostgreSQL); distributed SQL systems such as Spanner, CockroachDB and YugabyteDB shard and replicate automatically while keeping SQL and transactions. See sharding.
- NoSQL stores partition by key out of the box, but you still choose the key, and a poor key creates hot partitions.
| If the dominant need is | Lean toward |
|---|---|
| Transactions across several records, constraints, reporting queries | Relational |
| Lookups by a single key at very high rates | Key-value |
| Whole records with varied fields, read and written as a unit | Document |
| Heavy writes, read back by key in time or sort order | Wide-column |
| Many-hop relationship queries | Graph |
| Metrics and events queried by time window | Time-series |
| Free-text or faceted search | A search engine fed from the primary store |
| Large files | Object storage, with metadata in a database |
7. Which to pick for common prompts
| Prompt | Primary store | Why |
|---|---|---|
| URL shortener | Key-value | Every redirect is one lookup by code; no joins |
| Payment system | Relational | Ledger entries must balance; transactions and constraints matter more than raw scale |
| Wide-column for messages | High write rate; read by conversation in time order | |
| Twitter timeline | Sharded store for posts, in-memory store for timelines | Posts by ID; timelines are precomputed lists |
| File storage | Object storage plus relational metadata | Blobs are large and immutable; folders and sharing are relational |
| Job scheduler | Relational | Workers claim jobs with row locks; status changes are transactional |
| Uber | In-memory geospatial index for drivers, relational for trips | Locations change every few seconds; trips and payments need transactions |
| Notification system | Relational for preferences, a queue for sends | Preferences are small and relational; delivery is a stream |
8. Myths and mistakes
- "NoSQL is always faster." Speed comes from the access pattern and whether the data is in memory. A primary-key lookup in a well-indexed relational database is typically sub-millisecond. NoSQL stores are fast at the queries they were designed for and slow or unable at the rest.
- "SQL can't scale." Read replicas, sharding and distributed SQL systems run very large workloads. Scaling a relational database takes more deliberate work than a store that partitions by default, but it is not a ceiling.
- "NoSQL means no schema." The schema moves into application code and has to be versioned there.
- "NoSQL means no transactions." Many stores support atomic single-item updates and some offer multi-item transactions. Check the specific product rather than the label.
- Picking by brand. Name the data model and the reason, then a product as an example.
- One store for everything, or a different store for every table. Most designs need one primary store plus a cache, sometimes a search index or a stream. More than that needs a clear reason.
9. How to talk about it in the interview
- State the dominant access pattern before naming any database.
- Name what you give up: "no cross-partition transactions, which is fine because each message belongs to one conversation".
- Say where you would change your mind: "if we needed reporting across all users, I would stream the data into a warehouse rather than query this store".
Fictional example
- Interviewer
- Where do you store messages?
- Candidate
- The main query is "latest 50 messages in a conversation", and writes are high, hundreds of thousands a second at peak in my estimate. So I would use a wide-column store with the conversation ID as the partition key and the message time as the clustering key. A page of history is then one sorted read from one partition.
- Interviewer
- What do you lose?
- Candidate
- Transactions across conversations and arbitrary queries. Neither is needed for messages. User accounts and group membership are small and relational, so they stay in PostgreSQL. If we add message search, I would feed a search index from the message stream rather than query the message store.
Practicing with Natively
The storage choice is where interviewers push hardest, so practice defending it out loud. In a mock interview, Natively transcribes the conversation live and can suggest talking points for each question as it comes in; comparing them with what you said is a quick way to spot gaps such as the access pattern you skipped or the trade-off you did not name. It can explain a screenshot you capture of a schema or a problem statement. The transcript and notes are kept for review afterwards, so you can check whether you led with the access pattern or with a product name.
Questions people ask
When should I choose SQL over NoSQL in a system design interview?
Choose a relational database when you need transactions across several records, constraints or flexible queries and joins, as in a payment ledger or a job queue. Choose a NoSQL store when one access pattern dominates, such as lookups by a single key or reads of a partition in time order, and the scale or write rate outgrows one relational primary. Start from the queries, not the label.
Is NoSQL faster than SQL?
Not in general. Speed comes from the access pattern and whether data is in memory; a primary-key lookup in a well-indexed relational database is typically sub-millisecond. NoSQL stores are fast at the queries they were designed for and slow or unable at others.
Can SQL databases scale horizontally?
Yes. Read replicas spread read load, relational databases can be sharded in the application or with tools such as Vitess and Citus, and distributed SQL systems such as Spanner and CockroachDB partition and replicate data while keeping SQL and transactions. It takes more deliberate work than a store that partitions by default.
Where to go next
Practice with the same assistant you would use on a call: it listens, transcribes and suggests answers from your own resume and the job description.
Facts on this page were last checked on . Product details come from the Natively app itself; if something here is out of date, tell us at hello@heynatively.com.
