SQL vs NoSQL Under the Hood: How PostgreSQL, MongoDB, and Redis Actually Store and Serve Data

"SQL vs NoSQL" is really a choice of storage architecture

Framing database choice as SQL versus NoSQL obscures the more useful question: what data model and consistency guarantees does the workload actually need? PostgreSQL and MySQL organise data as rows in fixed-schema tables and lean on decades of relational algebra for querying. MongoDB stores flexible, JSON-like documents that can nest related data instead of splitting it across tables. Redis abandons disk-oriented storage almost entirely, keeping data structures in memory for sub-millisecond access. These are different engineering trade-offs, not a strict hierarchy — a well-run PostgreSQL deployment can outperform a badly modelled MongoDB one, and vice versa.

Inside a relational engine: MVCC, B-Trees, and the query planner

PostgreSQL and most mature relational databases use Multi-Version Concurrency Control (MVCC) to let readers and writers operate without blocking each other: rather than locking a row for every read, the engine keeps multiple versions of a row and hands each transaction a consistent snapshot as of when it started. This is why a long-running report query doesn't stall concurrent writes — it simply reads an older, still-valid version of the affected rows, while old versions are later reclaimed by a background process (PostgreSQL calls this VACUUM).

Indexes are what keep lookups fast as tables grow. The default index structure, the B-Tree, keeps values sorted so a lookup or range scan runs in roughly O(log n) comparisons instead of scanning every row (O(n)) — the difference between checking a handful of entries and checking a million as a table scales. PostgreSQL also supports specialised index types: GIN for full-text search and array/JSONB containment queries, GiST for geometric and range data, and BRIN for very large, naturally-ordered tables like time-series logs. When a query runs, PostgreSQL's cost-based optimizer doesn't just use whichever index exists — it estimates the cost of several candidate execution plans (sequential scan, index scan, different join orders and join algorithms such as nested-loop, hash join, or merge join) using table statistics, and picks the cheapest one. Running EXPLAIN ANALYZE on a slow query is the standard way to see which plan was actually chosen and where time is going.

Indexes are not free: every additional index has to be updated on every INSERT, UPDATE, or DELETE, so write-heavy tables with many indexes trade write throughput for read speed. This is a real, load-bearing trade-off in schema design, not just a rule of thumb.

Transactions and isolation levels: what ACID actually promises

ACID — Atomicity, Consistency, Isolation, Durability — describes the guarantees a transaction gets: it either fully commits or fully rolls back (atomicity), leaves the database in a valid state (consistency), behaves as if it ran alone even with concurrent transactions (isolation), and survives a crash once committed (durability). The isolation guarantee is the one with real, practical variation between engines. The SQL standard defines four levels — Read Uncommitted, Read Committed, Repeatable Read, and Serializable — trading correctness against concurrency, and defaults differ by engine: PostgreSQL and Oracle default to Read Committed, while MySQL's InnoDB engine defaults to Repeatable Read. Under Read Committed, two SELECTs in the same transaction can see different data if another transaction commits in between (a non-repeatable read); Repeatable Read prevents that but can still permit phantom rows appearing under some conditions; Serializable forces transactions to behave as though executed strictly one after another, at the cost of more rollbacks under contention.

MongoDB gained ACID guarantees far later than the relational world: single-document writes have always been atomic, but multi-document, cross-collection ACID transactions only arrived in MongoDB 4.0 (2018), and cross-shard transactions in 4.2. Redis occupies a different niche — individual commands are atomic, and MULTI/EXEC queues a batch of commands for atomic execution, but Redis transactions do not roll back on a runtime error partway through the batch (only queue-time errors, like a misspelled command, abort the whole transaction) — a frequently misunderstood distinction from relational rollback semantics.

Document stores: modelling the same problem without JOINs

Where a relational schema would normalise a users-and-orders relationship into two tables joined on a foreign key, a document database like MongoDB more often embeds related data directly inside a document, or uses application-side references when the related data is large or shared. A relational query that filters active users created since a given date, joins their completed orders, and aggregates total spend per user reads naturally as a single SQL statement with a WHERE clause, an INNER JOIN, and a GROUP BY/HAVING. MongoDB's equivalent is an aggregation pipeline: a $match stage filters users, a $lookup stage performs the join-like lookup against the orders collection, $unwind flattens the resulting array, a second $match filters by order status, and $group performs the per-user aggregation — the same relational operations (WHERE, JOIN, GROUP BY, HAVING, ORDER BY, LIMIT), expressed as a pipeline of stages rather than a single declarative query.

Under the hood, MongoDB has used the WiredTiger storage engine by default since version 3.2, which itself uses MVCC-style document-level concurrency control and B-Tree indexing — architecturally closer to a relational engine internally than the schema-less marketing sometimes suggests. The real difference is at the modelling layer: embedding avoids joins for read-heavy, document-shaped access patterns, but risks data duplication and awkward updates when the same data needs to change in many places at once.

Redis: an in-memory data-structure server, not just a cache

Redis is often introduced as "a cache," which undersells what it actually offers: a set of native data structures manipulated directly by the server, all held in memory for speed. Beyond simple string key-value pairs (SET key value, GET key, with optional expiry via EXPIRE or SET ... EX), Redis supports hashes (field-value maps ideal for representing an object without serialising it), lists (for queues), sets and sorted sets (ZADD/ZRANGE, useful for leaderboards and rate limiting, ordered by a numeric score), and a publish/subscribe messaging mode. Because operations on these structures run in memory, Redis routinely serves requests in well under a millisecond, which is why it's the default choice for session storage, caching layers, and rate limiters in front of a slower primary database.

Being memory-resident does not mean Redis is volatile by default: it supports two persistence strategies, RDB (periodic point-in-time snapshots to disk) and AOF (an append-only log of every write, replayed on restart for stronger durability at the cost of larger files and slightly higher write overhead), and the two can be combined. For horizontal scaling, Redis Cluster splits the keyspace into 16,384 fixed hash slots distributed across nodes, so a client can compute which node owns a given key without a central lookup service.

Scaling out: replication, sharding, and the CAP theorem in practice

Replication and sharding solve different problems and are often confused. Replication copies the same data to multiple servers — a primary handling writes and one or more replicas serving reads, or in leaderless systems, symmetric copies — primarily for fault tolerance and to spread read load. Sharding instead splits the dataset itself, sending different key ranges or hash buckets to different servers, which is what actually allows write throughput to scale horizontally, since no single server holds (or has to write) the entire dataset. MongoDB implements sharding through config servers that track which chunks of data live on which shard, and a mongos router layer that directs each query to the right shard(s); PostgreSQL relies on extensions (such as Citus) or manual partitioning for equivalent behaviour, since sharding isn't native to core PostgreSQL.

The CAP theorem states that a distributed system experiencing a network partition must choose between consistency and availability — it cannot guarantee both simultaneously during the partition. In practice the picture is more nuanced than the common "MongoDB is CP, Cassandra is AP" shorthand suggests: MongoDB's behaviour depends heavily on read/write concern settings — reading only from a primary with majority write concern behaves close to CP, while allowing reads from secondaries trades consistency for availability and lower latency. Cassandra is tunable per-query via consistency levels (ONE, QUORUM, ALL), so calling it flatly "AP" is a simplification of a dial, not a fixed setting. The theorem is best treated as describing a spectrum of configurable trade-offs each system exposes, not a permanent label stamped on a product.