SQL vs NoSQL and database types

A SQL (relational) database keeps data in tables with a declared schema, answers any question by joining them, and wraps multi-row changes in ACID transactions. “NoSQL” is not one rival design but an umbrella over several data models (key-value, document, wide-column and graph, plus time-series, search and vector specialists), most of which trade query flexibility for spreading data across many machines by key. Choosing is mostly about how you will read and write the data.

Picture it first: a hospital records room

Picture a big hospital’s records room. It holds facts about the same patients in several shapes, because different people ask different questions.

• Registers of patients, visits and prescriptions, cross-referenced by patient number. A clerk can answer almost any question by flipping between books, but every correction goes through one master set.
• Cloakroom tokens. Hand over token 417, get bag 417. Instant at any number of counters, but nobody can ask for “every red bag”.
• Case-file folders. One patient, one folder: perfect for that patient, but “everyone prescribed drug X” means opening every folder.
• Ward day-sheets. One per ward per day, in time order. Ideal for “what happened in ward 3 last night?”, useless for anything else.
• A referral map on the wall, doctors as pins and referrals as strings. “Two referrals away from Dr Rao” is a glance.

Map it back: the registers are relational, the tokens key-value, the folders document, the day-sheets wide-column and the wall map a graph. Add the drug handbook’s index (search), the ICU monitor strips (time-series) and the basement film archive (object storage). A well-run hospital keeps one authoritative set of registers and treats the rest as copies. So do most good designs.

What “relational” actually means

In June 1970, Edgar F. Codd of IBM published “A Relational Model of Data for Large Shared Data Banks”. His target was data independence. Databases of the day were trees (IBM’s IMS) or pointer networks, and programs broke whenever storage changed because they depended on the stored order of records, on which indexes existed, and on the access path through the tree.

Codd’s fix was to store everything as relations (tables) and let users say what they want, not how to fetch it. Five ideas follow, and together they are what “SQL database” means today:

1. Schema-on-write. Columns, types and constraints are declared up front and checked on every write.
2. Relationships as values. An order row holds a user_id, not a pointer, and each fact is stored once (normalization).
3. Joins at read time, so a question nobody planned is still just a query.
4. Declarative SQL. The optimizer picks indexes and join order; add an index tomorrow and the query text does not change.
5. ACID transactions across rows, with an isolation level controlling concurrency (see ACID transactions).

A question nobody planned for, answered with no schema change (PostgreSQL):

-- Recent orders placed by people Asha (u1) follows
SELECT u.name, o.id, o.total_inr, o.placed_at
FROM follows f
JOIN users  u ON u.id = f.followee_id
JOIN orders o ON o.user_id = f.followee_id
WHERE f.follower_id = 'u1'
ORDER BY o.placed_at DESC
LIMIT 20;

What you pay: schema design and careful migrations, join work at read time, and usually one primary that takes every write, so write capacity is one machine until you shard.

What “NoSQL” lumps together

By the mid-2000s the largest web companies had more data and writes than any single machine could hold, on fleets where some server failed every day. Two papers shaped the response:

• Bigtable (Google, OSDI 2006): a sparse, sorted map from (row key, column, timestamp) to bytes, split into row ranges across thousands of servers. Each row is atomic, but there are no general cross-row transactions and, by design, no full relational model.
• Dynamo (Amazon, SOSP 2007): many Amazon services only read and wrote by primary key, so Dynamo offered get and put on small blobs (usually under 1 MB), hash-partitioned and always writable, with no isolation and single-key updates only.

The name followed at a meetup in San Francisco on 11 June 2009, organised by Johan Oskarsson, with talks on Voldemort, Cassandra, Dynomite, HBase, Hypertable, CouchDB and MongoDB. Several were open-source children of those two papers. Martin Fowler later observed there is no definition, only shared traits: non-relational, open source, built for clusters, no fixed schema. Marketing soon softened it to “Not only SQL”.

So treat NoSQL as a family of data models sharing one bargain: give up some mix of joins, ad hoc queries, enforced schemas and multi-record transactions; get partitioning by key across many machines, predictable latency for the access pattern you designed, and flexible record shapes.

One dataset, four shapes

Take two users, Asha (u1) and Ravi (u2), two orders Asha placed, and one follow: Asha follows Ravi. Here it is in the four classic models.

