SQL vs NoSQL

Pradeep P
3 days ago
4 min read

Series: Modern System Design · Layer 1 — The building blocks

Layer 1 · Post 12 of 15

← Previous: Databases → Next: Database Indexes

Layer 1 — The building blocks · Post 12 of 88

SQL databases model related data with schemas and joins. NoSQL databases trade some of that structure for scale, flexibility, or simpler operations.

What you'll learn

What you actually gain and lose with each family
How to choose from access patterns, not from hype
Why "NoSQL" is several different ideas sharing a marketing name

The idea in one minute

SQL (relational) databases store data in tables with a declared schema. You relate rows with foreign keys and join them at query time. You get a powerful query language and, typically, strong transactions.

NoSQL is a grab bag: documents, key-value, wide-column, graphs. The shared theme is less joining, more pre-shaped data, often with easier horizontal scale or more flexible fields.

Neither is "better." SQL is the default for structured business data. NoSQL is a tool when the shape of access — or the scale — makes joins and a single primary painful.

Why it matters

This is the most common false dichotomy in system design.

Teams pick MongoDB because the payload is JSON, then spend a year reinventing joins and transactions in the application. Other teams pick Postgres, then try to store an unbounded social graph and wonder why one primary is on fire.

The useful question is: do I need to ask many different questions of the same data, with correctness across rows — or do I have one (or few) access paths at huge scale?

How it works

SQL, in practice

You model entities and relationships: users, orders, line items. An order query joins three tables. Changing the question ("revenue by category last week") is often a new SELECT, not a new database.

You pay:

Schema migrations when the model changes.
Join cost as data grows, unless you index well (next post) and keep queries honest.
Vertical scaling of the primary, then replicas, then sharding (harder than in some NoSQL systems).

You gain: constraints, transactions, ad-hoc query, a mature ecosystem, and a mental model that matches a lot of products.

NoSQL, unpacked

"NoSQL" is not one tradeoff.

Style: Document; Looks like: One JSON blob per id; You win when: You load a whole aggregate at once; You lose when: You need many relations across documents
Style: Key-value; Looks like: GET key; You win when: Extremely simple, huge scale; You lose when: You need queries that are not "by key"
Style: Wide-column; Looks like: Partition key + sort key; You win when: Time series, huge writes, known queries; You lose when: You need arbitrary joins
Style: Graph; Looks like: Nodes and edges; You win when: "Friends of friends" style walks; You lose when: Bulk analytics or simple CRUD might be overkill

Many NoSQL stores denormalize: copy the username onto every comment so you do not join. Updates then have to fan out. You traded query-time joins for write-time work.

The real axes (use these in interviews)

Query flexibility vs known access patterns
Transactions across items vs single-item updates
Single primary vs multi-shard write as a first-class feature
Schema on write (SQL) vs schema on read (many documents)

Postgres today also stores JSON, does full-text search, and can replicate. The line is blurrier than 2012. Still: if your core model is relational and your scale fits one decent primary plus replicas, start SQL.

A simple example

E-commerce orders: customers, orders, payments, inventory. You need "this payment belongs to this order" to be true together. SQL.

User session blob: session:abc → JSON, TTL, millions of independent keys. Key-value / Redis / DynamoDB.

Chat messages in a room: room_id + timestamp, append-only, huge write volume, you always fetch "latest N in this room." Wide-column or a document per message with that key design — not a giant SQL join of all rooms.

Product catalog with messy attributes: a document per product can be nicer than 40 nullable columns. Some teams still use SQL JSONB. Both are valid; the access pattern (get product by id vs search facets) decides the rest.

Common mistakes

NoSQL because "we might scale." Premature distribution is more expensive than a well-indexed Postgres for a long time.

SQL for a pure key-value workload at extreme QPS. You can, until you cannot. KV stores exist.

Joining in the application across two NoSQL queries and calling it equivalent to a transaction. It is not, unless you built that (sagas, Layer 4).

Ignoring operational skill. The best database is one your team can back up, upgrade, and explain at 3 a.m.

How this shows up in real systems

Stripe, many banks, internal tools: relational cores.
DynamoDB at Amazon-scale product teams: key/partition design as the schema.
Everyone: more than one store. SQL for money and users, something else for feed, search, or telemetry.

Saying "we'll use both" is mature if you say which data lives where and how they stay in sync (dual write, CDC — Layer 4).

Recap

SQL: schema, joins, transactions, flexible questions, harder write-scale.
NoSQL: several models; usually pre-planned queries, denormalization, easier horizontal writes for the right key.
Choose from access patterns and correctness needs, not from the acronym.