Choosing a Datastore

Start from the questions, not the schema

Before naming any product, write down:

1. What reads happen, and how often? By primary key? By range? By a secondary attribute? Full-text? Aggregations across millions of rows?
2. What writes happen? Append-only? Update-in-place? Heavy deletes?
3. How much data, growing how fast? 10 GB and 10 MB/day is a different world from 10 TB and 500 GB/day.
4. What consistency does each operation need? (See CAP — the answer is per-operation.)
5. What's the latency budget, and for which percentile?

The single most common design failure is picking a store optimised for a pattern you don't have, while making the pattern you do have expensive.

The families

Relational (Postgres, MySQL) — rows, schemas, joins, transactions. Strong at: ad-hoc queries you didn't anticipate, multi-entity invariants, anything where correctness matters. Weak at: horizontal write scaling, and very deep hierarchical data.

Key-value (Redis, DynamoDB, Memcached) — get and put by key. Strong at: extreme throughput at predictable latency, caching, sessions, counters, rate limiters. Weak at: anything requiring a query you didn't design the key for.

Document (MongoDB, DocumentDB, Postgres JSONB) — self-contained nested documents. Strong at: data that's read and written as a whole, and genuinely variable shapes. Weak at: relationships across documents, and consistency spanning several of them. The common mistake is using documents for relational data and then reimplementing joins in application code.

Wide-column (Cassandra, ScyllaDB, HBase, Bigtable) — rows partitioned by key, sorted by a clustering key. Strong at: enormous write throughput, time-ordered data per entity, linear scaling, multi-region active-active. Weak at: ad-hoc queries — you must know your queries before you design the table, and adding a new one may mean a new table.

Graph (Neo4j, Neptune) — nodes and edges as first-class citizens. Strong at: multi-hop traversal — "friends of friends who work at companies in this city." Weak at: everything else, and the operational burden is real. Worth it only when traversal is the product; a handful of joins does not need a graph database.

Time-series (Timescale, InfluxDB, Prometheus) — timestamped measurements. Strong at: recent-window queries, downsampling, retention policies, compression (often 10–20×). Weak at: updates and non-temporal queries.

Search (Elasticsearch, OpenSearch, Typesense) — inverted indexes with relevance ranking. Strong at: full-text, faceting, fuzzy matching. Weak at: being a source of truth — treat it as a derived index you can always rebuild.

Object storage (S3, R2, GCS) — blobs by key. Strong at: large immutable files at very low cost, and effectively infinite durability. Weak at: small objects, mutation, querying. The right home for images, video, backups, and data-lake files.

The decision matrix

The gravity of Postgres

A deliberate bias worth stating: start relational, and start with Postgres, unless you have a specific reason not to. It covers an unreasonable amount of ground:

• JSONB with indexing, for genuinely schemaless fields
• Full-text search, good enough well past the point most teams reach for Elasticsearch
• Arrays, ranges, and rich types
• PostGIS for geospatial — the best in the business, relational or not
• LISTEN/NOTIFY for lightweight pub/sub
• Partitioning, and TimescaleDB for time-series
• Logical replication for CDC out to other systems

You can serve a document workload, a search workload, and a time-series workload from one Postgres instance for a long time. And "one system" is a feature, not a compromise:

Keeping several stores in sync

Once you have more than one, you have a consistency problem — and the naive solution is a bug.

Dual writes are broken. Writing to Postgres and then to Elasticsearch in the same request handler fails the moment the second write errors, or the process dies between them, or two concurrent updates apply in different orders in each store. You will silently drift.

The correct patterns:

Change data capture. Read the database's replication log (Debezium, Postgres logical replication) and project changes into the other stores. The database's log is the single ordered source of truth, so downstream systems converge. This is the standard answer.

Transactional outbox. In the same transaction as your business write, insert a row into an outbox table. A separate process reads the outbox and publishes. Atomic with the write, since it's the same transaction — no dual-write race.

Both give you eventual consistency between stores, which is what you should expect and design for. If two stores must be updated atomically, that's a signal they should be one store.

Worked example: the ride-hailing service

Continuing the example from estimation:

Note the shape of that answer: Postgres is the source of truth; everything else is derived, ephemeral, or a specialised index. That structure — one authoritative store plus derived projections — is worth far more in an interview than a list of product names.

What to take away

• Access patterns choose the datastore. Naming a product first is backwards.
• Eight families, each excellent at one shape of question and poor at the others.
• Postgres covers a startling range; the default should be to start there and justify leaving.
• Every extra datastore costs operational surface, not licence fees. Add on measured need.
• Dual writes are a bug. Use CDC or a transactional outbox.
• Aim for one source of truth plus derived, rebuildable projections.