A structured decision framework using storage engine mechanics, consistency models, and scaling limits.
Written by Jatin Jain Saraf · Architect of SupraScanModule 11 — SQL vs NoSQL: An Engineer's Framework, Not a Marketing Debate
What this module covers: The SQL vs NoSQL debate is usually noise. Marketing claims, hype cycles, and cargo-culted architecture decisions dominate the conversation. This module cuts through that. By this point in the course you understand Postgres's internals precisely — MVCC, WAL, the planner, indexing. This module uses the same precision to examine what MongoDB, Redis, Cassandra, and ClickHouse actually do differently at the storage and consistency layer, when those differences matter, and a five-question decision framework for choosing correctly.
The Question Nobody Asks
The usual framing: "Should I use SQL or NoSQL?"
The useful framing: "What are the consistency guarantees, storage mechanics, and scaling characteristics I need — and which database provides exactly that?"
Every database exists because it makes a specific set of trade-offs that some workload needs. MongoDB is not "better" than Postgres. Cassandra is not "more scalable." Redis is not "faster." Each makes different trade-offs, and the right choice depends entirely on which trade-offs your workload can accept.
This module does not give you a winner. It gives you the precision to make the right call for your specific situation.
Postgres vs MongoDB
What MongoDB Actually Is
MongoDB is a document database. Documents are stored as BSON (binary JSON) with flexible schemas — each document in a collection can have different fields. The storage engine (WiredTiger since MongoDB 3.2) uses B-tree structures similar to Postgres for indexes.
The marketing pitch: "flexible schema, horizontal scaling, JSON-native." The engineering reality is more nuanced.
Storage Engine Comparison
Postgres (heap-based, MVCC):
- Rows stored in fixed-structure 8KB heap pages
- MVCC via
xmin/xmax— dead tuples accumulate, autovacuum reclaims them - WAL for durability and replication
- Full ACID transactions across multiple tables
MongoDB (WiredTiger, document-level concurrency):
- Documents stored in B-tree files, compressed by default (Snappy or zstd)
- Concurrency at the document level: multiple writers can write to the same collection simultaneously as long as they touch different documents
- Before WiredTiger: collection-level lock (global write lock in very old versions) — this is where "MongoDB doesn't scale for writes" came from, and it has not been true since 2015
- Multi-document ACID transactions added in MongoDB 4.0 (2018) — but with higher overhead than Postgres single-row operations
The dead tuple problem: MongoDB's WiredTiger uses a different mechanism than MVCC but has its own version of space reclamation. Deleted and updated documents leave fragmented space that compact reclaims — analogous to VACUUM FULL in Postgres. MongoDB's background compaction is generally less aggressive than Postgres's autovacuum.
Schema Flexibility: When It Matters
MongoDB's flexible schema is a genuine advantage when:
- You are still figuring out your data model — you can store documents without upfront schema definition and add fields later without migrations
- Different entities in the same collection have genuinely different shapes — product catalog where electronics have voltage/wattage but clothing has size/material
- You are ingesting external data you do not control and cannot normalize upfront
MongoDB's flexible schema is a trap when:
- You use it to avoid thinking about schema design — you end up with inconsistently structured documents that require application-layer validation instead of database-enforced constraints
- Your "documents" are actually relational data — orders, line items, users, payments — that you are embedding in nested documents to avoid joins, but then querying across the nesting
- Your documents grow unbounded — MongoDB has a 16MB document size limit; documents with embedded arrays that grow over time hit this limit
Joins: The Real Comparison
Postgres has native, optimized joins. MongoDB has $lookup (aggregation pipeline join). The comparison:
// MongoDB: find all transactions with their block info
db.transactions.aggregate([
{ $match: { status: "confirmed" } },
{ $lookup: {
from: "blocks",
localField: "block_height",
foreignField: "height",
as: "block"
}},
{ $unwind: "$block" }
])
-- Postgres equivalent
SELECT t.*, b.validator, b.timestamp
FROM transactions t
JOIN blocks b ON b.height = t.block_height
WHERE t.status = 'confirmed';
MongoDB's $lookup is a nested loop join — for each transaction, it looks up the block. There is no hash join or merge join. For large collections, this is significantly slower than Postgres's cost-based join selection. MongoDB's query optimizer does not consider join strategies the way Postgres's planner does.
When MongoDB wins on data access: when your access pattern is almost entirely document-centric — fetch a single document by _id, update a single document — and joins are rare. A content management system where each article is a self-contained document with embedded metadata, tags, and author info is a legitimate MongoDB use case.
When Postgres wins: any workload with multi-table queries, aggregations across relationships, or constraints that must be enforced across documents.
Transactions
Pre-MongoDB 4.0: no multi-document transactions. This was the fundamental consistency gap.
Post-MongoDB 4.0: multi-document transactions exist but:
- Higher overhead than single-document operations (write concern, distributed lock)
- Not enabled by default for all operations (you must explicitly start a session and transaction)
- Performance degrades significantly under high transaction concurrency compared to Postgres's MVCC
If your workload requires ACID transactions across multiple documents/tables: Postgres is the correct choice. MongoDB's transactions work, but they are a retrofit onto a system not designed for them.
Postgres vs Redis
What Redis Actually Is
Redis is an in-memory data structure store. It keeps its entire working dataset in RAM. Persistence is optional and asynchronous by default (RDB snapshots or AOF append-only file). It supports strings, hashes, lists, sets, sorted sets, streams, and more as first-class data types.
The marketing pitch: "blazing fast, sub-millisecond latency." This is true, and it is true because Redis does not write to disk on every operation by default.
The Durability Trade-off
Redis's default configuration (save 900 1, save 300 10, save 60 10000):
- Saves a snapshot to disk every 15 minutes if at least 1 key changed
- Saves every 5 minutes if at least 10 keys changed
- Saves every minute if at least 10,000 keys changed
If Redis crashes between saves, you lose everything since the last save. This is an explicit design choice — Redis trades durability for speed.
With appendonly yes and appendfsync always, Redis syncs every write to the AOF file — similar to Postgres's synchronous_commit = on. Latency increases to 1–5ms per write (same as Postgres). The throughput advantage largely disappears.
Redis default (RDB only): ~100,000 ops/sec, up to 15min data loss on crash
Redis (AOF, appendfsync=always): ~50,000 ops/sec, durable
Postgres (synchronous_commit=on): ~10,000 TPS, durable, full ACID
Redis is faster than Postgres for the same durability level — but not infinitely faster once durability requirements are equal.
When Redis Is the Right Choice
Caching: Redis's primary legitimate use case. Store the results of expensive Postgres queries, pre-computed aggregations, or session data with a TTL. The in-memory nature and LRU eviction make it ideal.
Rate limiting: Redis's atomic INCR and EXPIRE commands make rate limiting straightforward and fast. Doing this in Postgres requires a table with a FOR UPDATE lock or careful use of advisory locks.
Pub/Sub and queues: Redis Streams and Pub/Sub are purpose-built for message passing. Postgres's LISTEN/NOTIFY exists but is not designed for high-throughput message buses.
Sign in to keep reading
The rest of this module is free — sign in with Google to unlock it and track your progress.
Sign in & RegisterDiscussion
0Join the discussion