Part 4 · Databases & Data at Scale
How to Choose a Database for Your System
Hundreds of databases all claim to be fast and scalable. A practical seven-step process: the questions to ask, a sensible default, the signals that justify something else, prototyping with real load, and migrating safely when you got it wrong.
On this page
The Problem
There are hundreds of databases, and each website says its database is fast, scalable and easy. Teams often choose because of hype, because a big company uses it, or because one engineer likes it. Two years later, they are stuck:
- the database cannot answer a query (a question about the data) that the business now needs,
- it is expensive to run,
- or nobody knows how to fix it at 3 AM.
In the last nine lessons we learned the building blocks: data models, transactions, storage engines, normalisation, replication, sharding, consistent hashing, CAP and consistency models. This lesson turns them into a practical decision process.
The Core Idea
A database is a program that stores data and lets you read and change it safely. Choosing one is like choosing a vehicle.
- A family car (PostgreSQL/MySQL) does almost everything well: commuting, shopping, trips. Most people should start here.
- A delivery truck (Cassandra/ScyllaDB) carries enormous loads on fixed routes, but it's awkward for a quick trip to the shops. (It is great for lots of simple writes, but weak for ad-hoc queries.)
- A motorbike (Redis) is extremely fast for short trips. But it cannot carry much, and it is not where you keep valuables. (Redis keeps data in memory, which is fast but small and costly.)
- A bus (a data warehouse like BigQuery, a database built for big reports) moves huge numbers of passengers (rows) together. But you would not take it to buy milk (look up one row).
There is no "best vehicle". Pick the one that fits your trips. Many households have more than one.
How It Works
Step 1: Ask the right questions
About the data:
- What shape is it? Structured records with relationships (tables)? Nested documents? Time-ordered events? A graph of connections? Large files?
- How big is it now, and in 2–3 years? Gigabytes, terabytes or petabytes? (Estimate as in lesson 10.)
- How important is correctness? Money and inventory need ACID transactions and strong consistency. Likes and views do not. A transaction is a group of changes that succeed or fail together. ACID is the set of four promises a transaction makes: Atomicity (all or nothing), Consistency (rules stay true), Isolation (parallel transactions do not disturb each other) and Durability (saved data survives a crash). Strong consistency means every read sees the latest write.
About the access patterns (how your app reads and writes data):
- What are the main queries? Look-ups by key, by ranges, rich filters and joins, full-text search, or aggregations (sums and counts) over millions of rows?
- What is the read/write ratio? Read-heavy (catalogues, profiles) or write-heavy (events, logs, messages, IoT sensor data)?
- What latency and throughput do you need? Latency is the time one request takes. Throughput is how many operations happen per second. Do you need milliseconds per request? Thousands or millions of operations per second?
- Will new, unplanned queries appear? If so, prefer flexible (relational) models over designs built around fixed access patterns.
About operations and people:
- Where are users? One region or worldwide? Are there data residency rules (laws that say where data must be stored)?
- Managed or self-hosted? A managed service (RDS, Cloud SQL, Atlas, DynamoDB) is run for you by a cloud company. It costs more per GB but saves a lot of work.
- What does your team know? A database your team understands well usually beats a "better" one nobody can run.
- Cost: licences, hardware, storage, backups, data transfer, and people's time.
- Ecosystem: drivers for your language, ORMs (tools that map database rows to objects in code), backup tools, monitoring, hosting options, community and hiring.
Step 2: Start from a sensible default
For most new applications:
Start with a relational database (PostgreSQL or MySQL), plus Redis for caching.
Why:
- It handles structured data, relationships, transactions and flexible queries. A relational database stores data in tables with rows and columns, and you query it with SQL.
- It is mature, well understood, and has excellent managed options.
- PostgreSQL alone covers a surprising amount. JSONB is a column type that stores JSON documents in a fast binary form. Full-text search finds words inside text. PostGIS is an extension for maps and locations (geospatial data). Extensions are add-on packages, and some add time-series and vector search.
- One well-tuned server with replicas (extra copies that serve reads) scales much further than most products ever need.
Redis is an in-memory data store. It keeps data in RAM, so it is very fast, and it is mostly used as a cache (a quick copy of data you can get slowly elsewhere). Add specialised databases when a clear need appears, not before.
Step 3: Recognise the signals for something else
Each row below names a signal (a sign in your needs) and the kind of database that fits it. Terms: a key-value store looks up a value by its key, like a dictionary. A time series is a list of values with timestamps. A graph database stores things and the links between them. A vector database finds items that are similar in meaning, using embeddings (lists of numbers that describe a text or image). Object storage keeps whole files, like Amazon S3.
| Signal | Consider |
|---|---|
| Massive write volume of time-ordered data per key (messages, events, IoT), simple access patterns, multi-region writes | Cassandra / ScyllaDB / DynamoDB |
| Simple key-based access at huge scale with sudden spikes, and you want no servers to manage | DynamoDB (or similar managed key-value) |
| Very fast temporary data: cache, sessions, rate limits, leaderboards, queues | Redis (or Valkey, Memcached) |
| Varied, nested records where each item has different attributes, and the team prefers documents | MongoDB (or PostgreSQL JSONB) |
| Full-text search, typo tolerance, facets, relevance | Elasticsearch / OpenSearch (lesson 19) |
| Aggregations over billions of rows: dashboards, business reports (BI) | ClickHouse, BigQuery, Snowflake, Redshift |
| Metrics and sensor readings with time-based queries and retention | TimescaleDB, InfluxDB, Prometheus |
| Deep relationship queries (friends-of-friends, fraud rings) | Neo4j, Amazon Neptune |
| Similarity search on embeddings (AI features) | pgvector first, then a dedicated vector database if needed |
| Tables and transactions, plus spreading over many servers (horizontal scale) and many regions | Distributed SQL: Spanner, CockroachDB, YugabyteDB, TiDB |
| Files, images, video, backups | Object storage (lesson 17), not a database |
Step 4: Check the hard requirements
Before you commit, check these points:
- Transactions: does it support what you need (multi-row? multi-table? across partitions?) at the isolation level you need (lesson 21)? Isolation level means how much parallel transactions can see of each other.
- Consistency: can you get strong reads where you need them (lessons 27–28)?
- Scaling path: how will it grow? Options are replicas, sharding (splitting data across servers) and automatic partitioning (lessons 24–25).
- Failure behaviour: what happens when a node (one server) or a zone (one data centre) fails? How is failover done? Failover means switching to a backup server.
- Backups and recovery: can you do point-in-time recovery (restore the data to an exact moment)? How long does a restore take?
- Security and compliance: encryption, access control, audit logs, data residency.
Step 5: Prototype with your real workload
A benchmark is a test that measures speed. Vendor benchmarks use the vendor's own chosen workload, so do your own test instead:
- load realistic data volumes (or a scaled sample),
- run your actual queries at expected peak rates,
- measure p99 latency (lesson 8), not just averages. p99 is the time that 99 out of 100 requests beat, so it shows the slow cases,
- test failure scenarios: kill a node, fill a disk, cause replication lag,
- and estimate the monthly cost at your expected size.
Worked example: a ride-hailing app
| Data / feature | Needs | Choice |
|---|---|---|
| Users, drivers, trips, payments | Relationships, ACID, reporting | PostgreSQL (with replicas; shard by city later if needed) |
| Live driver locations (updated every few seconds) | Huge write rate, short-lived data, "nearby" queries | Redis with geospatial commands (or an in-memory geo index) |
| Trip history for analytics and pricing models | Aggregations over billions of rows | Columnar warehouse (stores data by column, good for sums over many rows; BigQuery / ClickHouse) fed by CDC or events |
| Receipts, invoices, and search in support tools | Full-text search | Elasticsearch / OpenSearch |
| Driver documents and profile photos | Large files | Object storage + CDN |
| Rate limiting, OTP attempts, sessions | Fast counters with a TTL (time to live: the data deletes itself after a set time) | Redis |
Each database has one clear job. PostgreSQL stays the source of truth, which means it is the place whose data is treated as the real, final version for core business data. CDC (change data capture) means reading the database's change log and sending each change to other systems.
Step 6: Keep the number of databases small
Every extra database adds:
- another system to learn, monitor, back up, secure, upgrade and pay for,
- another place for data to get out of sync (you will need events or CDC between them),
- another on-call skill set.
A good rule: one primary source of truth, plus a few purpose-built stores that are derived from it and could be rebuilt.
Step 7: If you must migrate, do it safely
A migration moves data and traffic from one database to another. When a database no longer fits, it usually follows this pattern. Dual write means writing to both databases. Backfill means copying the old data to the new one. Shadow reads mean reading from both and comparing, while users still get the old answer.
- 1Dual write or CDCnew writes reach both old and new databases
- 2Backfillcopy historical data across
- 3Verifycounts, checksums, sampled reads
- 4Shadow readsread both, compare, still serve the old one
- 5Switch readsgradually move reads to the new database
- 6Switch writesthe new database becomes the source of truth
- 7Decommissionkeep the old one read-only for a while, then retire it
Big migrations often take months. This is why a good first choice matters. It is also why boring, flexible defaults are valuable.
Trade-offs
- One general-purpose database: simple, consistent and cheap to operate, but it may be not the best fit for some very specialised workloads at huge scale.
- Many specialised databases: the best fit for each job, but more operations, cost, sync complexity and skills needed.
- Managed services: less operational work and built-in high availability and backups, but higher cost at scale, some lock-in (hard to leave the vendor) and less control.
- The newest, trendiest database: exciting features, but a smaller community, fewer experts, and more unknown ways to fail.
In the Real World
Discord's two migrations. Discord moved message storage from MongoDB to Cassandra when its data outgrew one database, and years later from Cassandra to ScyllaDB when running it became hard and latency spikes grew with trillions of messages. Each time, the decision came from clear, measured problems, and each migration was carefully planned with dual reads and data validation.
Uber's Postgres-to-MySQL move. In 2016, Uber published a detailed post explaining why it moved some core systems from PostgreSQL to MySQL, citing specific issues with write amplification (one small change causing many extra writes) and replication at its scale. The PostgreSQL community published careful replies. Reading both is a great lesson: database choices depend on specific workloads and versions, and "better" is always "better for this".
"Just use Postgres." A growing number of teams deliberately run most of their stack on PostgreSQL (relational data, JSON documents, queues, full-text search, even vectors) and only add other systems when they must. Notion, for example, scaled by sharding PostgreSQL rather than switching databases.
Stack Overflow has famously run on a relational database (SQL Server) with heavy caching, scaling mostly vertically (using bigger servers instead of more servers), and serves a huge audience. It proves that a "boring" choice done well can go a very long way.
Polyglot at big companies. "Polyglot" means using many languages, here many database types. Netflix uses Cassandra for high-volume data, relational databases for others, Elasticsearch for search and many analytics systems. Each is chosen for a job, and backed by strong platform teams to operate them. Smaller teams usually cannot afford that many systems.
Interview Questions
Q1How do you choose a database for a new system?
Start from the data's shape, the main queries and access patterns, the read/write ratio, correctness and consistency needs, size and growth, latency targets, regions, team skills and cost. Default to PostgreSQL or MySQL plus Redis, and add a specialised store only for a clear, measured signal.
Q2When would you pick Cassandra or DynamoDB over PostgreSQL?
When you have very high write volumes of simple data found by key (messages, events, IoT readings). You also need to know the access patterns up front, or need writes in many regions. And you must be willing to design one table per query and accept limited transactions and limited ad-hoc queries.
Q3Why not use one specialised database for every job?
Each extra database is another system to operate, secure, back up and keep in sync, and another skill for on-call. Keep one source of truth and a few derived stores that could be rebuilt from it.
Q4How would you benchmark a candidate database?
Use realistic data volumes and your real queries at the peak rates you expect. Measure p99, not only the average. Then switch off nodes, fill disks and create replication lag, and estimate the monthly cost at the target size. Vendor benchmarks measure the vendor's workload, not yours.
Q5How do you migrate a live system to a new database?
Write to both databases (dual write or CDC). Copy the old data (backfill). Check it with counts and checksums (a checksum is a short fingerprint of data). Read from both and compare (shadow reads). Move reads over slowly, then writes. Keep the old database read-only as a fallback, and retire it last.
Key Takeaways
- Choose a database from data shape, access patterns, correctness needs, scale, latency, team skills and cost, not from hype.
- Default to PostgreSQL/MySQL plus Redis. They cover most needs, including JSON, search and geospatial via extensions.
- Add specialised databases for clear signals: huge write-heavy streams, search, analytics, graphs, vectors, global transactional scale.
- Prototype with real workloads, measure p99, test failures and estimate cost.
- Keep one source of truth and few databases. Migrate carefully with dual writes, backfill, verification and gradual switching.
Further Reading
- Designing Data-Intensive Applications by Martin Kleppmann (chapters 1–3 and 12, "The Future of Data Systems")
- AWS's decision guide "Choosing an AWS database service"
- The DB-Engines website (for database categories and popularity trends)
- Discord's blog posts on storing billions and trillions of messages
- Uber's 2016 engineering post on moving from Postgres to MySQL, and the PostgreSQL community's responses
This wraps up Part 4. Next up, Part 5: APIs & Communication, starting with "REST API Design Best Practices".
Published by Jainik · Last updated · How these lessons are made
Keep learning
Part of the study guide
Related series