Relational stores three tables and lets the query stitch them together: “who follows Ravi?” and “what did Asha order?” are each an index lookup plus a join.

Document puts Asha’s whole page in one document: one read, no join. But it is shaped around Asha’s view, so “who follows Ravi?” must search every follows array or rely on a secondary index.

Wide-column starts from the queries, as the Cassandra docs advise, and builds one table per question. A partition key decides where rows live (all of u1’s orders together) and a clustering key sorts them inside the partition. With no joins, “who follows Ravi?” is a second table you write on every follow:

-- Cassandra CQL: the table is shaped by the question "orders for a user, newest first"
CREATE TABLE orders_by_user (
  user_id   text,
  placed_at timestamp,
  order_id  text,
  total_inr int,
  PRIMARY KEY ((user_id), placed_at, order_id)
) WITH CLUSTERING ORDER BY (placed_at DESC, order_id ASC);

Graph stores FOLLOWS and PLACED as real relationships, so multi-hop questions are short walks along stored links instead of chains of self-joins.

The pattern: the more a model is shaped around expected reads, the faster those reads are and the more an unexpected one costs. Data modeling and denormalization designs each shape from requirements.

The families, one by one

Key-value

A key and an opaque value: get, put and delete by exact key, hash-partitioned so capacity grows with nodes. Redis adds server-side structures (hashes, sorted sets, geo sets, streams) with atomic per-key operations. Ideal for sessions, carts, caches, counters and idempotency keys; wrong once you must search records by their fields, since there are no secondary indexes and joins move into your code. Examples: Redis, Memcached, DynamoDB used by key.

Document

Nested JSON-like documents whose fields may differ, queried by any field through secondary indexes and aggregation pipelines, and sharded by a shard key. Single-document writes are atomic; MongoDB added multi-document ACID transactions in 4.0 (replica sets) and 4.2 (sharded clusters). Great for aggregates read whole, like a product with category-specific attributes; poor for shared, many-to-many data. Examples: MongoDB, Couchbase, Firestore, and PostgreSQL’s jsonb.

Wide-column

Bigtable’s sorted map, refined by Cassandra into partitions of rows sorted by clustering columns. Every read supplies the partition key, then reads a range in order. Writes are cheap and multi-data-centre replication is built in, but there are no joins or referential integrity, only per-partition atomicity and lightweight compare-and-set transactions. Ideal for huge, append-heavy, time-ordered data per entity: chat messages, order history, device readings. Examples: Cassandra, ScyllaDB, HBase, Bigtable.

Graph

Nodes with labels and properties; relationships with a type, a direction and properties. Neo4j stores relationships next to the nodes, so traversals are pointer hops rather than joins: ideal for friends-of-friends, fraud rings sharing devices and UPI handles, and permission trees. It mostly scales up, with clusters for read capacity, since splitting a dense graph turns hops into network calls. Shallow hierarchies usually stay relational via recursive queries or SQL:2023’s SQL/PGQ. Examples: Neo4j, Amazon Neptune.

Time-series

Points of (timestamp, value) per labelled series: appended, queried by time window, aggregated, and partitioned by time so old data drops cheaply. TimescaleDB is a PostgreSQL extension with full SQL, while Prometheus’s local store is a single node that is neither clustered nor replicated. Examples: TimescaleDB, InfluxDB, Prometheus.

Search engines

An inverted index from terms to documents gives ranked full-text search, typo tolerance, synonyms and facets. Writes are near-real-time (searchable after the next refresh) and there are no cross-document transactions, so feed it from your database and never make it the source of truth. Examples: Elasticsearch, OpenSearch, Solr. PostgreSQL’s full-text search carries you a long way first.

Vector

Embeddings plus metadata, queried by approximate nearest neighbour (ANN) with metadata filters. Within a year of ChatGPT’s launch many major relational engines had added vector indexes; pgvector gives PostgreSQL exact search by default plus HNSW and IVFFlat indexes that trade some recall for speed. Stonebraker and Pavlo call a vector database a document store with a special index. Examples: pgvector, Pinecone, Milvus, Weaviate (vector databases).

Distributed SQL (NewSQL): the bridge

