SQL vs NoSQL: How to Choose in a System Design Interview

An interviewer asks which database you would use for an online store. “SQL for consistency, NoSQL for scale” sounds decisive, but it skips the work. The checkout, catalog, and browsing history have different queries and different failure costs. A database choice becomes defensible only when it is tied to those requirements.

This guide uses one store to compare the models, expose the common myths, and build a decision process you can use in an HLD interview.

SQL vs NoSQL: the comparison that matters

SQL is a query language; people often use “SQL database” to mean a relational database. NoSQL covers several models, including document, key-value, wide-column, and graph databases. It is not one storage architecture or one consistency policy.

These are modeling tendencies, not guarantees about every product. A relational system can store documents, and a NoSQL engine can support transactions. The comparison becomes useful when you replace the category names with the candidate services and their documented behavior.

Start with the store's real questions

Before choosing a database, write the requests the product actually needs to answer.

“Millions of users” is not yet a storage requirement. Estimate peak operations per second, record sizes, working-set size, growth, and the distribution of hot keys. Ten million mostly inactive accounts can produce less pressure than one globally popular item.

Model checkout with relational data

Suppose an order has multiple line items, each referring to a product. A relational schema keeps these relationships explicit and lets us enforce foreign keys and uniqueness where appropriate.

The order item stores the agreed unit price, even though the product also has a current price. That duplication is intentional: historical orders must not change when tomorrow’s catalog price changes. Normalization is a tool, not a demand to erase every snapshot.

For recent-order retrieval, start with the query and choose the index to match it:

CREATE INDEX orders_customer_created_id
ON orders (customer_id, created_at DESC, id DESC);
 
SELECT id, status, created_at
FROM orders
WHERE customer_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 20;

This is a keyset-pagination example for subsequent pages. The first page omits the cursor predicate. The ID breaks timestamp ties; the cursor must carry both values. An index that matches the query does not eliminate the need to inspect the execution plan and measure representative data. PostgreSQL explains the mechanics and costs in its indexes documentation.

For checkout correctness, a transaction is the container for the rule, not the rule itself. A read followed by an unconditional inventory write can still be wrong under concurrency. Use a suitable conditional update or lock, check the result, and roll back the order if stock reservation fails.

Model a catalog as a document

Products from different categories may have different attributes. A camera has a sensor size; a desk has dimensions. A bounded product document can keep the common read together:

{
  "product_id": "camera-42",
  "title": "Trail Camera",
  "category_id": "cameras",
  "price_cents": 24900,
  "attributes": {
    "sensor": "APS-C",
    "weather_sealed": true
  },
  "schema_version": 2
}

The benefits depend on how that document is used. Fetching one product by ID is convenient. Finding every product whose category recently changed may need another index or a different model. A flexible document does not remove validation, migrations, or the need to handle old record versions.

It also does not force a new database. PostgreSQL supports jsonb, JSON operators, and indexing, so a relational product row with a JSON attributes field is a possible starting point. Its documentation notes that updating a large JSON document still locks its whole row, which matters if unrelated fields receive concurrent writes. See PostgreSQL JSON types.

Design keys for the busiest access pattern

A key-value design makes the key part of the architecture. For recent browsing activity, one possible logical layout is:

partition key: USER#u42#2026-09
sort key:      VIEW#2026-09-12T10:04:00Z#event-918
payload:       product_id, source, schema_version

Time bucketing bounds the size of one logical group, but fetching activity across months now requires multiple groups. A stable event ID helps distinguish events with equal timestamps and can support duplicate detection if the write operation enforces it.

Hashing a product ID does not distribute writes within that one hot product. It sends them consistently to the same partition owner. If you add write shards, explain how reads find and merge those shards. DynamoDB’s partition-key design guide emphasizes distributing activity rather than merely creating many distinct keys.

Separate transactions from read consistency

Two questions often get collapsed into one:

1. Can several related changes commit together?
2. Will this particular read observe the latest committed state?

They are related, but they are not interchangeable. DynamoDB supports transactions, and it also exposes different read-consistency options. In the documented regional read model, tables and local secondary indexes support strongly consistent reads; global secondary indexes provide eventual reads. Check the chosen operation and index, not just the database name. See DynamoDB transactions and read consistency.

Similarly, choosing a relational database does not make an asynchronous read replica current. A “view my new order” request needs a route that provides the promised read-after-write behavior. Depending on the system, that may mean reading the primary, carrying a consistency token, or displaying the response from the successful write.

Add a second database only with a clear owner

You may eventually want a search index for catalog discovery and a separate store for activity. The danger is creating multiple independently writable versions of the same fact.

Here the product store owns the facts. Search and cache are derived views. The propagation path needs retries, ordering rules, deletion handling, and a way to rebuild from durable data. The browsing API must tolerate the declared indexing delay.

For our hypothetical store, a reasonable initial decision is one relational database for orders and the catalog, with structured columns plus JSON attributes where helpful. Add specialized storage when a concrete access pattern or operational limit justifies it. A team with different workload evidence could reasonably choose a different initial model.

Frequently asked questions

Try the decision yourself

Take the five store queries from this article. For each, write the key or index, the consistency requirement, and what happens when the request races with a write. Then identify the first query that would be awkward in your chosen model.

Continue with SQL vs NoSQL visually, database transactions, and sharding. Once ownership is clear, read caching strategies to see how a faster read path changes the freshness guarantee.