PostgreSQL vs MongoDB vs Redis: The Architectural Deep Dive

Every engineering team eventually confronts the database selection dilemma: should you standardize on a battle-tested relational engine, adopt a flexible document store, or leverage an ultra-fast in-memory data structure cache?

The common industry narrative reduces this choice to “Tables vs JSON vs Key-Value.” That simplification hides the critical engineering realities that dictate system performance: storage engines, memory hierarchies, disk I/O patterns, concurrency primitives, and write-ahead logging mechanics.

In high-concurrency production systems, choosing the wrong data store leads to spiraling hardware costs, memory bloat, distributed locking deadlocks, or disastrous data corruption. This guide breaks down the internals of PostgreSQL, MongoDB, and Redis, contrasting their architectures and establishing a blueprint for modern polyglot persistence.

1. Core Architectural Mental Models

Before examining code or benchmarks, you must understand the primary operational purpose and mechanical design of each engine:

Metric	PostgreSQL	MongoDB	Redis
Primary Paradigm	Relational & Object-Relational (RDBMS)	Document Store (Distributed NoSQL)	In-Memory Data Structure Store
Primary Data Unit	Tuples arranged in 8KB Pages	BSON Documents inside Collections	In-memory keys pointing to typed data structures
Storage Engine	Heap tables + Buffer Pool + WAL	WiredTiger (B-Tree + Log)	In-Memory (RAM) + Optional RDB / AOF
Primary Concurrency Model	Multi-Version Concurrency Control (MVCC)	Document-level concurrency + MVCC	Single-threaded command loop (Reactor pattern)
Default Latency Profile	1ms – 15ms (Disk / Shared Buffers)	1ms – 8ms (WiredTiger Cache / NVMe)	100µs – 800µs (Direct DRAM access)
Durability Default	Strict ACID (Synchronous WAL flush)	Tunable ACID (Journaling + Write Concern)	Configurable (AOF fsync / Point-in-time RDB)

2. Storage Engines & Memory Internals

How an engine organizes memory and flushes bytes to persistent storage determines its throughput ceiling and crash recovery profile.

PostgreSQL: Slotted Pages, Shared Buffers, and the Heap

PostgreSQL stores table data in fixed 8KB pages (slotted pages) on disk:

Shared Buffers (shared_buffers): PostgreSQL bypasses direct operating system writes by managing its own in-memory cache of pages. Reads first check shared_buffers; if absent, the kernel pulls the page into the OS page cache, then into PostgreSQL memory.
Write-Ahead Logging (WAL): When a transaction commits, PostgreSQL writes the change records sequentially to the WAL ring buffer and synchronously flushes them to disk before modifying dirty pages in the heap. This enables crash recovery and streaming replication.
MVCC & Bloat Management: PostgreSQL implements MVCC by creating a new version of the row (tuple) on every update or delete, tagging it with xmin (creation transaction ID) and xmax (expiration transaction ID). Because dead tuples accumulate in the 8KB heap pages, the background VACUUM and autovacuum processes must reclaim space and freeze transaction IDs to prevent transaction wraparound.
Indexing Primitives: PostgreSQL offers extensible indexing: B-Tree (default for equality and ranges), GIN (Generalized Inverted Index, critical for querying nested keys inside JSONB), GiST (for geometric and full-text data), and BRIN (Block Range Index, compact indexing for massive append-only sequential time-series tables).

MongoDB: WiredTiger, BSON, and Compressed Caching

MongoDB transitioned to the WiredTiger storage engine, replacing its legacy mmapv1 architecture:

Memory Allocation: By default, WiredTiger reserves 50% of (RAM - 1GB) for its internal cache. The remaining RAM is intentionally left for the operating system page cache to hold compressed disk blocks.
WiredTiger Cache & Compression: Data on disk is compressed using Snappy (optimized for CPU efficiency) or zlib (optimized for disk density). WiredTiger decompresses disk blocks into uncompressed BSON structures inside its working cache.
Checkpoints & Journal: WiredTiger snapshots its memory state to disk at 60-second intervals (checkpoints). To guarantee durability between checkpoints, changes are written to an append-only write-ahead journal (flushed to disk every 100ms by default, or immediately when using w: "majority" with j: true).
Document Model Constraints: Data is stored as BSON (Binary JSON), which includes native data types like 64-bit integers, doubles, dates, and ObjectId. A single BSON document has a hard size limit of 16MB to prevent unbounded RAM consumption and lock-holding during document reads.

Redis: In-Memory Reactor, jemalloc, and Persistent Logs

