System Design HLD-06.07
Choosing the right database
A decision flow by access pattern, consistency, scale and query flexibility, polyglot persistence, and the common wrong choices.
The big idea
Choosing a database is one of the hardest decisions to reverse: data migrations are slow, risky and expensive. Yet the choice is often made by habit ("we always use X") or fashion ("Y is web scale"). A better way is to let the workload decide. Write down what the data looks like, which queries must be fast, what consistency each operation needs, and how much data and traffic you expect, then pick the simplest store that serves all of it.
For most new products, that answer is a relational database, and the right time to add something else is when a specific access pattern clearly does not fit. Large systems end up with several stores, each doing what it is best at, an approach called polyglot persistenceUsing several different database technologies in one system, each for the data and queries it handles best., but each store you add is another thing to operate, secure, back up and keep consistent.
Think of choosing a vehicle. You do not start with "what is the fastest car?"; you start with what you need to carry, how far, on what roads, and how often. A delivery company ends up with vans, bikes and lorries, but each one was added for a reason.
How it works
Step 1: Ask four questions
- What shape is the data? Records with relationships, self-contained documents, timestamped events, a network of connections, large files.
- What are the main queries? By key only, flexible filters and joins, full-text search, time ranges, multi-hop traversals. List the top few by frequency.
- What consistency do they need? Multi-record transactions (money, inventory) or is eventual consistency fine (feeds, counters)?
- How big and how fast? Rows, gigabytes, reads and writes per second at peak, single region or global. Estimate them ( Back-of-the-envelope estimation ).
Step 2: Follow the tree
Try it
Try this:
- Describe an online shop's orders: records with relationships, flexible queries, transactions needed, one region. You land on a relational database.
- Now describe its click events: timestamped events, ranges over time, no transactions, huge write volume. A different store fits.
- Describe product search, then profile photos. A real shop uses several of these at once, which is polyglot persistence.
Going deeper
Start boring, add on evidence
A single PostgreSQL or MySQL instance with replicas carries many products to very large scale, and it can handle JSON documents, full-text search and time-series workloads reasonably well with extensions. Add a specialized store when you can point to a query that is too slow or a volume that will not fit, and keep one database as the source of truth, feeding the others through change data capture ( Transactional outbox and change data capture ) rather than dual writes from the application.
Questions people forget
Operational maturity (backups, point-in-time recovery, upgrades), managed service availability, team experience, licensing, and the cost of moving data out later often matter more than benchmark numbers.
Trade-offs
| Option | Wins when | Loses when |
|---|---|---|
| One relational database | Most products for a long time: simple, consistent, flexible | Extreme write volumes, global writes, specialized queries at scale |
| Polyglot persistence | Large systems with clearly different access patterns | Operational cost and keeping several stores consistent |
| Specialized store first | Workloads that are obviously special from day one (metrics, search, files) | General product data that needed transactions and joins |
Common mistakes
- Choosing by popularity or benchmarks instead of your own access patterns.
- Picking a NoSQL store "for scale" when the data fits comfortably on one server.
- Writing to two databases from application code and hoping they stay in sync.
- Storing files as blobs in the primary database.
Check yourself
-
What should drive the choice of database?
Show answer
B. The data shape, main queries, consistency needs and expected scale Workload first; technology follows.
-
An inventory system needs multi-row transactions and ad hoc reports in one region. What fits?
Show answer
B. Relational database Transactions and flexible queries at moderate scale are the relational sweet spot.
-
What is polyglot persistence?
Show answer
B. Using several database types, each for the workload it fits best Different stores for different access patterns, ideally fed from one source of truth.
-
Why avoid writing the same data to two databases directly from the application?
Show answer
B. A failure between the two writes leaves them inconsistent Dual writes have no shared transaction; use change data capture or the outbox pattern.
Recap
- Choose a database from the workload: data shape, queries, consistency and scale.
- A relational database is the default for most products.
- Add specialized stores for search, metrics, graphs, files or extreme key-value scale.
- Polyglot persistence works best with one source of truth feeding the others.
- Database choices are hard to reverse; estimate, then decide.
Next up
- Crack the system design round learning path