The most consequential choice in any design, and the one most often made by reflex. “NoSQL scales better” is not a reason.
Prerequisites: Consistency Models, CAP & PACELC Time to read: ~25 minutes
You need to store data. There are hundreds of databases. The internet says relational databases don’t scale and NoSQL does, which is roughly as accurate as saying cars are faster than boats.
The real question is never “SQL or NoSQL.” It’s: what are my access patterns, what consistency do I need, how much data will there be, and what am I willing to give up?
Answer those and the database chooses itself.
Data in tables. Rows and typed columns. Relationships by foreign key. Queried with SQL.
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
total NUMERIC(10,2) NOT NULL CHECK (total >= 0),
status TEXT NOT NULL
);
SELECT u.email, SUM(o.total) AS lifetime_value
FROM users u JOIN orders o ON o.user_id = u.id
WHERE o.status = 'completed'
GROUP BY u.email
HAVING SUM(o.total) > 1000;
What you’re actually buying:
| Property | What it means for you |
|---|---|
| Schema enforcement | The database rejects bad data. total cannot be "banana". Bugs fail at write time, not at read time in production three months later. |
| Normalization | Each fact stored once. Change a user’s email in one place and every query sees it. |
| Joins | Combine data at query time. You don’t have to decide your access patterns in advance. |
| ACID transactions | Multi-row, multi-table atomicity. Move money between accounts and either both sides happen or neither does. |
| Ad-hoc queries | New question? Write a new query. No migration, no rebuild. |
| Referential integrity | You cannot have an order pointing at a deleted user. |
| Maturity | 40 years of query optimizers, tooling, expertise, and battle-tested behaviour. |
🚨 The most underrated of these is ad-hoc queryability. When the business asks “how many users in Lahore ordered twice last month but not this month?”, SQL answers it in ten minutes. In a NoSQL store modelled around known access patterns, that question can require a new table, a backfill, and a week.
Where relational databases genuinely struggle:
ALTER TABLE on a billion rows needs care and tooling.
→ Zero-Downtime MigrationsJSONB handles this well — see below.)Systems: PostgreSQL, MySQL, SQL Server, Oracle. And “NewSQL” — CockroachDB, TiDB, YugabyteDB, Google Spanner — which offer SQL and ACID across a horizontally-scaled, sharded cluster. These matter: they weaken the old “SQL can’t scale writes” argument considerably.
“NoSQL” isn’t one thing. It’s four unrelated categories that share only the property of not being relational.
GET key / PUT key value. The value is opaque.
Strengths: the simplest possible model, so it’s the fastest and easiest to distribute. Partition by key hash and you scale linearly, forever.
Weaknesses: you can only look things up by key. No querying by value, no joins, no aggregation.
Use for: caching, sessions, feature flags, user preferences, shopping carts, real-time counters.
Systems: Redis, Memcached, DynamoDB (in its simplest form), etcd, Riak.
JSON-ish documents, grouped in collections. Schema-flexible; you can query inside the document.
{
"_id": "user_42",
"name": "Bilal",
"addresses": [
{"type": "home", "city": "Lahore"},
{"type": "work", "city": "Karachi"}
],
"preferences": {"theme": "dark", "notifications": true}
}
Strengths: the document shape matches the object shape in your code. Related data lives together, so one read fetches everything (no joins). Schema evolution is easy — add a field, done.
Weaknesses: the flexible schema means nothing validates your data, so your application must. Denormalized data must be updated in many places. Cross-document joins are weak or absent. Large documents that grow unboundedly (an array of comments) become a real problem.
Use for: content management, product catalogues, user profiles, event logging, anything where an aggregate is read and written as a whole.
Systems: MongoDB, DynamoDB, Couchbase, Firestore.
A row key, then arbitrarily many columns grouped into families. Think “a sorted, distributed, multi-dimensional map.” Optimized for enormous write throughput and range scans over a partition.
Partition key: user_42
├─ 2026-07-22T10:00Z → {event: "login", ip: "1.2.3.4"}
├─ 2026-07-22T10:05Z → {event: "view", page: "/home"}
└─ 2026-07-22T10:09Z → {event: "purchase", amount: 4200}
Strengths: massive write throughput (LSM-based, sequential writes), linear scaling, efficient range queries within a partition, tunable consistency.
Weaknesses: you must design the table around the queries you will run. There is no ad-hoc querying, no joins, and changing your access pattern usually means a new table and a migration. Hot partitions are a constant operational concern.
Use for: time series, messaging history, IoT/sensor data, event logs, activity feeds — anything write-heavy with known access patterns.
Systems: Cassandra, ScyllaDB, HBase, Google Bigtable.
Nodes and edges as first-class citizens, with traversal as the primary operation.
Strengths: relationship queries that are catastrophic in SQL become natural. “Friends of friends who like X, within 4 hops” is a single traversal instead of four self-joins.
Weaknesses: a niche tool. Poor at bulk aggregation and simple record retrieval. Harder to scale horizontally (graphs resist partitioning — any cut severs edges). Smaller talent pool.
Use for: social graphs, recommendations, fraud rings, knowledge graphs, network topology, permission hierarchies.
Systems: Neo4j, Amazon Neptune, JanusGraph, Dgraph. → Graph Databases
| Relational | Key-value | Document | Wide-column | Graph | |
|---|---|---|---|---|---|
| Query flexibility | ⭐⭐⭐⭐⭐ | ⭐ | ⭐⭐⭐ | ⭐⭐ | ⭐⭐⭐⭐ (traversals) |
| Write scalability | ⭐⭐ (⭐⭐⭐⭐ NewSQL) | ⭐⭐⭐⭐⭐ | ⭐⭐⭐⭐ | ⭐⭐⭐⭐⭐ | ⭐⭐ |
| Schema safety | ⭐⭐⭐⭐⭐ | ✗ | ⭐⭐ | ⭐⭐⭐ | ⭐⭐⭐ |
| Transactions | ⭐⭐⭐⭐⭐ | Single-key | Single-doc (multi-doc in modern MongoDB) | Single-partition | ⭐⭐⭐⭐ |
| Joins | Native | ✗ | Limited | ✗ | Native (traversal) |
| Operational maturity | ⭐⭐⭐⭐⭐ | ⭐⭐⭐⭐ | ⭐⭐⭐⭐ | ⭐⭐⭐ | ⭐⭐ |
🚨 “SQL doesn’t scale.” Misleading. SQL reads scale trivially with replicas. SQL writes scale vertically very far — a well-tuned Postgres on modern hardware handles tens of thousands of writes per second and terabytes of data. And NewSQL databases scale writes horizontally while keeping SQL and ACID. The honest statement: “relational writes on a single primary have a ceiling, and past it you shard, which costs you joins and cross-shard transactions.”
🚨 “NoSQL is schemaless.” No — it’s schema-on-read. The schema still exists; it’s just enforced by your application code instead of the database, in every service, forever. When it drifts, you find out in production. The flexibility is real, but so is the cost.
🚨 “NoSQL is faster.” Only for its specific access patterns. A key lookup in DynamoDB is fast; a key lookup in an indexed Postgres table is also fast. NoSQL wins on write throughput at scale and horizontal scaling, not on raw per-operation speed.
🚨 “NoSQL means no transactions.” Outdated. MongoDB has multi-document transactions, DynamoDB has transactional writes, Cassandra has lightweight transactions. They’re more limited and more expensive than relational transactions, but they exist.
🚨 “Postgres can’t do JSON.” It can, well. JSONB gives you binary-stored JSON with indexing
(GIN), containment operators, and path queries. For many “we need a document store” cases, Postgres
with JSONB gives you documents and joins and transactions in one system.
🎙️ “I’d start with Postgres. It handles the relational parts properly, and JSONB covers the semi-structured fields. If we later hit a write ceiling on one table, we can move that specific workload out rather than adopting a second database on day one.”
Ask these five questions, in order.
The single most important question, and it comes before choosing a database.
🚨 This is the real reason NoSQL migrations fail. Teams model Cassandra around today’s queries, the product changes, and the new query is impossible without rebuilding the table.
| Scale | Answer |
|---|---|
| < 1 TB, < 10k writes/sec | One relational database. No debate. |
| 1–10 TB, moderate writes | Relational + read replicas + caching |
| > 10 TB or > 50k writes/sec | Shard relational, or use NoSQL, or NewSQL |
| Petabytes, write-dominated | Wide-column (Cassandra/Bigtable) |
📐 Do the estimation first (Back-of-the-Envelope). Most systems that “need NoSQL for scale” turn out to be at 200 QPS.
Payments and inventory need strong consistency. View counts don’t. You can (and should) use different stores for different data. → Consistency Models
Highly relational with many-to-many relationships → relational. Self-contained aggregates → document. Time-ordered events per entity → wide-column. Relationships are the data → graph.
🚨 The most underweighted factor. A database nobody on the team can debug at 3 a.m. is a liability regardless of its benchmarks. Managed services (RDS, Aurora, DynamoDB, Atlas) shift much of this burden, and choosing one is a legitimate, senior answer.
Real systems use several databases, each for what it’s good at:
E-commerce platform
├── PostgreSQL → orders, payments, inventory (ACID matters)
├── Redis → sessions, cart, hot cache (speed matters)
├── Elasticsearch → product search (relevance matters)
├── Cassandra → user activity events (write volume matters)
├── S3 → product images (size matters)
└── Neo4j → "customers also bought" (relationships matter)
⚖️ The cost is real, though: more systems to operate, monitor, back up, and secure; data synchronization between them (usually via CDC or events); no cross-store transactions; and every engineer must learn several data models.
🎙️ The balanced answer: “I’d start with Postgres for everything and add specialized stores only when a specific workload demonstrably outgrows it — search first, probably, then the event stream. Each additional datastore is real operational cost, so it needs to earn its place.”
| Choice | Gain | Cost |
|---|---|---|
| Relational | Flexible queries, integrity, transactions, maturity | Write ceiling on one primary; schema migrations at scale |
| Key-value | Simplest and fastest; scales linearly | Lookup by key only |
| Document | Matches object shape; no joins needed; easy evolution | No enforced schema; denormalization update problems |
| Wide-column | Enormous write throughput; linear scaling | Must design around queries; no ad-hoc access; hot partitions |
| Graph | Natural traversals | Hard to scale horizontally; niche skills |
| Polyglot | Right tool per job | Operational multiplication; sync complexity |
1. Model the same thing three ways. Take a blog with posts, authors, comments, and tags. Model it in: Postgres (normalized), MongoDB (documents), and Cassandra (query-driven tables). Then write these queries against each:
The last one is where the differences become vivid — trivial in SQL, awkward in MongoDB, and requiring a purpose-built table in Cassandra.
2. Find the write ceiling. Run Postgres in Docker. Insert rows as fast as you can with 1, 10, and 100 concurrent connections. Find where throughput stops improving. Now you have a real number for “when would we need to shard?” instead of a vibe.
3. Use JSONB properly. In Postgres, store a JSONB column, add a GIN index, and query inside it. Time it against a normalized equivalent. This will change how often you reach for a document store.