Updated 24 October 2026

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?".

Try it yourself, in a playground:Choosing a shard keyComposite index column order

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".

Connection management

A detail that decides whether a design survives: databases handle connections, not requests. Postgres forks a backend per connection and falls over somewhere between a few hundred and a couple of thousand; a serverless fleet that opens one connection per instance will exhaust it long before the CPU is busy.

The answer is a pooler (PgBouncer, RDS Proxy, or the driver's own pool) in transaction mode, with a pool size set from the database's capacity rather than the fleet's size: a common rule is cores × 2 + effective spindles, so a few dozen, not a few thousand. Say this when the design has many small stateless workers; it is a mistake interviewers have seen in production.

Read replicas and where they fail you

Adding read replicas is the first scaling move for a read-heavy relational workload, and it comes with two catches worth naming: replication lag makes read-your-writes fail (route the user's reads to the primary for a short window after their write), and replicas do nothing for write throughput or for table size. When writes or size are the problem, the next moves are partitioning the hot tables, moving cold data out, and only then sharding.

Migrations at scale

Schema changes on a large table are an operational question, and interviewers ask it:

• Adding a nullable column with no default is instant on modern Postgres and MySQL; adding a column with a volatile default rewrites the table.
• Creating an index must be CONCURRENTLY / online, or the table locks.
• Backfills run in batches with a sleep between them, and are restartable from a cursor.
• Column renames and type changes are done as expand-migrate-contract: add the new column, dual-write both, backfill, switch reads, stop writing the old, drop it. Four deploys, never one.

A decision path you can say out loud

1. Does the data have relationships the product queries in more than one direction, and does it need multi-row transactions? → relational, and stay there until it hurts.
2. Is the workload a huge volume of writes keyed by one identifier, read by that key and a time range? → wide-column.
3. Is it a hot working set of small values with structure-specific operations? → key-value / Redis.
4. Is it full-text or ranked retrieval? → a search index, derived from the source of truth.
5. Is it append-only events queried by time and dimension? → columnar or time-series.
6. Is it a graph traversed several hops deep? → a graph database, or an adjacency table plus caching if the depth is two.

Most real designs end up with two or three of these, with one of them designated the source of truth and the rest derived.

Checklist

• The source of truth, and what is derived from it.
• Access patterns mapped to indexes or key designs.
• Transaction boundaries and what is outside them.
• Connection pooling and the write path's capacity.
• The migration story for the biggest table.

Questions people ask

SQL vs NoSQL: how do you choose a database?

Choose from the access patterns: what the queries are, in what volume, and with what consistency. A relational database, with a fixed schema, secondary indexes, joins and ACID transactions, is the right default for the system of record whenever data has relationships and correctness matters. A NoSQL store earns its place for a quantified reason, such as write volume a single primary cannot handle, reads that are all by key, or append-only time-ordered events.

What is NoSQL database design?

NoSQL database design means shaping keys and tables around the access patterns, because 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, while the sort key gives range queries within the partition. In a wide-column store you design a table per query, for example messages partitioned by channel id and clustered by time when every read is the last N messages in a channel.

What is the NoSQL database design process?

List every read and write with its shape and rate, match the workload to a database family, then design the partition key, sort key and indexes so that each query is served by one of them. A query that is not will be fine at 10 k rows and a page-long outage at 100 M. Then check for hot partitions, the classic failure, and spread them by bucketing the key by time (channel_id + day) or adding a hash suffix.

What are the main types of NoSQL databases?

The main families are key-value, wide-column, document and graph. Key-value stores such as Redis and DynamoDB serve gets and puts by key; wide-column stores such as Cassandra handle time-ordered data at high write volume; document stores such as MongoDB hold nested objects read as a unit; graph databases such as Neo4j suit traversals of variable depth. Most designs end up with two or three stores, one of them designated the source of truth.

Why not use Postgres for everything?

Postgres is the right default until a quantified reason says otherwise: write volume, access shape, data shape or availability. At 200 k writes/s a single primary cannot keep up and sharding Postgres by hand is a project, while Cassandra scales writes by adding nodes. Reads that are all by key fit a key-value store, and multi-region active-active writes need a leaderless store; the reverse case for Postgres is transactions, ad-hoc queries and secondary indexes without redesign.