Start from the questions, not the products
"Which database should we use?" is the wrong first question. Answer these first, per kind of data, and the choice usually follows:
| Question | Why it matters |
|---|---|
| What are the top queries? | By key? By range? Ad hoc? Full text? Aggregates over billions of rows? |
| What are the numbers? | Reads and writes per second, total size, growth (01.05 Back-of-the-envelope estimation). |
| What guarantees? | Multi-row transactions? Strong consistency, or is a little lag fine? Can any data ever be lost? |
| How will it change? | New queries every month (favour flexible) or one fixed pattern (favour specialised)? |
| Who runs it? | What your team already knows, and whether a managed service exists, often outweigh benchmarks. |
A sensible default
For most products, the default is a relational database (Postgres or MySQL), run as a managed service, plus a cache. It handles relations, transactions, constraints and new queries, and with the ladder from 04.08 Scaling a relational database it scales much further than most products ever need.
Then add a specialised store when a specific kind of data earns it:
- Write volume or size beyond one database, with simple access by key: key-value or wide-column.
- Full-text search or many-field filtering: a search index, fed from the main database.
- Analytics over huge event volumes: a columnar store, fed by a pipeline.
- Relationships many hops deep, queried constantly: a graph database.
- Large files: object storage, with only metadata in the database.
Snip's storage, so far
4 steps. Use Next and Back to follow the request.
Text version
- Postgres is the source of truth for everything relational: links, users, plans and daily counts. It needs transactions and constraints, and it fits on one well-tuned primary with replicas.
- Redis holds the hot code → URL mapping, so most redirects never reach Postgres.
- Raw click events (billions a year) go through an event log into a columnar store, which answers 'clicks by country by hour' by scanning compressed columns.
- Backups and archived write-ahead log go to object storage, for point-in-time recovery.
- Snip services → Redis: hot reads
- Snip services → Postgres: source of truth
- Snip services → Click events: clicks
- Click events → Columnar store: load
- Postgres → Object storage: WAL, backups
Every extra store is another system to run, secure, back up and keep consistent with the others (the data moves between them asynchronously, so they disagree briefly). That cost is why the list stops at four.
Seen in production
Discord
2017Message storage
Discord started on MongoDB. By late 2015 it had 100 million stored messages, and once data and indexes no longer fit in RAM, latencies became unpredictable. Its reads were random and the read/write ratio about 50/50. It listed requirements (linear scalability, automatic failover, low maintenance, proven to work, predictable performance, not a blob store, open source) and found Cassandra was the only database meeting all of them. Messages were partitioned by channel and a time bucket, since every query operates on a channel.
What we foundDiscord wrote down its requirements before picking a database, then modelled the data around its one dominant query.
How Discord Stores Billions of Messagesdiscord.com ↗A startup with 5 engineers, all experienced with Postgres, is building a marketplace: users, listings, orders and payments, with about 50 writes a second expected in year one. One engineer proposes a document database 'because it scales better'. What's the strongest response?
Recap
Interview questions
How do you decide which database to use for a new system?