Introduction to Databases

A database is a program that lays records out on disk in a structure it can find again by key or index — so a lookup touches a few blocks instead of scanning everything — and the SQL-versus-NoSQL split is fundamentally a choice about where that structure is enforced: the relational engine validates every row against a declared schema when you write (schema-on-write), while most NoSQL stores accept whatever shape you send and leave your application code to make sense of it when you read (schema-on-read). Everything else — query language, scaling story, ACID guarantees — follows from that one decision.

The two families

A Database Management System (DBMS) is the software layer between your application and the bytes on disk; it handles concurrent access, durability, and querying so you never touch raw files. Two families dominate:

• Relational (SQL): data lives in tables of rows and columns with a declared schema and typed columns; relationships between tables are first-class. You query with SQL. Examples: PostgreSQL, MySQL, SQL Server, Oracle.
• Non-relational (NoSQL): an umbrella for four distinct models — document (MongoDB), key-value (Redis, DynamoDB), wide-column (Cassandra), and graph (Neo4j). Each trades the fixed relational shape for a data model that fits one access pattern especially well.

"NoSQL" is not one thing. A Redis key-value cache and a Neo4j graph engine have almost nothing in common except that neither is a relational table — so blanket statements like "NoSQL scales better" or "NoSQL has no schema" are usually wrong for at least one member of the family.

The five dimensions, corrected

These are the axes engineers actually compare on. Two claims you will see repeated in older material are wrong today and are fixed below.