Tables and SQL on a NoSQL-style core: the database splits data into key ranges itself and replicates each range by consensus (Paxos or Raft), so writes scale out while transactions stay ACID across ranges. Google’s Spanner (2012) is the landmark. You pay consensus latency on every commit, worst across regions, and a younger ecosystem. Examples: Spanner, CockroachDB, TiDB (MySQL-compatible), YugabyteDB (PostgreSQL-compatible); internals in database architectures.

Object storage: not a database, but where blobs go

Buckets of keys pointing to immutable objects: put, get and list by prefix, with no queries and no partial updates, at nearly unlimited scale. Keep the photo, video or invoice PDF there, keep a metadata row (key, size, owner) in the database, and serve the bytes through a CDN. Don’t stuff large blobs into rows; a DynamoDB item, for example, is capped at 400 KB. See blob storage.

The six axes that actually decide

“SQL or NoSQL?” is the wrong question. These six are the right ones, roughly in order of how often they decide.

1. Data shape. Many entities pointing at each other want joins and foreign keys; self-contained records read whole fit documents or key-value items.

2. Query flexibility. Relational answers questions you haven’t thought of yet; key-value and wide-column stores answer the ones built into their keys. That is Codd’s access-path dependence, accepted on purpose in exchange for predictable speed.

3. Scaling model. The most misunderstood axis, so here it is drawn.

Relational databases usually run the left model: replicas scale reads, one machine caps writes. DynamoDB, Cassandra and distributed SQL are natively the right model: writes scale almost linearly, but only queries carrying the partition key stay cheap. Sharding middleware such as Vitess or Citus moves a relational database right, under the same rule.

4. Transactions and consistency. Moving ₹450 from a wallet to an order, or holding an IRCTC tatkal berth while payment runs, needs multi-row atomicity, which relational and distributed SQL give you by default. Most NoSQL stores make one item atomic, offer small bounded multi-item transactions and often default to eventually consistent reads (ACID vs BASE, CAP and PACELC).

5. Operational maturity. Backups, point-in-time recovery, online schema changes and people who have debugged it at 3 am. Managed DynamoDB is simpler to run than PostgreSQL; self-hosted Cassandra is not.

6. Team skill. A SQL-fluent team ships a correct relational design faster than its first wide-column one, and a bad partition key is far harder to fix than a missing index.

Convergence: the line keeps blurring

Stonebraker and Pavlo’s 2024 review argues the relational model still dominates because SQL keeps absorbing the good ideas. The traffic runs both ways.

Relational moved towards NoSQL. SQL:2016 added JSON. PostgreSQL’s jsonb is slightly slower to write than plain json but much faster to query and GIN-indexable, so doc @> '{"cuisine": "biryani"}' can use an index; its docs still advise small, predictably shaped documents because an update locks the whole row. Distributed SQL covers scale-out, SQL:2023 added graph queries, and vector indexes arrived within a year.

NoSQL moved towards relational. Nearly every NoSQL store grew a SQL-like language (CQL, PartiQL, SQL++, and SQL for MongoDB Atlas in 2021), secondary indexes and transactions:

• MongoDB: multi-document transactions from 4.0 (replica sets) and 4.2 (sharded clusters).
• DynamoDB: TransactWriteItems applies up to 100 actions on distinct items, at most 4 MB in total, within one account and Region, all or nothing.
• Cassandra: lightweight compare-and-set transactions within a partition.

So the real difference today is the default shape. Relational starts normalized and flexible and lets you add denormalized copies for speed; NoSQL starts denormalized and partitioned and lets you add indexes and transactions within limits.

Three myths to retire

“NoSQL is schemaless.” It is schema-on-read: the schema lives in your code and in every document ever written, so a renamed field must be handled in both shapes forever, or migrated anyway. MongoDB even offers optional JSON Schema validation to pin it down.

“SQL doesn’t scale.” One primary plus replicas carries most products further than people expect, and sharding or distributed SQL covers the rest. What scales badly is cross-shard joins and transactions, which most NoSQL stores avoid by simply not offering them.

“NoSQL is faster.” Speed comes from a precomputed access path: data stored in the exact shape of one query, in one partition. A PostgreSQL primary-key lookup is also a single index probe, and Stonebraker and Pavlo note pre-joined data is not necessarily faster than a join. A NoSQL query that misses the key design becomes a scan of every node.

A decision ladder, and the interview default

