PostgreSQL In-Depth
Module A-11·32 min read

A structured decision framework using storage engine mechanics, consistency models, and scaling limits.

JJS
Written by Jatin Jain Saraf · Senior Software Engineer

Module 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:

  1. You are still figuring out your data model — you can store documents without upfront schema definition and add fields later without migrations
  2. Different entities in the same collection have genuinely different shapes — product catalog where electronics have voltage/wattage but clothing has size/material
  3. You are ingesting external data you do not control and cannot normalize upfront

MongoDB's flexible schema is a trap when:

  1. 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
  2. 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
  3. 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:

javascript
sql

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.

text

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 & Register
PreviousModule A-10: Replication, High Availability, and the CAP Trade-offsNext Module A-12: Production Operations: Monitoring, Migration, and the Runbooks That Matter

Discussion

0

Join the discussion

Loading comments...
PortfolioBlog

© 2026 Jatin Jain Saraf (JJS). All rights reserved.