Choosing a database

Relational vs document vs wide-column vs key-value vs graph vs search vs time-series; how to pick storage from the access pattern, design keys and indexes, and answer "why not Postgres?".

The storage choice is the most consequential decision in a design and the deep dive interviewers reach for first. The right way to choose is from the access patterns: what are the queries, in what volume, with what consistency? "NoSQL because scale" is not an answer; "a wide-column store partitioned by channel id and clustered by time, because every read is 'the last N messages in a channel'" is.

Start with the access patterns

Before naming a database, list the reads and writes with their shape and rate:

• point lookup by primary key (get user 123);
• range scan within a partition (messages in channel X after time T);
• secondary lookups (orders by customer, by status);
• aggregations (count, sum, group by);
• full-text or fuzzy search;
• graph traversals (friends of friends);
• multi-row transactions (debit A, credit B, atomically).

Then match the workload to a family. Most designs end up with two or three stores: a relational or document database as the system of record, a cache, and a specialised store for search, analytics or time series.

The families

Relational (Postgres, MySQL, Aurora, Spanner)

Tables with a fixed schema, secondary indexes, joins, and ACID transactions. It is the right default for the system of record whenever data has relationships and correctness matters: users, orders, payments, inventory. A single well-tuned primary handles 5 k to 20 k writes/s and far more reads with replicas; that covers most products. The limits are write scaling past one node (you shard yourself, or use a distributed SQL like Spanner, CockroachDB, Vitess) and schema flexibility.

Say "Postgres" as the default and be ready to say when you would leave it.

Key-value (Redis, DynamoDB, etcd)

Get and put by key, sometimes with TTLs and atomic operations. Extremely fast and horizontally scalable because keys are independent. Use for caches, sessions, rate-limit counters, feature flags, and any mapping (short code → URL) where the only query is by key. DynamoDB adds durability and partition-key plus sort-key queries, which makes it a viable primary store for key-shaped workloads.

Wide-column (Cassandra, ScyllaDB, HBase, Bigtable)

Rows grouped into partitions by a partition key and ordered within the partition by clustering columns. Writes are append-only and cheap (LSM trees); reads are efficient only along the partition and clustering order. Scales writes linearly by adding nodes, with tunable consistency per query. Use for time-ordered data at high write volume: messages, events, sensor readings, feeds. The cost: you design a table per query, no joins, no ad-hoc queries, and hot partitions if the key is badly chosen (see sharding).

Document (MongoDB, Couchbase, Firestore)

JSON documents with flexible schema and secondary indexes. Good when each entity is naturally a nested object read as a unit (a product with its variants, a user profile with preferences) and the schema evolves quickly. Weaker for cross-document transactions and heavy relational queries, though modern versions support both to a degree.

Search (Elasticsearch, OpenSearch, Typesense)

Inverted indexes for full-text search, relevance ranking, faceting, and fuzzy matching. Never the system of record: index a copy fed by change events, and accept seconds of lag.

Time-series and analytics (ClickHouse, TimescaleDB, InfluxDB, BigQuery)

Column-oriented storage with compression for append-heavy, aggregation-heavy workloads: metrics, logs, click analytics. Ingest millions of rows per second and answer "group by hour, country" over billions of rows in seconds. Not for point reads or updates.

Graph (Neo4j, Neptune)

Nodes and edges with traversal queries. Use only when the queries are genuinely traversals of variable depth (fraud rings, recommendation paths). Friends-of-friends at depth 2 is usually fine in a relational or key-value store with an adjacency list.

Object storage (S3, GCS, Azure Blob)

Not a database, but where large blobs live: images, video, backups, data lake files. Eleven nines of durability, practically unlimited, cheap, and slow per request (tens of milliseconds). Keep the metadata in a database and the bytes here.

Indexes

An index is a sorted copy of one or more columns that turns a scan into a lookup. Every query in the access-pattern list should be served by the primary key or an index; a query that is not will be fine at 10 k rows and a page-long outage at 100 M. The costs are write amplification (every insert updates every index) and storage. Say which indexes the design needs and which queries they serve. Composite index order matters: (customer_id, created_at) serves "this customer's recent orders"; the reverse order does not.

Key design

In a partitioned store the key decides everything. The partition key must spread load evenly (a user id, not a country) and put together what is read together (all of one channel's messages). The sort key gives you range queries within the partition (a time-ordered id). A hot partition (one channel with 10× the traffic, one day's worth of events on one key) is the classic failure; bucket the key by time (channel_id + day) or by a hash suffix to spread it. See sharding and partitioning.

Transactions and consistency

ACID transactions are the reason to keep money, inventory and anything with invariants in a relational database. Across services or across shards you do not get them; you use idempotency, sagas and the outbox pattern instead. See distributed transactions. Distributed stores offer tunable consistency: quorum reads and writes for correctness, ONE for speed. Say which you use where.

Answering "why not Postgres?"

Give one of these and quantify it:

• Write volume. "At 200 k writes/s a single primary cannot keep up and sharding Postgres by hand is a project; Cassandra scales writes by adding nodes."
• Access shape. "Every read is by key with no joins; a key-value store is simpler and faster."
• Data shape. "Events are append-only and time-ordered; a wide-column store's LSM and clustering fit that."
• Availability. "We need multi-region active-active writes; a leaderless store gives that where Postgres gives us failover."

And the reverse, "why not NoSQL": transactions, ad-hoc queries, secondary indexes without redesign, and a team that already runs it well.

Polyglot layout for a typical design

• Postgres: users, accounts, orders (system of record).
• Redis: sessions, cache, rate limits.
• Cassandra or DynamoDB: the high-volume time-ordered stream (messages, events).
• S3: media.
• Elasticsearch: search index fed by change events.
• ClickHouse: analytics fed by Kafka.

Naming this layout with one sentence on why each exists is a complete answer to "what stores does this system use".