SQL vs NoSQL Databases: Differences and How to Choose

The answer to lead with: choose a SQL (relational) database when your data has clear relationships, you need to query it in ways you can’t fully predict, and correctness rules (unique emails, no negative stock, money that adds up) must hold across several rows at once. Choose a NoSQL database when you know your access patterns up front, they’re mostly simple lookups by key, and you need a data model or scaling behaviour that a relational database makes awkward: very high write volume spread across many machines, flexible document shapes, or graph traversals.

Interviewers push back on two lazy answers. “NoSQL scales, SQL doesn’t” ignores read replicas, partitioning and distributed SQL systems. “NoSQL has no schema” is misleading: the schema still exists, it just lives in your application code instead of the database. The useful answer ties the choice to the workload in front of you.

How they differ

The last row is the heart of it. With SQL you model the domain and trust the query planner to answer new questions later. With most NoSQL stores, especially DynamoDB and Cassandra, you model the queries, and a question you didn’t plan for may need a new index, a copy of the data or a full scan.

The four NoSQL families

• Document (MongoDB, Couchbase): JSON-like documents with nested arrays and objects. Good for aggregates that are read and written together, like a product with its variants.
• Key-value (Redis, DynamoDB in its simplest use): get and put by key, extremely fast. Caches, sessions, counters, rate limits.
• Wide-column (Cassandra, ScyllaDB, Bigtable): rows partitioned by key and sorted within a partition. Built for heavy write throughput and time-ordered data such as events or metrics.
• Graph (Neo4j): nodes and relationships as first-class data. Good when the questions are about paths: friends of friends, fraud rings, recommendations.

Saying “NoSQL” in an interview without naming a family is a small red flag. Saying which family and why is a strong signal.

What the relational database does for you

Here’s a small order system in SQLite, which ships with Node 22 as the experimental node:sqlite module. It’s not Postgres, but the relational guarantees shown are the same ideas.

import { DatabaseSync } from 'node:sqlite';
const db = new DatabaseSync(':memory:');
db.exec(`
  PRAGMA foreign_keys = ON;
  CREATE TABLE customers (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE);
  CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    total_cents INTEGER NOT NULL CHECK (total_cents >= 0)
  );
  CREATE TABLE stock (sku TEXT PRIMARY KEY, qty INTEGER NOT NULL CHECK (qty >= 0));
  INSERT INTO customers VALUES (1, 'ada@example.com'), (2, 'grace@example.com');
  INSERT INTO orders VALUES (100, 1, 2500), (101, 1, 1200), (102, 2, 900);
  INSERT INTO stock VALUES ('book', 1);
`);

console.log(db.prepare(`
  SELECT c.email, COUNT(o.id) AS orders, SUM(o.total_cents) AS spent
  FROM customers c JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id ORDER BY spent DESC`).all().map((r) => ({ ...r })));

[
  { email: 'ada@example.com', orders: 2, spent: 3700 },
  { email: 'grace@example.com', orders: 1, spent: 900 }
]

That query wasn’t planned when the tables were designed. A join and a GROUP BY answered it. Ad hoc reporting like this is where relational databases are hard to beat.

Now try to break the data:

db.exec(`INSERT INTO orders VALUES (103, 99, 500)`);
db.exec(`INSERT INTO customers VALUES (3, 'ada@example.com')`);

FOREIGN KEY constraint failed
UNIQUE constraint failed: customers.email

The database refused an order for a customer who doesn’t exist and a duplicate email. (SQLite enforces foreign keys only after PRAGMA foreign_keys = ON; Postgres always does.) Finally, a transaction that must change two tables together:

