Database Selection Guide: PostgreSQL vs MongoDB vs Redis for Your Workload

Database selection is the architectural decision that determines which storage engine best matches a project's data model, consistency requirements, and read/write performance profile before a single record is written. See also: webhook.

That commitment lands early and compounds over time. A production system accumulates indexes, query patterns, ORM mappings, and operational runbooks tied to the engine underneath it. Migrating away later means rewriting queries, rebuilding indexes, retraining the team, and accepting downtime risk. The persistence layer decision deserves the same up-front rigour as any schema design choice. This article maps three dominant engines, PostgreSQL, MongoDB, and Redis, to the workload patterns they actually fit, using four decision axes that hold across team sizes and cloud providers. How these engines interact with communication protocols in a distributed system is covered separately in microservices communication with gRPC, REST, and message queues.

What Database Selection Actually Decides

Database selection determines more than where bytes land on disk. The choice of persistence layer locks in the query execution model, the consistency contract available to application code, and the operational overhead the team must absorb for the system's lifetime. PostgreSQL is a relational database that enforces referential integrity through foreign keys, check constraints, and multi-version concurrency control. MongoDB is a document store that maps JSON-shaped payloads directly to storage without an ORM translation step. Redis is an in-memory data store that serves reads in under a millisecond by holding the working dataset entirely in RAM. Each represents a different answer to the same four questions every system must answer before accumulating data.

The Four Decision Axes

Use these axes as a pre-commit checklist before writing the first migration file.

1. Data model shape. Relational rows with foreign key relationships and referential integrity point toward a relational database. Nested documents with per-record attribute variation point toward a document store. Flat key-value or ranked-set retrieval points toward an in-memory data store.
2. Consistency requirement. Transactions that must roll back atomically across multiple rows or tables require ACID compliance. Workloads that tolerate stale reads in exchange for lower latency can operate under eventual consistency with tunable write concern.
3. Latency SLA. Complex multi-table reporting queries tolerate 5 to 500 ms. Document retrieval by primary key or index tolerates 1 to 10 ms. Session lookup, rate-limit checks, and leaderboard reads that require sub-millisecond response need an in-memory data store.
4. Operational maturity. Managed cloud offerings (Amazon RDS, MongoDB Atlas, Redis Cloud) reduce the self-hosted operational surface but introduce egress costs and vendor lock-in. Self-hosted deployments require team fluency with replication, backup, and failover for whichever engine is chosen.

PostgreSQL: Relational Database for Structured Data Integrity

Database selection of PostgreSQL is justified when the data model has foreign key relationships and transactions must survive concurrent writes without producing anomalies. PostgreSQL is a relational database that implements ACID compliance through MVCC (multi-version concurrency control): each write creates a new row version rather than overwriting in place, so concurrent readers never block writers and partial failures never leave rows in an inconsistent intermediate state. The write-ahead log (WAL) underpins this durability guarantee, as established in Mohan et al.'s foundational work on transaction recovery (Mohan et al., ARIES (ACM SIGMOD)). The PostgreSQL Transaction Isolation documentation covers the four isolation levels available and the anomalies each prevents.

The query planner is PostgreSQL's operational lever for performance. Running EXPLAIN ANALYZE on any slow query reveals which of the 10+ join strategies the planner selected based on table statistics, and where a missing index is forcing a sequential scan. JSONB columns extend PostgreSQL into hybrid territory: teams that need occasional document flexibility without abandoning relational joins can store semi-structured payloads in a JSONB column and index specific paths with GIN indexes. This is a useful escape hatch but not a substitute for a document store at scale.

PostgreSQL fits these workloads:

• Financial transfers and ledger entries where a failed transaction must roll back all changes atomically
• Multi-table relational schemas with referential integrity constraints enforced at the database layer
• Complex reporting aggregations that the query planner can optimize with bitmap index scans and hash joins
• Any system where data correctness ranks above write throughput

