SQL vs NoSQL: How I Choose the Right Database for Each Service

My decision checklist

Choose PostgreSQL when:

You need ACID transactions. Financial data, inventory management, anything where partial writes are catastrophic.
Your data has relationships. Users have orders, orders have items, items belong to categories. JOINs are PostgreSQL's strength.
You need complex queries. Aggregations, window functions, CTEs, full-text search — PostgreSQL handles all of these natively.
Your schema is stable. If you know the shape of your data upfront, a relational schema gives you validation and documentation for free.

Choose MongoDB when:

Your schema is genuinely flexible. CMS content, user-generated forms, product catalogs where each product has different attributes.
You read entire documents. If every read returns the whole object and you rarely JOIN across collections, the document model avoids unnecessary joins.
You need horizontal scaling out of the box. MongoDB's sharding is simpler to set up than PostgreSQL's partitioning or Citus.

Choose Redis when:

Sub-millisecond latency. Session storage, rate limiting counters, real-time leaderboards.
Ephemeral data. Caches, temporary locks, pub/sub channels.
Simple data structures. If your data fits in strings, hashes, sets, or sorted sets, Redis is unbeatable.

Real examples from my systems

User service: PostgreSQL. Users, roles, permissions, organizations — deeply relational. Every query involves JOINs. Transactions ensure a user cannot be in two conflicting roles simultaneously.

Product catalog: MongoDB. Electronics have specs like CPU and RAM. Clothing has size and material. Each category has different fields. A flexible schema avoids a 200-column table or an EAV anti-pattern.

Session store: Redis. Sessions are ephemeral, read on every request, and need sub-millisecond latency. A hash per session with a TTL is the simplest, fastest solution.

Analytics pipeline: PostgreSQL with TimescaleDB. Time-series event data with complex aggregations. TimescaleDB extends PostgreSQL with hypertables for time-series performance while keeping standard SQL.

The mistakes I have seen

1. MongoDB for everything

Startups pick MongoDB because "no schema means we move fast." Six months later they are writing application-layer JOINs, fighting data inconsistency, and wishing they had foreign keys.

2. PostgreSQL for caching

Querying PostgreSQL for data that changes every 5 minutes and is read 1000 times per second. Put it in Redis. Your database will thank you.

3. Premature polyglot persistence

Do not start with five databases. Start with PostgreSQL. When a specific access pattern clearly does not fit, add a specialized store for that use case. Most apps can run on PostgreSQL alone for years.

PostgreSQL (source of truth)
  → Redis (cache + sessions)
  → Elasticsearch (search, if needed)
  → MongoDB (only if you have genuinely flexible schemas)

PostgreSQL is the default. Everything else is added when there is a clear, measurable reason. Not before.