Redis takes an entirely distinct route: primary storage is RAM, eliminating disk seek times entirely.

Single-Threaded Execution: Redis processes commands using a single-threaded event loop based on I/O multiplexing (epoll on Linux, kqueue on macOS/BSD). Because commands execute sequentially without thread context switching or lock contention, operations complete with sub-millisecond determinism. While Redis 6.0 introduced threaded I/O for network socket read/write processing, the command execution engine remains single-threaded.
Memory Allocator: Redis manages dynamic memory via jemalloc, which minimizes fragmentation. When RAM reaches maxmemory, Redis applies eviction policies such as volatile-lru, allkeys-lru, volatile-lfu, or allkeys-lfu.
Persistence Mechanisms:
- RDB (Redis Database): Generates point-in-time binary snapshots using bgsave. Redis forks a child process using OS Copy-on-Write (COW), dumping memory state to an .rdb file without blocking incoming client commands.
- AOF (Append-Only File): Logs every mutating command. The fsync frequency is tunable: appendfsync always (highest durability, lowest throughput), appendfsync everysec (industry standard: max 1-second data loss window), or appendfsync no (relies on kernel flush).

3. Data Modeling & Query Ergonomics

To evaluate operational complexity, let us examine how each database handles a real-world entity: an Order with a customer ID, status, and line items.

PostgreSQL: Relational Normalization + Hybrid JSONB

PostgreSQL gives you the choice between strict normalization and hybrid semi-structured storage:

-- Normalized Relational Approach
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    status VARCHAR(32) NOT NULL DEFAULT 'pending',
    total_amount NUMERIC(12, 2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    sku VARCHAR(64) NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10, 2) NOT NULL
);