Correction 1 (Schema): "Altering the schema means modifying the whole database and going offline" is false on current engines. PostgreSQL and MySQL 8+ support online DDL: adding a nullable column, or a column with a default, is a metadata-only change that completes in milliseconds without rebuilding the table. One precision the folklore version drops: the ALTER still needs a brief exclusive lock (ACCESS EXCLUSIVE in PostgreSQL; a short metadata lock in MySQL's INSTANT DDL) — so if a long-running transaction already holds the table, the ALTER queues behind it and every query issued after the ALTER queues behind the ALTER. That queue-behind-a-long-transaction pile-up is the classic online-DDL production incident. Mitigation: run DDL with a lock_timeout and retry, or schedule it off-peak.

Correction 2 (Querying): older summaries call the NoSQL query language UnQL (Unstructured Query Language). UnQL was a short-lived 2011 proposal that never shipped in any mainstream database — it is not a standard and you will never meet it in production. Each NoSQL product has its own API.

Worked example: one car catalog, two engines

Take three products and trace what actually happens on disk and on the wire.

Relational (PostgreSQL) — schema on write

CREATE TABLE cars (
  id     BIGINT PRIMARY KEY,
  make   TEXT NOT NULL,
  model  TEXT NOT NULL,
  year   INT  NOT NULL
);

INSERT INTO cars VALUES (1, 'Toyota', 'Corolla', 2020);
INSERT INTO cars VALUES (2, 'Honda',  'Civic',   2019);

-- Marketing now wants a colour. Naive fear: "this rebuilds the table + downtime."
-- Reality on PG / MySQL 8:
ALTER TABLE cars ADD COLUMN color TEXT;   -- metadata-only, ~1 ms, no table rewrite; still takes a BRIEF exclusive lock

INSERT INTO cars VALUES (3, 'Tesla', 'Model 3', 2023, 'Red');
SELECT make, model FROM cars WHERE year >= 2020;  -- engine uses the year to filter; returns rows 1 and 3

Trace of the write path for row 3:

1. Parser + planner validate the statement against the catalog: 5 values, 5 columns, types match. If year were 'twenty-twenty-three' the write is rejected here — the bad data never lands.
2. Row is written to the WAL (durability) then to a data page.
3. Because color was added as a nullable column, rows 1 and 2 were never touched — the engine reads their missing color as NULL. That is why the ALTER was instant.

Document (MongoDB) — schema on read

db.cars.insertOne({ _id: 1, make: "Toyota", model: "Corolla", year: 2020 })
db.cars.insertOne({ _id: 2, make: "Honda",  model: "Civic",   year: 2019 })

// Adding colour needs no migration — you just include the field:
db.cars.insertOne({ _id: 3, make: "Tesla", model: "Model 3", year: 2023, color: "Red" })

db.cars.find({ year: { $gte: 2020 } }, { make: 1, model: 1 })

Trace of the read path: the collection now holds documents of two different shapes — rows 1 and 2 have no color key at all. Nothing rejected the mismatch on write; instead your application (or a $exists check) must handle "the field may be absent" at every read. The flexibility on write became a branch in your read code. That is the whole trade in one sentence.

When to use which — how a senior engineer decides

The decision is not "which is better" but "where does the cost of structure belong for this workload."

Choose relational (SQL) when:

• The data is highly connected and you query across entities — orders join customers join line-items. Relational joins do this natively; the alternative is re-implementing joins in application code.
• Multi-row correctness matters: money transfers, inventory decrements, bookings. You want the engine to guarantee atomicity, not hope your code does.
• Access patterns are not fully known yet. SQL lets you ask new questions with a new query; you do not have to have modeled them in advance.

Prefer a NoSQL model when:

• Document (MongoDB): each request reads/writes one self-contained aggregate (a user profile, a product page) and shapes vary. You gain a natural object mapping and no join; you pay by duplicating data and losing engine-enforced integrity across documents.
• Key-value (Redis, DynamoDB): the only access is "get/put by a known key" and you need single-digit-millisecond latency at huge scale. You gain raw speed and effortless horizontal scale; you give up ad-hoc querying — there is no WHERE colour = 'red' without a secondary structure.
• Wide-column (Cassandra): write-heavy, always-available, geo-distributed workloads (event logs, time series). You gain linear write scaling and no single point of failure; you pay by designing tables per query up front and living with eventual consistency.
• Graph (Neo4j): the value is the relationships — fraud rings, social graphs, recommendations with deep multi-hop traversals that would be dozens of self-joins in SQL.

Crisp rule: choose relational when your data has relationships and you need transactional correctness with flexible queries; prefer a specific NoSQL model when one access pattern dominates and you will trade query flexibility and cross-record integrity for scale, latency, or a natural fit to that pattern. Many mature systems run both — Postgres as the system of record, Redis as the cache, Elasticsearch for search.

Pitfalls

• "NoSQL means no schema." There is always a schema; it just moved into your code and into the mix of old and new document versions on disk. Six months in you have five field variants and every read branches on which one it is. Flexibility on write is deferred cost on read.
• Assuming every ALTER is instant. Adding a nullable column is metadata-only, but changing a column's type, adding a NOT NULL without a default on old MySQL, or building a large index can still rewrite the table or take locks. Always check the specific operation against your engine's version and use tools like pt-online-schema-change / gh-ost for the heavy ones.
• Picking NoSQL to "avoid joins" — then rebuilding joins in the app. If your read genuinely needs data from three entities, a document store makes you fetch three collections and stitch them in code, without the optimizer, without transactional consistency. That is slower and buggier than one SQL join.
• Believing horizontal scale is free. Scaling out hinges on the partition/shard key. A poor key creates hot partitions (one node melts while others idle) and makes any query that spans keys expensive or impossible. The hard part is the key design, not adding nodes.
• Treating "NoSQL sacrifices ACID" as still true. MongoDB, DynamoDB, and Spanner all offer transactions now. Verify the actual consistency and transaction guarantees of the product and configuration you deploy, not the 2012 reputation of the category.

Takeaways

• SQL vs NoSQL is one decision — schema-on-write (engine enforces structure) vs schema-on-read (your code does) — and query language, scaling, and ACID all follow from it.
• "NoSQL" is four unrelated models (document, key-value, wide-column, graph); reason about the specific one, never the umbrella.
• Modern engines do online DDL, and modern NoSQL products do transactions — the old "schema change = downtime" and "NoSQL = no ACID" claims are dated.
• Decide by workload: relationships + transactional correctness + evolving queries → relational; one dominant access pattern where you'll trade query flexibility for scale or latency → the matching NoSQL model.

Interview drill ladder

• L1: What is the difference between schema-on-write and schema-on-read?
  Answer: Schema-on-write validates structure at insert time (relational: the row must match the table definition), so readers can trust every record's shape. Schema-on-read stores whatever arrives and interprets structure at query time (document stores), so writers move fast but every reader must handle missing/renamed fields. The trap: "schemaless" doesn't mean no schema — it means the schema lives, unversioned, in your application code.
• L2: Can a NoSQL store be ACID?
  Answer: Yes — the blanket "NoSQL = BASE" claim is dated. MongoDB has multi-document ACID transactions (4.0+), DynamoDB has TransactWriteItems, and Spanner is globally-distributed ACID. The honest trade-off today is not "ACID vs scale" but how large/contended a transaction each system tolerates before latency and abort rates bite.
• L3: When is a document store the wrong choice despite flexible schema?
  Answer: When your reads genuinely span multiple entities: you end up fetching three collections and stitching them in application code — rebuilding joins by hand, without the optimizer and without transactional consistency (the pitfall above). Also when you need ad-hoc cross-entity analytics or enforceable invariants across records; both are what the relational model and its constraints exist for.

Re-authored and deepened for this guide. Sources: Designing Data-Intensive Applications (Martin Kleppmann, ch. 2, on schema-on-write vs schema-on-read); PostgreSQL documentation on ALTER TABLE and MySQL 8.0 documentation on Online DDL (ALGORITHM=INSTANT); MongoDB documentation on multi-document ACID transactions; Amazon DynamoDB and Google Cloud Spanner transaction docs. The "UnQL" term traces to the abandoned 2011 UnQL specification and is corrected here as non-standard.