PostgreSQL is a poor fit for unstructured schemas that change every sprint, for sub-millisecond cache retrieval requirements, and for write-heavy workloads above tens of thousands of inserts per second without connection pooling via PgBouncer.

PostgreSQL Scaling Patterns

The first scaling path is vertical: increase instance RAM and tune shared_buffers to 25% of total memory and effective_cache_size to 75%. The second is read replica fan-out via streaming replication. A read replica absorbs reporting and analytics queries while the primary handles writes. PgBouncer in transaction-pooling mode is a prerequisite at high concurrency because PostgreSQL forks a process per connection, and without pooling, high-concurrency workloads exhaust OS process limits before the database reaches CPU or I/O saturation. For datasets that exceed single-node limits, Citus extends PostgreSQL with horizontal sharding across worker nodes while preserving the SQL interface.

MongoDB: Document Store for Schema Flexibility and Horizontal Scale

MongoDB is a document store that persists BSON documents (a binary-encoded superset of JSON) without requiring a predefined table schema, which eliminates ORM mapping layers and lets backend teams iterate on data shapes without migration files. Schema flexibility is genuine during early development, and optional schema validation via $jsonSchema can enforce structure once the model stabilises. Tunable read/write concern levels (local, majority, linearizable) give the application control over the consistency-latency tradeoff, a dimension the IEEE's analysis of CAP theorem evolution explores in detail (Brewer, CAP Twelve Years Later (IEEE)).

Sharding through MongoDB Atlas or a self-managed sharded cluster distributes writes across shard chunks based on the shard key. The Aggregation Pipeline handles real-time analytics on event streams with operators for grouping, windowing, and lookup joins across collections. Practical use cases include product catalogs where different categories carry different attribute sets, content management systems with nested document structures, and mobile backend APIs where the document maps directly to the API response payload without serialization logic.

The MongoDB Read Concern documentation details how to configure consistency guarantees per operation. Eventual consistency is the default: MongoDB's read concern of local can return data from a secondary that has not yet applied the latest primary writes. For inventory deductions or financial operations, read concern majority is required and adds latency proportional to replication lag.

Replica Sets and Write Concern

MongoDB replica sets run with one primary and two or more secondaries. Write concern controls how many nodes must acknowledge a write before the driver returns success.

Teams that need MongoDB's document model for a use case with financial durability requirements should set w:majority globally and accept the additional latency. Choosing w:1 for write throughput in those contexts trades consistency for speed in a way that can produce data loss on failover.

Redis: In-Memory Data Store for Sub-Millisecond Retrieval

Redis stores all working data in RAM, serving reads without touching disk during normal operation and achieving a latency floor that disk-backed engines cannot match. The data structure model (strings, hashes, lists, sets, sorted sets, streams, and HyperLogLog) separates Redis from generic key-value stores and enables native operations like ZADD/ZRANGE for leaderboards and INCR/EXPIRE for rate limiting without application-layer logic. Adopting Redis for a given workload domain requires understanding both what it accelerates and where it cannot substitute for a durable database.

TTL-based expiration maps directly to session storage: set a key with an expiry equal to the session timeout, and Redis handles invalidation without a background job. The write-through cache pattern places Redis in front of PostgreSQL or MongoDB. Writes go to both Redis and the primary database simultaneously, keeping the cache warm. Cache invalidation is the operationally difficult half of this pattern: when the primary database record changes, the Redis key must be invalidated or updated atomically, or the cache will serve stale data until the TTL expires. Designing the cache invalidation strategy upfront prevents consistency bugs that compound as write volume grows.

The data eviction policy controls what happens when Redis reaches its maxmemory limit. allkeys-lru evicts the least recently used key across all keys; volatile-lru evicts only keys with a TTL set; noeviction returns an error on new writes. The wrong data eviction policy for a given workload can silently discard keys that have not yet been flushed to the primary store. See the Redis Persistence documentation for RDB and AOF configuration details.

