8. Choosing Data Stores

How to decide which database (or databases) a system needs, from the numbers in Capacity Estimation. General background: SQL vs NoSQL, Modern Database Trends, ACID and CAP theorem and its implications.

The default

Start with PostgreSQL (or MySQL) unless a requirement clearly rules it out.

• Strong consistency, transactions, joins, constraints, mature tooling, and it scales further than most people expect.
• Add other stores only when a specific number or access pattern demands it. Each extra database is more to run, secure, back up and keep in sync.

Ask these questions first

flowchart TD
    S[Start] --> B{Large binary files?}
    B -- yes --> OBJ[Object storage + metadata DB]
    B -- no --> Q{Main access pattern?}
    Q -- joins, transactions, ad-hoc queries --> REL{Fits one node?}
    REL -- yes --> PG[PostgreSQL / MySQL]
    REL -- no, huge relational --> NS[Sharded SQL or NewSQL Vitess, CockroachDB, Spanner]
    Q -- lookup by key at huge scale --> KV[Key-value / wide-column DynamoDB, Cassandra]
    Q -- flexible documents --> DOC[MongoDB / document DB]
    Q -- time-stamped metrics --> TS[Time-series DB]
    Q -- text search --> ES[Elasticsearch / OpenSearch]
    Q -- similarity of embeddings --> VEC[Vector index]
    Q -- relationships and paths --> GR[Graph DB]
    Q -- big analytical scans --> OLAP[Column store / warehouse ClickHouse, BigQuery]
    Q -- sub-ms hot data --> RED[Redis cache]

The families at a glance

Per-family detail: Key-value stores, Column-family stores, Document databases, Graph databases.

When to reach for Cassandra (or similar wide-column stores)

Cassandra spreads data over many nodes with no single primary, writes append to a log (LSM tree) so writes are very fast, and every node can accept reads and writes.

Good fit when most of these are true - Write-heavy: tens of thousands to millions of writes per second. - Lots of data: many TB to PB, growing continuously. - Access by known keys: "latest N items for user X", "all readings for device Y today". - Time-ordered per key (feeds, messages, events, sensor data). - Always available, multi-region: tolerate node or data-centre loss with no downtime. - You can accept eventual or tunable consistency.

Poor fit when - You need joins, ad-hoc queries, or many secondary indexes. - You need multi-row ACID transactions. - The dataset is small (a single Postgres is simpler and cheaper). - Your queries change often (you model tables per query, so changes mean new tables). - You lack operations skills (repairs, compaction, tombstones need care). Consider managed options (DynamoDB, Astra, Keyspaces).

-- Cassandra (CQL): model the table around the query "latest photos for a user"
CREATE TABLE photos_by_user (
  user_id   bigint,
  taken_at  timeuuid,
  photo_id  bigint,
  url       text,
  caption   text,
  PRIMARY KEY ((user_id), taken_at)      -- partition key, clustering key
) WITH CLUSTERING ORDER BY (taken_at DESC);

SELECT * FROM photos_by_user WHERE user_id = 42 LIMIT 20;   -- one partition, fast

Time-series databases

Use when data is append-only, timestamped, queried by time range, and old data should be rolled up or expired.

$ sensors              = 10,000 devices x 1 reading / s  = 10,000 writes / s
$ per day              = 10,000 x 86,400 = 864M points   (~17 GB at 20 B each)
$ typical queries      = "average per minute, last 24 h, for device 7"

Vector databases

Store embeddings (arrays of numbers produced by an ML model) and find the nearest ones: semantic search, "similar photos", recommendations, retrieval for AI assistants (RAG).

$ "red sports car" -> embedding model -> [0.12, -0.8, ... 768 numbers]
$ query: nearest 10 vectors by cosine similarity (approximate)

-- pgvector
CREATE EXTENSION vector;
ALTER TABLE photos ADD COLUMN embedding vector(768);
CREATE INDEX ON photos USING hnsw (embedding vector_cosine_ops);
SELECT id FROM photos ORDER BY embedding <=> '[0.12,-0.8,...]' LIMIT 10;

Other quick calls

• Cache / counters / leaderboards / rate limits / sessions: Redis (rate limiting).
• Full-text search, faceted filters, log search: Elasticsearch or OpenSearch, fed from the primary DB. It is an index, not the source of truth.
• Graph traversal ("friends of friends", fraud rings): graph DB, or recursive SQL for small graphs.
• Reporting over billions of rows: column store or warehouse, loaded from the OLTP database (CDC/ETL, see Advanced Db Concepts).
• Files and media: always object storage, never database BLOBs at scale.

Polyglot persistence: the photo app

One system, several stores, each for what it does best.

flowchart TD
    API[API servers]
    API --> PG[(PostgreSQL users, accounts, billing)]
    API --> CS[(Cassandra / DynamoDB photo metadata and feeds, 36 TB)]
    API --> RD[(Redis hot metadata cache, counters)]
    API --> OBJ[(Object storage photos, 90 PB)]
    CS -. change events .-> ES[(OpenSearch captions and tags)]
    CS -. change events .-> VEC[(Vector index similar photos)]
    API --> TS[(Time-series DB metrics and uploads per minute)]

Keep stores in sync with events (queue or change-data-capture), and treat all but one as derived data that can be rebuilt.

Mistakes to avoid

• Picking a database because it is fashionable ("we need NoSQL for scale") with 10 GB of data.
• Using one database for everything or five databases for a prototype.
• Treating a search index or cache as the source of truth.
• Choosing Cassandra and then needing joins and ad-hoc reports.
• Ignoring consistency: money and inventory belong in a transactional store.
• Ignoring operations: who backs it up, upgrades it, restores it?
• Not testing with realistic data volumes and a load test before committing.

Quick cheat sheet