function placeOrder(id, customerId, sku) {
  db.exec('BEGIN');
  try {
    db.prepare('INSERT INTO orders VALUES (?, ?, ?)').run(id, customerId, 1500);
    db.prepare('UPDATE stock SET qty = qty - 1 WHERE sku = ?').run(sku);
    db.exec('COMMIT');
    return 'placed';
  } catch (e) {
    db.exec('ROLLBACK');
    return 'rolled back: ' + e.message;
  }
}
console.log(placeOrder(104, 2, 'book'));
console.log(placeOrder(105, 2, 'book'));
console.log({ ...db.prepare('SELECT COUNT(*) AS n FROM orders').get() }, { ...db.prepare('SELECT qty FROM stock').get() });

placed
rolled back: CHECK constraint failed: qty >= 0
{ n: 4 } { qty: 0 }

The second order would have pushed stock to -1, so the CHECK failed and the rollback also undid that order’s insert. Four orders exist and stock is 0: no order without stock, no stock change without an order. Getting the same guarantee across two items in a store without multi-item transactions means designing it yourself, with conditional writes, idempotency and compensation.

The same data as documents

A document store would usually embed each customer’s orders. This is a plain JavaScript stand-in for a document collection, not a real database, but it shows the trade:

customers.set('c1', {
  _id: 'c1', email: 'ada@example.com',
  orders: [{ id: 100, totalCents: 2500 }, { id: 101, totalCents: 1200 }],
});
customers.set('c2', { _id: 'c2', email: 'grace@example.com', orders: [{ id: 102, totalCents: 900 }], loyalty: { tier: 'gold' } });

ada@example.com 2 3700
[ '_id,email,orders', '_id,email,orders,loyalty' ]
{ scanned: 2, big: [ 100, 101 ] }

The planned access pattern, “show this customer with their order history”, became a single lookup with no join. The second document gained a loyalty field without a migration, which is the flexibility people mean. But the unplanned question, “which orders are over $10, across all customers?”, had to look at every document. In a real store you’d add an index on the nested field, keep a separate orders collection, or accept the scan. Embedding also has limits: an order list that grows without bound will hit document size limits (16 MB in MongoDB) and makes every customer read heavier. The embedding versus referencing guide goes through that decision.

The lines have blurred

Modern systems don’t split as cleanly as the table suggests. PostgreSQL’s jsonb columns store and index documents. MongoDB has supported multi-document ACID transactions since version 4.0, and DynamoDB offers transactional writes across multiple items, though both work best when you rarely need them. Distributed SQL databases (CockroachDB, Google Spanner, YugabyteDB) partition data across nodes while keeping SQL and transactions. So a strong interview answer often says: “I’d start with Postgres, and add a specialised store when a specific access pattern needs it.” Using several stores, each for what it’s good at, is called polyglot persistence.

When you do pick NoSQL, name the cost you’re accepting: denormalised data must be updated in several places, which usually means eventual consistency between copies, and new query patterns may require backfilling a new index or table.

System design exercise: pick the stores

You’re designing a ride-hailing app. Choose storage for: (a) riders, drivers, payments and trip records; (b) driver locations, updated every 4 seconds by 200,000 active drivers; (c) a cache of “nearby drivers” results; (d) analytics on millions of past trips. Justify each.

Reasoned answer

(a) A relational database such as Postgres. Payments and trips need transactions and constraints (a trip has one rider and one driver, a payment belongs to a trip), and support teams run unplanned queries. Scale reads with replicas and partition by region or city if needed. (b) This is about 50,000 writes per second of short-lived data that’s always looked up by driver id or area. A key-value or wide-column store fits: Redis with geospatial commands for the live view, or Cassandra if you must also keep a location history. Losing a few seconds of location on failure is acceptable, which is exactly the kind of relaxed requirement that makes NoSQL a good trade. (c) Redis with a short TTL. (d) Neither main database: export trips to a columnar warehouse (BigQuery, Snowflake, Redshift) so heavy scans don’t slow the transactional system. Saying this kept the choice per workload, rather than one database for everything, is the point the interviewer is looking for.

Keep reading

• System design topic hub
• System design MCQ practice
• System design partitioning
• DynamoDB keys and access patterns
• Martin Fowler: Polyglot Persistence