What the SQL vs NoSQL Question Really Asks

When an interviewer asks 'SQL or NoSQL?', they are not asking which is better. They are asking whether you understand the trade-offs between a strict relational model with ACID guarantees and a flexible model that scales horizontally.

The wrong answer is 'NoSQL because it scales' or 'SQL because it is reliable'. Both statements are wrong. The right answer always sounds like:

'Given the access patterns (X), the consistency requirements (Y), and the expected scale (Z), I would choose [database] because [specific reason]. The main trade-off I am accepting is [specific trade-off].'

This lesson gives you the vocabulary and the framework to produce that answer for any workload.

SQL: The Relational Model

Core Idea

A SQL database stores data in tables with a fixed schema. Rows in different tables are connected through foreign keys and combined at query time using joins. The schema is enforced by the database, so every row in users must have the columns the schema declares.

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    total_cents INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Get all orders for a user with email
SELECT o.id, o.total_cents, o.status
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.email = '[email protected]';

ACID: The Four Guarantees

SQL databases provide ACID transactions. These are the guarantees that make your money safe.

A classic ACID example is a bank transfer:

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If the server crashes between the two UPDATE statements, atomicity guarantees neither happens. Money does not disappear.

Strengths of SQL

• Strong consistency by default. The database is the source of truth.
• Joins let you model relationships once and query them many ways. No need to denormalize.
• Mature query optimizer. The database figures out the best execution plan; you just declare the result you want.
• Schema enforcement catches application bugs at the storage layer.
• 30+ years of tooling - migrations, ORMs, BI tools, observability.

Weaknesses of SQL

• Vertical scaling first. Adding read replicas is straightforward; sharding writes is painful and usually application-controlled.
• Schema migrations on huge tables can lock writes (mitigated by tools like pt-online-schema-change or PostgreSQL's CONCURRENTLY operations, but still operationally heavy).
• Joins across shards do not work. Once you shard a SQL database, you give up the convenience of JOIN.

Popular SQL Engines

• PostgreSQL: extensible, JSON support, strong consistency, the default for most new projects.
• MySQL / MariaDB: extremely popular, great read scaling, weaker default isolation.
• CockroachDB / Spanner / YugabyteDB: 'NewSQL' - SQL with horizontal scaling and global consistency.
• SQLite: embedded, single-file, perfect for edge and local-first apps.

NoSQL: Four Families

'NoSQL' is not one thing. It is an umbrella over four very different data models. You must know all four because the interviewer might ask you to pick between them.

1. Key-Value Stores

The simplest model: a giant distributed hash map. You store a value (any blob) under a key, and you can GET, SET, or DELETE by key. There are no queries by value.

// Redis client
await redis.set('session:abc123', JSON.stringify({ userId: 42, exp: 1700000000 }), 'EX', 3600);
const session = JSON.parse(await redis.get('session:abc123'));

• Engines: Redis, Memcached, DynamoDB (in pure KV mode), Riak.
• Best for: caching, session storage, rate-limit counters, leaderboards.
• Weakness: cannot query by anything except the key.

2. Document Stores

Store self-contained JSON-like documents. Each document has its own structure; the database does not enforce a schema. You can index any field and query by it.

{
  "_id": "user_42",
  "name": "Alice",
  "email": "[email protected]",
  "addresses": [
    { "label": "home", "city": "Paris" },
    { "label": "work", "city": "Lyon" }
  ],
  "prefs": { "theme": "dark", "lang": "en" }
}

• Engines: MongoDB, Couchbase, Firestore, DynamoDB (document mode).
• Best for: content with variable shape (user profiles with optional fields, product catalogs, CMS content).
• Weakness: joins are weak or absent. Heavy reliance on denormalization, which makes updates hard.

3. Wide-Column Stores

Picture a table where each row can have a different set of columns, optimized for massive write throughput and range scans by primary key. Internally based on the LSM-tree storage engine.

---------- Cassandra row example ----------
row key: user_42
  column: profile.name      = 'Alice'
  column: profile.email     = '[email protected]'
  column: events.2026-04-26 = '{"type":"login"}'
  column: events.2026-04-27 = '{"type":"purchase"}'

• Engines: Cassandra, ScyllaDB, HBase, Bigtable.
• Best for: time-series data, event logs, write-heavy workloads at petabyte scale.
• Weakness: query patterns must be designed up front (you model around the queries, not the data). Ad-hoc queries are painful.

4. Graph Databases

First-class support for nodes and edges. Queries traverse relationships efficiently (find friends-of-friends in 2 hops).

---------- Graph snippet ----------
(Alice:User) -[FOLLOWS]-> (Bob:User) -[FOLLOWS]-> (Carol:User)
(Alice:User) -[LIKES]-> (Post:p123) <-[POSTED]- (Bob:User)

• Engines: Neo4j, Amazon Neptune, ArangoDB, JanusGraph.
• Best for: social networks, fraud detection, recommendation engines, knowledge graphs.
• Weakness: harder to scale horizontally; smaller ecosystem; many problems do not actually need graph traversal.

BASE: The NoSQL Counterpart to ACID

Most distributed NoSQL systems offer BASE semantics instead of ACID:

• Basically Available - the system always responds, even during partial failures.
• Soft state - the state may change without input as replicas converge.
• Eventual consistency - all replicas converge to the same value, given enough time without new writes.

BASE is a deliberate trade: you give up immediate consistency in exchange for availability and horizontal scale. See the CAP Theorem lesson for why this trade-off is forced on distributed systems.

SQL vs NoSQL: The Decision Matrix

A Decision Framework

Ask these questions in order. Stop at the first 'yes' that strongly signals one side.

1. Do you need multi-row ACID transactions across related entities? -> SQL. (Payments, inventory reservations, double-entry ledgers.)
2. Are your access patterns mostly key lookups at very high QPS? -> Key-value store. (Sessions, rate limiters, caches.)
3. Is your data shape highly variable or evolving rapidly? -> Document store. (User-generated content, CMS, product catalogs with diverse attributes.)
4. Are you ingesting massive write volume that one machine cannot handle? -> Wide-column. (IoT telemetry, event logs, time series, audit trails.)
5. Are your queries primarily about traversing relationships? -> Graph. (Social graphs, recommendation, fraud rings.)
6. None of the above strongly applies? -> Default to PostgreSQL. It is rarely the wrong call for a new system.

Polyglot Persistence: Use Both

Real systems usually combine multiple databases. Each store is chosen for the workload it handles best.

---------- Typical polyglot stack ----------
PostgreSQL      -> users, orders, payments       (ACID, source of truth)
Redis           -> sessions, rate limits, cache  (low-latency KV)
Elasticsearch   -> full-text search index        (inverted index)
S3 + DynamoDB   -> uploaded files + metadata     (blob + KV)
Kafka           -> event log between services    (durable queue)