db-choice-matrix

DB Choice Matrix — Workload → Pick

Ngắn gọn cho lúc đang spec dự án mới.

Tags: attachment cheatsheet decision Liên quan: Tuan-10-Redis-Mastery · Tuan-11-DynamoDB-Cassandra · Tuan-14-OLAP-Columnar-ClickHouse

---

1. Master Decision

flowchart TD
    A[Workload?] --> B{OLTP / OLAP / KV / Search / Vector?}

    B -->|OLTP transactional| C{Single region?}
    C -->|Yes <10TB| C1[Postgres]
    C -->|Multi-region / >10TB| C2[CockroachDB / TiDB / Aurora DSQL]

    B -->|OLAP analytics| D{Latency sensitive?}
    D -->|Sub-second dashboards| D1[ClickHouse]
    D -->|Minutes OK| D2[BigQuery / Snowflake / Iceberg+Trino]
    D -->|Local embedded| D3[DuckDB]

    B -->|KV / cache / session| E{Persistence?}
    E -->|In-memory OK lose| E1[Redis / Valkey]
    E -->|Durable| E2[DynamoDB / Cassandra / FoundationDB]

    B -->|Document flexible| F{Cardinality + access pattern?}
    F -->|Predictable| F1[Postgres JSONB]
    F -->|Truly polymorphic| F2[MongoDB]

    B -->|Search keyword + fuzzy| G[Elasticsearch / OpenSearch]

    B -->|Vector AI / RAG| H{Vector count?}
    H -->|<10M| H1[pgvector]
    H -->|>10M| H2[Qdrant / Pinecone / Milvus]

    B -->|Graph traversal >3 hops| I[Neo4j / Memgraph]

    B -->|Time-series sensor / metrics| J{Cardinality?}
    J -->|Low| J1[Prometheus / VictoriaMetrics]
    J -->|High| J2[TimescaleDB / InfluxDB v3]

    style C1 fill:#c8e6c9
    style H1 fill:#c8e6c9
    style F1 fill:#c8e6c9

2. By Use Case

Use Case	Pick	Reason
Web app, <10TB, single region	Postgres	Boring tech, ACID, ecosystem
SaaS multi-tenant 100-10K tenants	Postgres + RLS	Pool + isolation
Multi-region active-active	Aurora DSQL / CockroachDB	Strong consistency global
Massive write throughput >100K/s	Cassandra / ScyllaDB / TiKV	Sharded writes
Cache, session, leaderboard	Redis / Valkey	µs latency, atomic ops
Queue with at-least-once	Redis Streams / Kafka	Persisted log
Job queue lightweight	Postgres SKIP LOCKED	No new system
Full-text search >1M docs	Elasticsearch / OpenSearch	Inverted index, ranking
Full-text search <1M docs	Postgres FTS + GIN	Saves a system
Real-time analytics dashboards	ClickHouse	Sub-second OLAP
Data warehouse / BI	BigQuery / Snowflake	Serverless, SQL
Open lakehouse	Iceberg + Trino / Spark	Multi-engine, cheap S3
Local notebook analytics	DuckDB	Embedded, fast
Time-series metrics	VictoriaMetrics / Prometheus	Compressed, native PromQL
Time-series business (IoT)	TimescaleDB	SQL + retention + downsample
RAG <10M vectors	pgvector	Reuse Postgres
RAG enterprise scale	Qdrant / Pinecone	Filtered search at scale
Knowledge graph / fraud	Neo4j	Graph traversal
Edge / mobile / local	SQLite (+ Litestream)	In-process, simple
Document store flexible schema	MongoDB	Native doc
Polyglot persistence	Multiple via CDC (Debezium)	Postgres as source of truth

3. Common Stacks 2024-2026

Startup MVP

Postgres (primary) + Redis (cache) + S3 (files) + Stripe

SaaS B2B at scale

Postgres (per-tier pools, RLS for small) + PgBouncer + Redis + ES + Kafka (CDC)

Real-time analytics product

App → Kafka → Flink → ClickHouse + Iceberg (cold) → BI tools
       └─→ Postgres (OLTP)

AI / RAG product

Postgres (source of truth) + Qdrant (vectors) + ES (BM25) +
Redis (semantic cache) + LLM API

Multi-region e-commerce

Aurora DSQL OR CockroachDB (primary) + ElastiCache + ES global cluster +
DynamoDB Global Tables (carts) + S3 multi-region

4. Cost Sanity Check (2024 ballpark)

Workload	Self-host	Managed (AWS)
100GB Postgres OLTP	$200/mo (EC2 + EBS)	$300/mo RDS
1TB ClickHouse	$500/mo	$1500/mo Aiven
10M Redis cache	$100/mo	$400/mo ElastiCache
100GB ES	$400/mo	$1000/mo OpenSearch Service
1TB DynamoDB on-demand	N/A	$250/mo + IO
10M vectors Qdrant	$200/mo	$400-2000/mo Qdrant Cloud
1TB Iceberg S3	$25/mo storage	$25/mo storage + compute

5. Defaults — When in Doubt

graph LR
    Default[Don't know yet?] --> Postgres[Use Postgres]
    Postgres --> Pain[Production pain]
    Pain --> Specific[Add specific tool for specific reason]

    style Postgres fill:#c8e6c9

Postgres handles: OLTP, JSONB, full-text, geo (PostGIS), time-series (decent), vector (pgvector), graph (recursive CTE), queue (SKIP LOCKED), pub/sub (LISTEN/NOTIFY).

When to leave Postgres:

• Specific scale/latency need PROVEN
• Specific feature mandatory (e.g., true distributed transactions)
• Team capable of operating extra system

Default to Postgres is rarely wrong for <10 engineers + <1TB.

6. Migration Cost Reality

graph LR
    A[Add new DB] --> B[Code: 2-4 weeks]
    A --> C[Migration: 1-3 months]
    A --> D[Operational: ongoing]
    A --> E[Team learning: ongoing]

    F[Total cost] -.->|easily 6-12 months engineer time| Final

→ Always evaluate: “can current DB handle this with optimization?” before adding new one.

---

Cập nhật: 2026-05-16