Choose one slice of data at a time: the core records first, then each special access pattern.

The interview default: start relational unless a concrete access pattern, scale number or data shape argues otherwise, and say which. Relational is the cheapest default to be wrong with: a new question costs an index, not a new table and a backfill; invariants like “one berth, one passenger” are enforced by the database; and every engineer and tool already speaks SQL.

“Argues otherwise” must be specific:

• A number: a billion chat messages a day is 1,000,000,000 ÷ 86,400 ≈ 11,600 writes/s, append-only and always read by conversation and time. That earns a wide-column store.
• An access pattern: riders ping every 4 seconds and we ask for “nearest free riders within 3 km”. That earns Redis geo sets or a geospatial index.
• A shape: every product category has different attributes and is read as one record. That earns a document store, or just a jsonb column.

Polyglot persistence: a food-delivery app

Using several stores, each for the access pattern it suits, is polyglot persistence. Here it is for a Swiggy-style delivery app; the traffic figures are illustrative assumptions.

Orders and payments: PostgreSQL. Placing an order touches the customer, restaurant, items, payment and coupon, all or nothing. At an assumed 3 million orders a day, 3,000,000 ÷ 86,400 ≈ 35 orders/s, or about 175/s at a 5× dinner peak. Even at roughly 10 row writes per order that is about 1,750 row writes/s, the kind of load one relational primary is normally sized for. If growth outruns it, orders shard cleanly by city.

Menus: PostgreSQL as the source, Redis as the cache. Assume 20 menu views per order: 60 million a day ≈ 700/s, about 3,500/s at peak. At a 95% hit rate PostgreSQL sees 5% of 3,500 ≈ 175 reads/s (cache-aside).

Live rider locations: Redis geo sets. Assume 200,000 riders online, each pinging every 4 seconds: 200,000 ÷ 4 = 50,000 updates/s. In PostgreSQL each UPDATE writes a new row version plus WAL for vacuum to clean up, for data that is worthless a minute later. In Redis, GEOADD overwrites the position and GEOSEARCH finds free riders within 3 km; after a restart, riders re-send within 4 seconds (proximity search).

Search: Elasticsearch. “biriyani near me” should match biryani, rank by rating and distance, and facet by veg-only, without loading the primary.

Analytics: a warehouse fed by CDC. “Average delivery time by area and hour over 90 days” scans 3,000,000 × 90 = 270 million orders, which is a columnar warehouse job.

What it costs. Five systems to secure, monitor, back up and be on call for. Copies lag, so a closed restaurant can linger in search for a few seconds. Each copy needs a rebuild path and drift checks. And the sync must be reliable, which is why the arrows come from change-data-capture or a transactional outbox, never from application code.

Going deeper: how the wrong choice fails

The new-question problem

Six months in, product wants “orders per restaurant, by hour”. In PostgreSQL you add an index on (restaurant_id, placed_at). In a query-first design you create orders_by_restaurant, backfill it, update every write path, and handle the window where the two tables disagree. Choosing a key-shaped store is a bet that your questions won’t change much.

Hot partitions and scatter-gather

Partitioned stores spread load only if keys do. A celebrity chef’s restaurant in a flash sale puts all traffic on one key and one node; fixes include write sharding (suffix the key with 0–9 and read all ten), caching and higher-cardinality keys (hot keys). The opposite failure is a query without the partition key, like city = Pune in Figure 2: it asks every node, waits for the slowest, and gets costlier as the cluster grows.

Copies that lag or bloat

Denormalized copies are eventually consistent unless written in one transaction; DynamoDB’s docs say a committed transaction reaches global secondary indexes and streams gradually. Read the source for truth-critical checks like payment status or seat availability. Embedded arrays that grow without bound (every comment on a viral post) make each update rewrite a bigger document until it hits a size limit; reference them instead.

The cost of leaving

Data is sticky: Stonebraker and Pavlo note IBM IMS databases still run decades on because migrating is risky. A wrong choice is usually lived with, not rewritten, which is the best argument for a flexible default.

Cheat sheet

How to say it in the interview