Redis Persistence and Durability Tradeoffs

Redis Cluster distributes keys across 16,384 hash slots assigned to cluster nodes, enabling scale beyond single-node memory limits. Cross-slot operations are not supported natively, so application code must avoid multi-key commands that span slot boundaries.

Choosing the Right Database: A Workload-Driven Framework

Database selection maps cleanly to workload pattern when the four decision axes from the opening section are applied in order. The persistence layer each engine owns is distinct, and overlap between engines in a composite architecture is a feature, not a design smell, provided each engine owns a genuinely separate domain. NIST SP 500-292 frames multi-tier data architecture in cloud-native deployments as a deliberate separation of concerns across storage roles (NIST SP 500-292). Apply that framing here: each engine in a composite architecture should own exactly one tier. See also: Kubernetes Deployment.

Work through the checklist in sequence. Stop at the first axis that produces a clear answer.

1. Data model shape. Rows with foreign key relationships and enforced referential integrity: choose PostgreSQL. Nested documents with per-record attribute variation and frequent schema changes: choose MongoDB, with schema flexibility as the primary driver. Flat key-value retrieval, sorted sets, or session tokens: choose Redis as the primary engine for that data domain.
2. Consistency requirement. Transactions spanning multiple rows or tables with no tolerance for partial failures: PostgreSQL, which enforces strong ACID guarantees at the storage level. Near-ACID durability acceptable with w:majority write concern and a small replication-lag window: MongoDB. Cache-layer eventual consistency acceptable because the primary database holds the source of truth: Redis as the cache tier.
3. Latency SLA. Complex multi-table reporting at 5 to 500 ms acceptable: PostgreSQL. Document retrieval by primary key or index at 1 to 10 ms: MongoDB. Session lookup, rate-limit check, or leaderboard read requiring under 1 ms: Redis.
4. Scale trajectory. Petabyte-scale relational data with Citus horizontal sharding or read replica fan-out: PostgreSQL. Geographic distribution with MongoDB Atlas Global Clusters: MongoDB. Single-node up to available RAM, then Redis Cluster beyond that boundary: Redis.

Most production systems at 100,000 or more daily active users converge on a composite pattern. PostgreSQL holds financial records, user accounts, and orders where strong transactional guarantees on writes are non-negotiable. Redis sits in front as a write-through cache for hot-read paths. A read replica offloads reporting queries from the PostgreSQL primary. This PostgreSQL-plus-Redis composite satisfies both the durability requirement on writes and the sub-millisecond retrieval requirement on reads without forcing a single engine to serve both roles.

A second composite adds MongoDB for document-heavy domains. Understanding how these engines communicate with the rest of the service layer is covered in the microservices communication with gRPC, REST, and message queues guide. For teams building toward a full-stack architecture that incorporates these persistence choices, the Full-Stack Development Learning Path provides the surrounding context. The role of the persistence layer in deployment pipelines is addressed in the CI/CD Pipeline and Programming Languages guide.

When to Run All Three Engines Together

A three-engine architecture is justified when three genuinely distinct workload roles exist in the same system. In an e-commerce reference architecture: PostgreSQL stores orders, user accounts, and payment records because multi-step checkout transactions require atomic rollback across several tables. MongoDB stores the product catalog because product attribute sets vary per category (electronics carry different fields than apparel), and schema flexibility allows the catalog team to add attributes without migration files. Redis caches session tokens, product page data for the top 1,000 SKUs, and rate-limit counters for the checkout API. Each engine owns a distinct domain with no cross-engine joins in application code.

Running three engines without clear domain separation increases the operational surface area without proportional benefit. The decision to add a second or third engine should follow demonstrated workload pressure on the first engine, not architectural preference. Teams evaluating language-level performance implications of these backend choices will find the C++ vs Rust Speed Comparison relevant for understanding how the systems language underneath a database client affects tail latency. See also: Rust.

Frequently Asked Questions