Database Selection — Not Just "SQL vs NoSQL"Database Selection: SQL vs NoSQL, OLTP vs OLAP, ACID vs BASE

The Problem

When an interviewer asks "why MongoDB?", a senior engineer can blow it — because the right answer isn't "because it's fast," it's "because my access pattern is reads aggregated by user_id, writes outweigh reads, and the schema changes often." Database selection isn't a tech religion; it's constraint derivation. Data model, access pattern, consistency requirements, scale, and operational cost — five axes decide everything.

The counter-examples are plentiful. Uber migrated from Postgres to MySQL in 2016 (Postgres replication protocol's write amplification), then in 2020 built Schemaless on top of MySQL as a KV store. Pinterest moved from MongoDB to sharded MySQL in 2012. Discord migrated message storage from MongoDB to Cassandra in 2017 (1B messages/day), then to ScyllaDB in 2023. Stripe still runs MongoDB for the core ledger — backed by a meticulous application-layer consistency layer. There is no best database, only the closest fit.

Requirements and Constraints (the interview opener)

• Access pattern: point lookup by primary key, secondary-index scan, range query, multi-table join, or full-table aggregation? This dictates the database type.
• Read/write ratio and QPS: 100k QPS of writes won't fit on a single Postgres (ceiling around 50k); you need Cassandra, DynamoDB, or sharded MySQL.
• Data volume and growth: 100GB? 10TB? 1PB? Above 10TB, single-box OLTP databases are out — you must shard or move to distributed SQL (CockroachDB, TiDB, Spanner).
• Consistency requirements: bank balance (strong) vs social like (eventual).
• Latency requirements: p99 1ms (Redis), 10ms (DynamoDB), 100ms (Postgres + index), seconds (OLAP).
• Schema stability: still iterating fast (NoSQL flexible) or stable (SQL's structure is safer)?
• Operational capacity: does the team know how to tune Postgres or Cassandra? Managed (Aurora, DynamoDB) or self-hosted?

High-Level Architecture (Typical Data Stack)

graph LR
    APP["Application
API / Service"]
    CACHE["Redis
KV cache · sessions · leaderboards"]
    OLTP["OLTP
Postgres / MySQL · DynamoDB"]
    SEARCH["Search
Elasticsearch · OpenSearch"]
    QUEUE["CDC / Kafka
binlog → downstream"]
    DWH["Data Warehouse
Snowflake / BigQuery · OLAP"]
    LAKE["Data Lake
S3 + Iceberg · cold data"]

    APP --> CACHE
    APP --> OLTP
    APP --> SEARCH
    OLTP -->|binlog| QUEUE
    QUEUE --> DWH
    QUEUE --> SEARCH
    QUEUE --> LAKE

    classDef app fill:#1a2530,stroke:#64c8ff,color:#e8eef5
    classDef cache fill:#1a1a30,stroke:#ffb450,color:#e8eef5
    classDef oltp fill:#0e2030,stroke:#5eead4,color:#e8eef5
    classDef olap fill:#2a1530,stroke:#ff7ab6,color:#e8eef5
    class APP app
    class CACHE cache
    class OLTP,SEARCH oltp
    class QUEUE,DWH,LAKE olap

A modern backend is usually polyglot: OLTP for transactions, OLAP for analytics, search for search, cache for reads

Key Technical Points

1. SQL vs NoSQL: Not a Binary — Pick a Data Model

Principle: "NoSQL" is a poor label. It's an umbrella for four genuinely different things:

2. OLTP vs OLAP: Row Storage vs Columnar, the Real Difference

Principle: transactional and analytical systems have inverted access patterns:

• OLTP: each request reads/writes a few rows by primary key, write-heavy, low-latency (ms), ACID. "Get user 123's orders," "decrement inventory."
• OLAP: scans huge row sets, read-heavy, long-running (seconds to minutes), aggregations. "GMV by region for the last 30 days."

This determines the storage layout. OLTP uses row storage (all fields of a row together, optimized for fetching a whole row). OLAP uses columnar storage (all values of one column together, optimized for aggregation, high compression, and SIMD).

graph LR
    subgraph Row["Row Store (Postgres / MySQL)"]
        R1["[1, Alice, 30, US]"]
        R2["[2, Bob, 25, UK]"]
        R3["[3, Carol, 28, JP]"]
    end
    subgraph Col["Columnar Store (BigQuery / Snowflake)"]
        C1["id: [1, 2, 3]"]
        C2["name: [Alice, Bob, Carol]"]
        C3["age: [30, 25, 28]"]
        C4["country: [US, UK, JP]"]
    end
    Row -->|"SELECT * WHERE id=2 ✅"| Q1["fast point lookup"]
    Col -->|"AVG(age) GROUP BY country ✅"| Q2["fast aggregation · 10x compression"]
    classDef row fill:#0e2030,stroke:#5eead4,color:#e8eef5
    classDef col fill:#2a1530,stroke:#ff7ab6,color:#e8eef5
    class Row,R1,R2,R3 row
    class Col,C1,C2,C3,C4 col

# Why columnar aggregations are fast: only read the column you need
# Table with 100 columns, 1B rows
# OLTP row store running SELECT AVG(salary):
#   scans 1B × 100 columns of data = massive I/O
# OLAP columnar running SELECT AVG(salary):
#   reads only the salary column (1B × 1 column), compressed to ~1GB
#   + SIMD vectorized execution → sub-second response

3. ACID vs BASE: Two Philosophies of Consistency

ACID (Atomicity, Consistency, Isolation, Durability): the traditional RDBMS promise. BASE (Basically Available, Soft state, Eventual consistency): the NoSQL compromise.

But since the 2010s, the line has been blurring:

• Spanner / CockroachDB / TiDB: distributed SQL using Paxos/Raft + TrueTime to give ACID with horizontal scale.
• DynamoDB 2018+: supports transactions (TransactWriteItems / TransactGetItems), up to 100 items / 4MB.
• MongoDB 4.0+: multi-document transactions (10× slower than single-document, but they exist).

# Postgres SERIALIZABLE transaction (highest isolation)
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- if balance >= 100, debit
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;
-- ⚠️ Serializable raises SerializationFailure on concurrent conflict
-- the application must catch and retry (backoff + jitter)

4. A Decision Tree: Postgres / DynamoDB / Cassandra / Redis

Principle: four mainstream options cover 90% of scenarios. Here's a practical decision path.

graph TD
    Start{"Need transactions and joins?"}
    Start -->|"Yes"| SQL_q{"Data > 1TB?"}
    Start -->|"No"| KV_q{"Latency < 1ms?"}

    SQL_q -->|"No"| PG["✅ Postgres
transactions · complex queries · JSONB"]
    SQL_q -->|"Yes"| Dist_q{"Global multi-region?"}

    Dist_q -->|"Yes"| Span["✅ Spanner / CockroachDB
cross-region ACID"]
    Dist_q -->|"No"| TiDB["✅ TiDB / Vitess
distributed MySQL"]

    KV_q -->|"Yes"| Redis["✅ Redis
in-memory KV · cache · leaderboards"]
    KV_q -->|"No"| Scale_q{"> 50k write QPS & massive data?"}

    Scale_q -->|"Yes"| Wide_q{"Managed or self-hosted?"}
    Scale_q -->|"No"| PG2["✅ Postgres
usually enough"]

    Wide_q -->|"Managed"| DDB["✅ DynamoDB
zero-ops · pay per access"]
    Wide_q -->|"Self-hosted"| Cass["✅ Cassandra / Scylla
multi-region write · BASE"]

    classDef sql fill:#0e2030,stroke:#5eead4,color:#e8eef5
    classDef kv fill:#1a1a30,stroke:#ffb450,color:#e8eef5
    classDef wide fill:#2a1530,stroke:#ff7ab6,color:#e8eef5
    classDef q fill:#1a2530,stroke:#64c8ff,color:#e8eef5
    class PG,PG2,Span,TiDB sql
    class Redis kv
    class DDB,Cass wide
    class Start,SQL_q,KV_q,Dist_q,Scale_q,Wide_q q

Scaling Up: What to Do After Growth

• Single-box Postgres can't take it: first add read replicas (read/write split) → vertical sharding (split by domain: user / order / inventory) → horizontal sharding (Citus / Vitess / application-layer).
• Cross-domain joins: CDC (Debezium pulling binlog) → Kafka → materialize to search/warehouse; application-layer joins become downstream consumers.
• OLAP slowing OLTP down: never run analytical SQL on the primary. Use a read replica or CDC to an independent OLAP system (Snowflake, ClickHouse).
• Multi-region: single-region OLTP (avoiding distributed transactions) with async replication to other regions; or move to Spanner / CockroachDB (global ACID with 100ms+ write latency).
• Cold data archival: hot data in OLTP (last 90 days), cold data on S3 + Iceberg / Parquet, queried via Athena / Trino.

Common Pitfalls (Even Senior Engineers Slip Here)

Sample Interview Questions

1. Designing Twitter — what stores the timeline? Why not all MySQL?
2. Postgres or MySQL? Name three real differences (don't say "about the same").
3. When would you pick Cassandra over DynamoDB?
4. What does the "S" (soft state) in BASE mean? Give an example.
5. From 100k to 100M users, how does the database evolve? List phases and each phase's bottleneck.
6. HTAP databases (TiDB) vs the "OLTP + CDC to OLAP" pattern — which do you pick?

Key Resources

• Designing Data-Intensive Applications, Ch. 2–3 (Kleppmann): the canonical treatment of data models and storage engines.
• "Dynamo: Amazon's Highly Available Key-value Store" (SOSP 2007) and "Spanner: Google's Globally Distributed Database" (OSDI 2012): the two milestones of NoSQL and distributed SQL.
• Discord engineering blog, "How Discord Stores Trillions of Messages" (2023): the MongoDB → Cassandra → ScyllaDB migration story, with latency data.

English Summary

Database selection is constraint derivation, not technology religion. Five axes determine the choice: data model (relational / KV / document / wide-column / graph / search / time-series), access pattern (point lookup vs range scan vs aggregation vs join), consistency (ACID vs BASE), scale (QPS, data size, geo distribution), and operational cost. Default to Postgres unless you have a specific reason — its versatility (JSONB, full-text, geo, time-series extensions) covers 80% of needs. Reach for DynamoDB / Cassandra when write throughput exceeds ~50k QPS or you need horizontal scale-out; Redis when sub-ms latency is required; columnar warehouses (Snowflake / BigQuery / ClickHouse) when analytical aggregation dominates. Modern stacks combine multiple stores via CDC pipelines: OLTP handles transactions, search handles full-text, warehouse handles analytics.

Going Deeper (click to expand)