“I’ll put the system of record (orders, users, payments) in PostgreSQL: the data is relational, I need multi-row transactions, and I want to answer questions I haven’t thought of yet. Hot menu reads go through Redis and search goes to Elasticsearch, both fed by change-data-capture so there’s one source of truth. I’d move core data to Cassandra or DynamoDB only if the arithmetic says writes outgrow one primary and every read carries a known key, and I’d say that number. NoSQL isn’t faster by magic; it’s fast for the access pattern its keys were designed around.”

Why not just use MongoDB for everything? For self-contained aggregates it works well. But you lose enforced relationships, rebuild joins with duplication or $lookup, and lean on multi-document transactions that MongoDB’s own docs say cost more than single-document writes. A relational schema with a few jsonb columns gives document flexibility where you need it and integrity everywhere else.

When does Cassandra beat Postgres? When writes are huge, append-mostly and always read by a known partition key and time range, especially across data centres. A billion chat messages a day is about 11,600 writes/s; at roughly 200 bytes each that is 200 GB a day, about 73 TB a year before replication. Postgres wins for ad hoc queries, joins, multi-row invariants and frequent updates to the same rows.

How do you do joins in a key-value store? Precompute them, which is what the Cassandra docs prefer: store the combined data under the key you read by and update every copy on write, ideally via CDC. Or join in the application: fetch the parent, then batch-get the children by key. Or send the question to a system that joins, such as the warehouse or the relational source.

Can SQL databases scale horizontally? Yes. Replicas scale reads; Vitess or Citus shard tables across many primaries, at the cost of cross-shard joins and transactions; and Spanner, CockroachDB, TiDB and YugabyteDB shard and replicate automatically while keeping ACID, at the cost of consensus latency on each commit.

Where to go next

• Databases for system design interviews: which database to name for common prompts.
• PostgreSQL vs MySQL: the next choice once you’ve picked relational.
• Database architectures: how primary-replica, sharded and distributed SQL systems are built.
• Data modeling and denormalization: from requirements to tables, documents or query-first designs.
• OLTP vs OLAP: why analytics belongs in a columnar warehouse.
• ACID vs BASE: the consistency trade-off behind many NoSQL designs.
• Database sharding and data replication: Figure 2’s two scaling tools in depth.
• Cassandra and DynamoDB, Redis, Elasticsearch and PostgreSQL: the example’s stores in practice.
• Change-data-capture: keeping polyglot copies in sync without dual writes.

References and further reading

1. A Relational Model of Data for Large Shared Data Banks (Codd, Communications of the ACM, 1970)seas.upenn.eduThe founding paper: relations, data independence, and the ordering, indexing and access-path dependencies of tree and network databases.
2. Bigtable: A Distributed Storage System for Structured Data (Chang et al., OSDI 2006)static.googleusercontent.comThe wide-column model: a sorted map keyed by row, column and timestamp, atomic per row, with no general cross-row transactions.
3. Dynamo: Amazon’s Highly Available Key-value Store (DeCandia et al., SOSP 2007)allthingsdistributed.comWhy primary-key-only services traded relational features, isolation and multi-key updates for availability and simple scale-out.
4. What Goes Around Comes Around… And Around… (Stonebraker and Pavlo, SIGMOD Record, 2024)db.cs.cmu.eduTwenty years of key-value, document, column-family, search, vector, graph and NewSQL systems, and how each converged back towards SQL.
5. NosqlDefinition (Martin Fowler, 2012)martinfowler.comWhere the term came from (a June 2009 meetup in San Francisco) and the common traits that are not a definition.
6. PostgreSQL documentation: JSON typespostgresql.orgjson versus jsonb, containment queries, GIN indexing, and advice to keep documents small because updates lock the whole row.
7. MongoDB manual: Transactionsmongodb.comSingle-document atomicity, multi-document ACID transactions (replica sets from 4.0, sharded clusters from 4.2) and their extra cost.
8. Amazon DynamoDB Developer Guide: Transactionsdocs.aws.amazon.comTransactWriteItems limits (100 actions, 4 MB, one account and Region), the 400 KB item limit and gradual propagation to indexes and streams.
9. Apache Cassandra documentation: RDBMS design versus Cassandracassandra.apache.orgNo joins, no referential integrity, denormalization as normal, and query-first table design.
10. Neo4j documentation: What is a graph databaseneo4j.comNodes, typed directed relationships and properties, stored natively so traversals need no joins; clusters for fault tolerance and read scaling.