Choosing a database

Lesson 3.1 · 3 min read · by Vinay C

We'll cover the following

Step 1: Do you need relational?
Step 2: If not, pick by the type of data or lookup
The flow in one view
Don't say this
Summary

Step 1: Do you need relational?

Ask:

Do I need joins?
Do I need flexible queries and filters (many ways to slice the data)?
Do I need transactions: several rows changing together, all-or-nothing?

If yes to any, use a relational database (Postgres). It is the default. Stop here unless write volume is far beyond one machine. Then shard it by a key, or use distributed SQL (Spanner, CockroachDB).

Step 2: If not, pick by the type of data or lookup

What you need	Pick	Examples
Lookup by key (phone book)	Key-value	DynamoDB, Redis
Very heavy writes, time-ordered data	Wide-column	Cassandra, Bigtable
Whole nested object read as one	Document	MongoDB, Firestore
Text search, ranking, facets	Search engine	OpenSearch, Elasticsearch
Relationships ("friends of friends")	Graph	Neo4j
Reports over billions of rows	Warehouse	BigQuery, ClickHouse

Search and warehouse sit next to your main database as a copy. They are not the source of truth.

The flow in one view

Joins / flexible queries / transactions?
   yes -> Postgres  (huge writes? -> shard, or Spanner/CockroachDB)
   no  -> by lookup type:
          key         -> DynamoDB
          heavy writes-> Cassandra
          search      -> OpenSearch
          graph       -> Neo4j
          documents   -> MongoDB

Don't say this

"SQL is consistent, NoSQL is eventual." Wrong. Consistency is a setting on each query, not a type of database.

Check yourselfURL shortener: you only ever do short code to long URL. Phone book or bank?

Phone book. Pure key lookup, so a key-value store fits. (And at ~40 writes/s it is tiny anyway, see Estimation.)

Summary: say it in the interview

"No joins or transactions, just key lookup at high volume, so DynamoDB. If that changed, I would go Postgres." Give the choice, then what would change your mind.