Database Fundamentals — SQL vs NoSQL, Choosing Correctly

The framing that actually matters

"SQL vs NoSQL" gets treated like a tribal allegiance — pick a side, defend it in code reviews. That's the wrong frame entirely. The right frame is: what shape is your data, how is it accessed, and what can you afford to trade away?

Answer those three questions honestly and the database choice mostly makes itself.

Question 1: What shape is your data?

Relational databases (PostgreSQL, MySQL) are built for data with stable, well-defined relationships — a user has many orders, an order has many line items, a line item references a product. These relationships are enforced by the database itself through foreign keys and constraints, which means the database actively prevents you from creating orphaned or inconsistent data.

NoSQL databases come in a few shapes, each suited to different data:

• Document stores (MongoDB, DynamoDB) suit data that's naturally self-contained — a user profile with nested preferences, a product catalog entry with variable attributes per category.
• Key-value stores (Redis, Memcached) suit data accessed by a single known key, where you don't need to query by anything else — session data, feature flags, rate-limit counters.
• Wide-column stores (Cassandra, HBase) suit write-heavy, time-series-shaped data at massive scale — sensor readings, event logs, metrics.
• Graph databases (Neo4j) suit data that is the relationships — social graphs, recommendation engines, fraud-ring detection.

If you're modeling an e-commerce order system, the data has inherent relational structure — use a relational database. If you're modeling a product catalog where every category has wildly different attributes (a shoe has size and color; a laptop has RAM and screen size), forcing that into rigid relational tables produces either a sparse mess of nullable columns or an over-normalized attribute-value nightmare. A document store fits that shape naturally.

Question 2: How is your data accessed?

This is where a lot of "SQL vs NoSQL" arguments go wrong — they focus on write-time data modeling and ignore read-time access patterns, which usually matter more in practice.

Ask: what are the actual queries this system needs to answer, and how often does each one run?

• If you need ad-hoc queries across multiple dimensions — "show me all orders over $500 from customers in California placed last month" — SQL's declarative query language and query planner are doing real work for you. Recreating that flexibility in a NoSQL store usually means either denormalizing aggressively or bolting on a separate search index.
• If your access pattern is "fetch this exact record by this exact key, millions of times a second," a key-value store will outperform a relational database by a wide margin, because it skips the query planning and join overhead entirely.
• If you need to fetch "this document and everything nested inside it" in a single read — a user and their full settings object — a document store avoids the join that a normalized relational schema would require.

A useful exercise before choosing: write out your five most frequent queries in plain English before picking a database. If four of five are "look up by ID," that's a strong NoSQL signal. If three of five involve filtering or joining across multiple entities, that's a strong SQL signal.

Question 3: What can you trade away?

This is the CAP-theorem-adjacent question, and it's the one most engineers underweight until it bites them in production.

Relational databases default to strong consistency — a write is immediately visible to every subsequent read, enforced by transactions and locking. That's exactly what you want for a bank balance or an inventory count, where showing stale data means real financial harm.

Many NoSQL databases default to eventual consistency in exchange for availability and horizontal write scale — a write to one node might take a few hundred milliseconds to propagate to others, during which a read from a different node returns the old value. That's a completely acceptable trade for a "likes" counter or a view count, where a slightly stale number for a moment causes zero real harm, and it's an unacceptable trade for account balances.

The mistake to avoid: picking a database based on scale requirements alone, without checking whether its consistency model matches what your data actually needs. A system that needs strong consistency but chooses a database that only offers eventual consistency will end up rebuilding consistency guarantees in application code — which is slower, harder to get right, and harder to test than letting the database do it.

A decision framework, condensed

If your data has...	And your access is...	And you need...	Lean toward
Stable, enforced relationships	Ad-hoc, multi-dimensional queries	Strong consistency	Relational (SQL)
Self-contained, variable-shape records	Fetch whole record by key	Flexible schema	Document store
Simple values, no relationships	Extremely fast lookup by known key	Low latency at high throughput	Key-value store
High write volume, time-ordered	Range scans by time/key	Horizontal write scale	Wide-column store
The relationships themselves are the data	Traversal, "friends of friends"	Deep relationship queries	Graph database

The trap: choosing based on scale you don't have yet

The most common real-world mistake isn't picking the wrong database type — it's picking a NoSQL database "for scale" for a system that will realistically never outgrow a well-indexed PostgreSQL instance, and then paying the cost of weaker consistency guarantees and less flexible querying for a scale problem that never materializes.

A single well-tuned relational database, with read replicas and appropriate indexing, comfortably handles far more load than most systems will ever see. Reach for NoSQL when the shape of your data or a specific access pattern demands it — not because a well-known company uses it at a scale you're nowhere near.

Next in this series: a bonus deep-dive on PACELC, which extends the consistency-vs-availability trade-off from this post into what actually happens when there isn't a network partition — a distinction CAP theorem alone doesn't cover, and one that changes how you should think about "eventually consistent" databases even during normal operation.