Stop Picking Databases by Popularity
Every few years, a database becomes the default choice. For a while it was MongoDB ("it's web scale!"). Then PostgreSQL became the darling of the developer community. Both are excellent databases — for the right use case. The problem is reaching for one tool regardless of the problem.
Here's how I think about database selection: start with your access patterns, data relationships, and consistency requirements. The database choice follows from those constraints, not from Hacker News sentiment.
PostgreSQL: The Swiss Army Knife
PostgreSQL handles an absurdly wide range of use cases. Relational data with complex joins? Obviously. JSON documents? Yes, with JSONB columns and GIN indexes. Full-text search? Built-in with tsvector. Time-series data? Reasonable with proper partitioning or the TimescaleDB extension. Geospatial queries? PostGIS makes it one of the best spatial databases available.
Where PostgreSQL Excels
Strong consistency with ACID transactions. If you're building anything involving money, user accounts, or inventory — where correctness matters more than raw speed — PostgreSQL is the safe choice.
Complex queries with multiple joins, aggregations, window functions, CTEs, and recursive queries. PostgreSQL's query planner is remarkably good at optimizing these. I've seen it outperform dedicated analytics databases on moderately complex queries over datasets up to 50-100GB.
-- Window functions + CTEs make complex analytics readable
WITH monthly_revenue AS (
SELECT
customer_id,
date_trunc('month', created_at) AS month,
SUM(amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY customer_id, date_trunc('month', created_at)
)
SELECT
customer_id,
month,
revenue,
LAG(revenue) OVER (PARTITION BY customer_id ORDER BY month) AS prev_month,
revenue - LAG(revenue) OVER (PARTITION BY customer_id ORDER BY month) AS growth
FROM monthly_revenue
ORDER BY customer_id, month;Where PostgreSQL Struggles
Write-heavy workloads at extreme scale. PostgreSQL's MVCC (Multi-Version Concurrency Control) creates dead tuples on every update, requiring regular vacuuming. At 50,000+ writes per second, vacuum can struggle to keep up, and your table bloats.
Horizontal sharding isn't native. Citus extension helps, and native partitioning covers some use cases, but if you need true distributed writes across dozens of nodes, you're fighting the architecture.
MongoDB: Documents Done Right
MongoDB stores data as BSON documents — binary JSON with a richer type system. The flexible schema means different documents in the same collection can have different fields.
Where MongoDB Fits
Content management systems, product catalogs, user profiles — anywhere your data is naturally hierarchical and you'd be doing a lot of JOINs in SQL to reconstruct what's essentially one object.
// A product document — all related data in one place
{
"_id": ObjectId("64a1b2c3d4e5f6a7b8c9d0e1"),
"name": "Running Shoes Pro X",
"brand": "SpeedRunner",
"price": { "amount": 129.99, "currency": "USD" },
"variants": [
{ "size": 10, "color": "black", "sku": "SRP-BK-10", "stock": 45 },
{ "size": 10, "color": "white", "sku": "SRP-WH-10", "stock": 12 },
{ "size": 11, "color": "black", "sku": "SRP-BK-11", "stock": 33 }
],
"specs": {
"weight_grams": 280,
"material": "mesh upper, EVA midsole",
"drop_mm": 8
},
"reviews_summary": { "avg_rating": 4.3, "count": 847 }
}MongoDB's aggregation pipeline is powerful for analytics on document data. It's not SQL, but it handles grouping, filtering, unwinding arrays, and joining collections (via $lookup) reasonably well.
Horizontal scaling is where MongoDB genuinely outshines PostgreSQL. Sharding is built into the core product, and the mongos router handles shard-aware query routing transparently. For multi-terabyte datasets spread across dozens of nodes, MongoDB's operational model is more mature.
Where MongoDB Falls Short
Multi-document transactions exist since MongoDB 4.0, but they're slower and more limited than PostgreSQL transactions. If your business logic frequently needs to update records across multiple collections atomically, you'll feel the friction.
Lack of schema enforcement (unless you use JSON Schema validation) means data quality issues creep in over time. That "flexible schema" advantage becomes a liability when different parts of your application store the same concept with different field names or types.
Redis: The Speed Demon
Redis is an in-memory data structure store. Everything lives in RAM, which means sub-millisecond reads and writes. It supports strings, lists, sets, sorted sets, hashes, streams, and more. Think of it less as a database and more as a programmable data structure server.
Primary Use Cases
Caching layer in front of your primary database. Store query results, session data, computed values. A Redis GET takes about 0.1ms compared to 1-50ms for a database query.
Rate limiting with sliding window counters. Redis's atomic operations (INCR, EXPIRE) make this trivial to implement correctly.
Session storage for web applications. Fast reads, automatic TTL-based expiration, and the ability to inspect sessions for debugging.
Pub/Sub and Streams for real-time features. Redis Streams (added in 5.0) provide a log-like data structure similar to Kafka topics but without the operational overhead of running a Kafka cluster.
Leaderboards and counters using sorted sets. ZADD and ZRANGE give you O(log N) insertion and O(log N + M) range queries — fast enough for real-time gaming leaderboards with millions of entries.
Redis Limitations
Data must fit in memory. Yes, Redis can persist to disk (RDB snapshots and AOF), but it's not designed as a disk-based database. If you have 500GB of data and 64GB of RAM, Redis isn't your primary store.
Query capabilities are limited. No complex filtering, no joins, no aggregations beyond what the data structures natively support. Redis is a building block, not a query engine.
The Decision Matrix
Here's how I'd map common requirements to databases:
- Complex relational data with ACID → PostgreSQL
- Document-oriented data, horizontal scaling needed → MongoDB
- Sub-millisecond reads, caching, real-time counters → Redis
- Time-series metrics at scale → TimescaleDB (PostgreSQL extension) or InfluxDB
- Full-text search as primary feature → Elasticsearch or Meilisearch
- Graph relationships (social networks, recommendations) → Neo4j
- Wide-column analytics (billions of rows, few queries) → ClickHouse
Most applications need a primary database (PostgreSQL or MongoDB) plus Redis for caching and real-time features. That two-database setup covers 90% of use cases. Only add specialized databases when you have a clear need that your existing stack can't meet without significant compromise.
And honestly — if you're unsure, start with PostgreSQL. It's the safest default and you can always add specialized stores later as bottlenecks emerge.