Skip to content
learn
  1. Learn
  2. High Level System Design
  3. Databases and Storage Fundamentals

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.

Intermediate 12 min Last reviewed

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

  1. What shape is the data? Records with relationships, self-contained documents, timestamped events, a network of connections, large files.
  2. 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.
  3. What consistency do they need? Multi-record transactions (money, inventory) or is eventual consistency fine (feeds, counters)?
  4. 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

A decision tree for choosing a database The root asks what the workload needs. Transactions and flexible queries lead to a relational database, or distributed SQL at global scale. Key lookups at huge scale lead to key-value or wide-column stores. Special queries lead to search engines, graph or time-series databases. Files lead to object storage. Relational Postgres, MySQL Distributed SQL global scale Transactions / joins flexible queries Key-value DynamoDB Wide-column write-heavy Lookups by key at huge scale Search full text Graph or TSDB links, metrics Special queries Object store S3-like Files What does the workload need? Figure 1. Start at the top with the workload, not the technology. The glowing path, transactions and flexible queries leading to a relational database, is where most products start.

Try it

This interactive needs JavaScript. The diagrams and text above cover the same idea.

Try this:

  1. Describe an online shop's orders: records with relationships, flexible queries, transactions needed, one region. You land on a relational database.
  2. Now describe its click events: timestamped events, ranges over time, no transactions, huge write volume. A different store fits.
  3. 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

OptionWins whenLoses 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

  1. What should drive the choice of database?

    Show answer

    B. The data shape, main queries, consistency needs and expected scale Workload first; technology follows.

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

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

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

Get new lessons by email

Get new posts on backend and AI internals. No spam, unsubscribe anytime.

Your email is only used to send new posts. Privacy

Next up