-- Hybrid Semi-Structured Approach using JSONB with GIN Index
CREATE TABLE order_events (
    id BIGSERIAL PRIMARY KEY,
    payload JSONB NOT NULL,
    recorded_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_order_events_payload ON order_events USING GIN (payload jsonb_path_ops);

-- Querying inside JSONB with index containment (@>)
SELECT id, payload->>'customer_id' AS customer
FROM order_events
WHERE payload @> '{"status": "paid", "currency": "USD"}';

Key Takeaway: PostgreSQL enforces schema integrity, foreign key cascading, and arithmetic precision with NUMERIC. When flexibility is required, JSONB gives you document capabilities with indexable paths without giving up ACID guarantees.

MongoDB: Embedded Document & Aggregation Pipeline

In MongoDB, line items are embedded directly within the root order document, avoiding multi-table joins:

// Inserting an embedded document in MongoDB
db.orders.insertOne({
    customerId: ObjectId("66e1f0c29a1b2c3d4e5f6789"),
    status: "paid",
    totalAmount: 149.50,
    items: [
        { sku: "KEYBOARD-MECH-01", qty: 1, price: 109.50 },
        { sku: "USB-C-CABLE-2M", qty: 2, price: 20.00 }
    ],
    metadata: { source: "mobile_app", channel: "organic" },
    createdAt: new Date()
});

// Querying with Aggregation Pipeline ($match, $unwind, $group)
db.orders.aggregate([
    { $match: { status: "paid" } },
    { $unwind: "$items" },
    { $group: {
        _id: "$items.sku",
        totalQuantitySold: { $sum: "$items.qty" },
        revenue: { $sum: { $multiply: ["$items.qty", "$items.price"] } }
    }},
    { $sort: { revenue: -1 } },
    { $limit: 10 }
]);

Key Takeaway: Querying embedded collections avoids joins at read time. However, complex multi-document reporting requires the aggregation pipeline, and schema governance shifts to the application layer (or $jsonSchema validators).

Redis: Primitive Structures & Key-Value Access

Redis stores neither SQL tables nor free-form collections. You construct your data model from typed primitives:

# Storing an order hash for instant field-level access
HSET order:1001 customer_id "98234" status "paid" total_amount "149.50"

# Reading order status in microsecond time
HGET order:1001 status

# Adding order to customer's chronological order index (Sorted Set by timestamp)
ZADD customer:98234:orders 1760342400 "order:1001"

# Retrieving customer's 10 most recent order IDs
ZREVRANGE customer:98234:orders 0 9

# Atomic inventory decrement with safety check (O(1))
DECRBY inventory:KEYBOARD-MECH-01 1

Key Takeaway: Redis requires the engineer to design indexing manually using sorted sets (ZSET), sets (SET), and hashes (HSET). It excels at discrete, high-frequency lookups rather than arbitrary relational queries.

4. ACID, Transactions, and the CAP Theorem

A critical architectural distinction lies in how each system handles network partitions, concurrency conflicts, and isolation levels.

                  Consistency (C)
                     /\
                    /  \
                   /    \
                  /  CA  \
                 /        \
   PostgreSQL --/----------\-- MongoDB (CP Default)
               /            \
              /              \
             /________________\
Availability (A)    AP         Partition Tolerance (P)
               \              /
                \-- Redis ---/ (AP Bias in Cluster/Replication)

PostgreSQL: Full ACID & ANSI Isolation Levels

PostgreSQL provides bulletproof transaction guarantees across any number of tables:

Isolation Levels Supported: Read Committed (default), Repeatable Read, and Serializable.
Serializable Snapshot Isolation (SSI): PostgreSQL detects read-write conflicts (rw-antidependencies) without table-wide locking, aborting transactions that would produce non-serializable anomalies (such as write skew).
Single-Node Mastery: Within a primary instance, data integrity is mathematically guaranteed. When scaling reads via streaming replication, replicas operate under eventual consistency unless synchronous replication (synchronous_commit = on or remote_apply) is enforced.

MongoDB: Document Atomicity to Distributed 2PC

Single Document Atomicity: Updates to a single document are always atomic, even when updating embedded arrays or sub-documents.
Multi-Document Transactions: Introduced in MongoDB 4.0 (for replica sets) and 4.2 (for sharded clusters), multi-document transactions use snapshot isolation across WiredTiger storage engines coordinated by a Two-Phase Commit (2PC) protocol.
Tunable Consistency: Developers control the CAP trade-off per query:
- writeConcern: { w: "majority", j: true, wtimeout: 5000 } ensures durability on a quorum of replica set members before acknowledging the client.
- readConcern: "linearizable" guarantees that reads return data that has been acknowledged by a majority quorum, preventing dirty or stale reads during network partitions.

Redis: Atomicity via Event Loop & Optimistic Locking

Single-Command Atomicity: Because operations run on a single thread, commands like INCR, HSET, and LPUSH are inherently atomic without locks.
MULTI / EXEC Blocks: Groups multiple commands into a pipeline that executes without interleaving from other clients. Note: Redis transactions do not support rollbacks on logical errors—if a command fails due to a wrong data type, remaining commands in the block still execute.

Optimistic Locking with WATCH: Allows client-side CAS (Check-And-Set) operations:

WATCH balance:account_a
# Read balance in application...
MULTI
DECRBY balance:account_a 50
INCRBY balance:account_b 50
EXEC
# Returns nil if balance:account_a was modified by another client in the interim

Lua Scripts & Redis Functions: Complex transactional logic can be written as a Lua script executed atomically on the server via EVAL or Redis Functions, preventing any interleaved commands.

5. Latency, Throughput, and Scaling Topologies

Performance is governed by physical hardware boundaries: CPU cache, DRAM bus width, NVMe drive IOPS, and network round-trip time.

Latency Profiles Under Load

Redis (DRAM)        : [0.1ms - 0.8ms] 🚀
MongoDB (WiredTiger): [1.0ms - 5.0ms] ⚡
PostgreSQL (NVMe)   : [1.5ms - 12.0ms] 🛡️

Redis: Latency is measured in microseconds. Memory lookups complete in nanoseconds, with overall latency bounded primarily by the TCP network stack (0.2ms – 0.8ms). A single Redis instance can easily process 100,000 to 500,000 operations/sec per core.
MongoDB: Latency for indexed reads against hot cache sits in the 1ms – 5ms range. Write throughput scales horizontally across shards.
PostgreSQL: Point lookups via indexed B-Trees execute in 1ms – 4ms when pages reside in shared_buffers. Complex multi-table relational joins with aggregate window functions take 10ms – 100ms+, reflecting the CPU work of row-matching and sorting.

Scaling Strategies & Limits

PostgreSQL Scaling:
- Vertical: Scale up primary CPU and RAM.
- Read Scaling: Provision read replicas using physical streaming replication.
- Connection Scaling: Use lightweight external poolers like PgBouncer or Odyssey to eliminate PostgreSQL’s process-per-connection fork overhead.
- Horizontal Sharding: Natively complex; requires extensions like Citus (distributed PostgreSQL) or application-level partitioning.
MongoDB Scaling:
- Native Sharding: Designed natively for horizontal scale-out. A cluster consists of mongos query routers, a config server replica set, and individual data shard replica sets. Shard keys distribute chunks evenly across machines.
Redis Scaling:
- Redis Cluster: Shards data automatically across 16,384 hash slots. Keys are assigned to slots using CRC16 hashing (CRC16(key) mod 16384).
- Memory Ceiling: Constrained by RAM costs. Storing 10TB in Redis RAM is 15x–30x more expensive than storing 10TB on enterprise NVMe drives in PostgreSQL or MongoDB.

6. The Architect’s Decision Matrix

Use this matrix to match your application workload with the correct engine:

Requirement / Scenario	Recommended Engine	Primary Technical Justification
Financial Ledgers & Billing	PostgreSQL	Strict ACID, table constraints, foreign keys, decimal precision, zero silent data loss.
User Authentication & Core RDBMS	PostgreSQL	Structured relational entities, join-heavy authorization graphs, audit compliance.
Dynamic E-Commerce Product Catalogs	MongoDB	Highly polymorphic attributes across millions of items without frequent DDL schema migrations.
Real-time Session Store & Token Blacklists	Redis	Sub-millisecond lookup, automatic TTL expiration, zero disk I/O bottleneck.
Distributed Rate Limiting (Token Bucket)	Redis	Atomic `INCR` and `EXPIRE` primitives or Lua script execution across high-throughput gateways.
Real-Time Leaderboards & Gamification	Redis	Native Sorted Sets (`ZADD`, `ZRANK`, `ZREVRANGE`) execute score rankings in $O(\log N)$ time.
IoT Telemetry Ingestion (100k writes/sec)	MongoDB / Redis	Redis for write buffering or MongoDB for time-series collections with automatic bucketing.
Complex Reporting & Ad-hoc Analytics	PostgreSQL	Mature SQL optimizer, window functions (`ROW_NUMBER()`, `RANK()`), CTEs, and parallel query plans.

7. Production Blueprint: The 3-Tier Polyglot Persistence Architecture

Modern high-scale architectures (such as Stripe, Shopify, and Uber) rarely choose just one database. They combine PostgreSQL, MongoDB, and Redis into a Polyglot Persistence Blueprint:

                                  Client Request
                                        │
                                        ▼
                              ┌───────────────────┐
                              │ API Gateway / LB  │
                              └─────────┬─────────┘
                                        │
                         1. Rate Limit & Session Auth
                                        │
                                        ▼
                               ┌─────────────────┐
                               │   Redis Cache   │
                               │  (Tokens / TTL) │
                               └────────┬────────┘
                                        │
                                        ▼
                              ┌───────────────────┐
                              │ Application Core  │
                              └────┬────────────┬─┘
                                   │            │
            2. Financial Tx / CRUD │            │ 3. Polymorphic Catalog /
               Strict Relational   │            │    Event Logging
                                   ▼            ▼
                         ┌────────────┐      ┌────────────┐
                         │ PostgreSQL │      │  MongoDB   │
                         │   (SSOT)   │      │ (Catalog)  │
                         └────────────┘      └────────────┘

Component Roles in the Production Stack:

Redis (Edge & Acceleration Tier):
- Handles API rate-limiting via atomic token buckets.
- Manages user sessions, JWT revocation lists, and transient WebSocket pub/sub presence.
- Serves as a Cache-Aside layer for read-heavy PostgreSQL query results (with 5-minute TTLs and event-driven invalidation).
PostgreSQL (System of Record Tier):
- Holds the Single Source of Truth (SSOT): users, organizations, invoices, subscriptions, and financial ledgers.
- Enforces referential integrity so an order cannot exist without a valid customer or payment record.
- Utilizes JSONB for third-party webhook payloads (e.g., Stripe events).
MongoDB (Specialized Document Tier):
- Manages heterogeneous product catalogs where different categories require completely different schemas (e.g., electronics vs apparel).
- Ingests high-volume operational logs, event audits, or content management payloads where horizontal sharding is required from day one.

Conclusion & Next Steps

There is no universal database.

Choose PostgreSQL as your default relational backbone. With its rock-solid ACID engine, rich extension ecosystem (PgBouncer, PostGIS, pgvector), and native JSONB capabilities, PostgreSQL solves 85% of real-world application needs without introducing multi-system operational overhead.
Deploy Redis alongside PostgreSQL whenever you need sub-millisecond responses, distributed caching, atomic rate limiting, or temporary session state.
Adopt MongoDB when you have genuinely polymorphic, rapidly evolving document structures or when your horizontal write throughput demands native out-of-the-box cluster sharding.

Mastering schema design, relationships, and query optimization is the bedrock of database engineering.

👉 Master relational database architecture interactively on SQLMaster. Practice schema modeling, foreign key constraints, indexing strategies, and complex joins directly in your browser.