How it works
Start with the workload, not a database name. List the main reads and writes, their latency targets, expected volume, and rules that must always hold. “Fetch the latest 50 messages in a conversation” and “reserve every night of a hotel stay atomically” lead to different storage requirements.
SQL is a query language, but this comparison usually means relational databases versus nonrelational databases. Relational databases organize data into tables with explicit relationships, indexes, and constraints. For a booking system, reservations and inventory can be updated in one transaction, with locking or conditional updates preventing overselling.
NoSQL covers several models, not one architecture. Key-value stores fit direct lookups, document stores group related fields into documents, wide-column stores support partition-oriented access, and graph databases support relationship traversal. Compare a specific model and database against your workload rather than treating all NoSQL systems alike.
Model the actual queries. A chat store might partition messages by conversation ID and order them by sequence number, making recent-message reads bounded. In a relational design, a composite index can support the same query; the decision then depends on throughput, partitioning, transaction scope, and operations, not the presence of rows.
Check guarantees separately from the model. Some NoSQL databases support transactions and strongly consistent reads; relational databases can expose stale reads through asynchronous replicas. For each important operation, specify atomicity, isolation, read freshness, durability, and behavior during failures.
Use multiple stores only when their roles justify the complexity. Keep authoritative orders in a transactional database and build a search index asynchronously for discovery, for example. Explain how updates reach the derived store, how stale it may become, and how it can be rebuilt.
A worked example
Choosing storage for hotel reservations
Suppose a hotel system manages 10,000 hotels and must reserve a room type for three consecutive nights. Start with a relational database containing inventory rows keyed by hotel, room type, and date. In one transaction, lock the three rows in a consistent order, verify availability, decrement inventory, and insert the reservation with a unique idempotency key. A separate search index can serve destination and price filters, but checkout must recheck authoritative inventory because search may lag. A NoSQL database could also work if it supports the required atomic operation across those records. The deciding factor is whether its transaction boundaries and contention behavior fit the reservation invariant, not whether it stores documents.
Trade-offs to say out loud
- Flexible queries versus predictable access paths
- Relational databases often make joins and changing queries easier, while access-pattern-specific models can make known queries efficient but new queries require indexes or new data layouts.
- Transaction scope versus distribution cost
- Cross-record or cross-partition transactions require coordination, so verify the database's supported scope and cost rather than assuming either category solves them cheaply.
- Normalization versus denormalization
- Normalization reduces duplicated facts, while denormalization can reduce read work at the cost of write amplification and keeping copies consistent.
- Horizontal scale versus partitioning constraints
- Both relational and nonrelational databases can scale horizontally, but partition-key choice, skew, cross-partition queries, and rebalancing determine how well they do.
- Schema flexibility versus validation
- Flexible documents simplify some data evolution, but applications still need validation, version handling, and migrations for existing records.
- Specialized performance versus operational complexity
- Adding another database can improve a particular workload while increasing backup, monitoring, recovery, data synchronization, and team expertise requirements.
When to reach for it
- The system has financial, inventory, uniqueness, or ownership invariants that require explicit transaction and concurrency guarantees.
- You need to choose between joins over related entities and denormalized records tailored to a few high-volume queries.
- The workload has substantial write volume, large datasets, or uneven traffic that makes partitioning and hot keys important.
- Different parts of the system have different needs, such as authoritative order state, full-text search, and time-series analytics.
- An interviewer asks you to justify a database choice or explain when your initial choice would stop working.
How to bring it up in the interview
- Lead with requirements: “We need atomic multi-night reservations and several related queries, so I would start with a relational database.” Name a product only after explaining the required capabilities.
- Show one concrete schema or document shape, the primary key, and the indexes for your busiest queries. Expect follow-up questions about scans, pagination, and index write costs.
- State the correctness boundary: which records must change together, what isolation or conditional-write mechanism enforces the rule, and whether replica reads may be stale.
- Use workload estimates to defend scaling decisions. Interviewers may ask about peak throughput, dataset size, hot partitions, and what happens when one tenant dominates traffic.
- Describe failure and evolution paths: failover guarantees, backups, schema changes, and how you would add a read model without creating an unsafe dual write.
Common mistakes
- Saying SQL cannot scale horizontally or that NoSQL is automatically faster.
- Equating SQL with ACID and NoSQL with eventual consistency without checking the chosen database and configuration.
- Choosing a document database solely because the API returns JSON, or choosing a relational database solely because the data has a schema.
- Claiming transactions prevent overselling without specifying isolation, locking, conditional updates, or constraints.
- Adding several databases without defining the source of truth, synchronization mechanism, and acceptable staleness.
Where it comes up
Interview questions where sql vs nosql is part of a strong answer, and why.
- Design a URL shortener like BitlyShort-code lookups provide a concrete way to compare a key-value model with a relational table and unique index.
- Design a hotel booking systemAtomic reservations across multiple inventory dates make transaction scope and concurrency guarantees central to storage selection.
- Design a payment systemLedger integrity, retry-safe operations, and concurrent balance changes require a database choice grounded in explicit transactional guarantees.
- Design a chat app like WhatsAppMessage history requires a storage model and partitioning strategy that support high write volume and bounded, ordered conversation reads.
- Design a news feed like FacebookMaterialized feeds make the trade-off between relational queries and denormalized, access-pattern-specific storage central to the design.
- Design a metrics monitoring system like DatadogTime-series ingestion, retention, cardinality, and range aggregation require evaluating storage models beyond a generic SQL-or-NoSQL label.
Questions people ask
What is the main difference between SQL and NoSQL?
In this comparison, SQL usually refers to relational databases with tables, relationships, and SQL queries. NoSQL refers to several nonrelational models, including key-value, document, wide-column, and graph databases. Transaction support, consistency, and scalability vary by implementation rather than following a strict category boundary.
How do I choose a database in a system design interview?
Write down access patterns, correctness rules, estimated load, and failure requirements first. Then choose a model, keys, indexes, and transaction boundaries that satisfy them. Explain the main limitation of your choice and what evidence would justify changing it.
Is NoSQL faster and more scalable than SQL?
Not inherently: performance depends on the query, indexes, data layout, hardware, and coordination required. Some NoSQL databases make partitioned workloads straightforward, while distributed SQL databases also support horizontal scaling. Compare specific operations under the guarantees you need, including hot-key behavior and cross-partition work.