Cruxly › System design › Databases: Choosing One

How to choose a database

start from the workload, default to relational, leave for a measured reason

Picking a database is a short chain of reasoning, not a product preference. Write down the access patterns, the read to write ratio, the consistency need, the size and the latency target. Start from a relational database, and leave only when one of those numbers cannot be met by a primary, replicas, a cache and partitioning. Every other store you add is a derived copy of one system of record.

Start from the workload, not the product

Candidates lose this question in the first ten seconds by naming a product. Ask for, or state, the workload first. Eight things decide almost everything:

Give numbers. "About 60,000 reads and 2,000 writes a second, 400 GB, strong consistency for payments" is a workload. "A lot of data" is not. If the interviewer has not given you the numbers, say your assumptions out loud and ask whether they are fair.

Expect the interviewer to push on each row. "What if reads double?" tests the read/write ratio. "What if product wants a new filter next quarter?" tests query flexibility, and it is the question that quietly kills stores where the query is fixed at design time. "Who runs this?" tests maturity. Have a one-line answer for each before you start drawing boxes.

The order matters. Access patterns come first because they decide the data model, and the model decides which stores are even candidates. Cost comes last because it breaks ties between options that already work.

:::key State assumptions, then derive size Estimate the total yourself: records per day x bytes per record x retention. A candidate who computes "2 TB in a year" before choosing a store shows the judgement the question is testing. :::

Default to relational, and leave only for a reason

The default is a relational database, PostgreSQL or MySQL. Say that sentence in the interview, then make the other side earn its place. The reasons are practical: you get multi-row transactions, constraints the database enforces, ad-hoc queries for questions nobody has asked yet, secondary indexes that stay consistent, mature tooling and a hiring pool that knows it. Later lessons cover what transactions and isolation actually guarantee.

One primary plus replicas, a cache in front, and large tables partitioned goes a long way. Reads scale by adding replicas. The part you cannot copy freely is the primary, because every write still lands there. So the one number to watch is write load on the primary. I did not find a trustworthy universal "writes per second per primary" rule of thumb, and you should not invent one in an interview. Say instead: "I'd measure it, and here is the signal that tells me to move."

What leaving looks like. When the primary saturates, you have two families of exits:

• Shard the relational database. Shopify runs MySQL shards keyed by shop, with a routing table in a separate unsharded database, and moves a shop between shards online. The price is a rebalancing problem: before rebalancing, their least and most used shards differed roughly fourfold in load, and simulated moves brought that to about twofold.
• Move that workload to a store built for it, such as an LSM-based wide-column store for a firehose of keyed writes.

:::warn Version-dated complaints Uber's 2016 post about leaving PostgreSQL for MySQL is often quoted. It was based mainly on PostgreSQL 9.2, a release about four years old at the time (Uber's own post called it "somewhat old" and noted 9.5 was current). It cited write amplification from indexes, physical replication cost across data centres, replicas that could not run long reads without lagging, and process-per-connection limits. Some of that has been mitigated since. Quote it as "one company's reasons in 2016", never as a current fact about Postgres. :::

Real reasons to leave, and reasons that are not

A real reason has two parts: a requirement and a measurement showing one primary plus replicas, cache and partitioning cannot meet it.

A real example, with a caveat. Discord stores messages by channel and time bucket. It shows what a wide-column store costs to run at scale, not that relational failed: Discord started on MongoDB and moved to Cassandra at roughly 100 million messages, when data and index no longer fit in RAM. Years later, at trillions of messages, they moved Cassandra to ScyllaDB: 177 nodes became 72, historical-read p99 went from 40 to 125 ms down to 15 ms, and insert p99 from 5 to 70 ms down to 5 ms. The pain was hot partitions, compaction backlogs and garbage-collection pauses. The reasons were a specific access pattern and measured operational limits, not "NoSQL is cool".

Wrong reasons, and the one-line answer to each:

• "Web scale." With 10 GB of data it is not. Name the rates.
• "Joins are slow." Joins on selective, indexed keys are what the engine is built for. They get expensive when unindexed, when they produce huge intermediate results, or when they cross shards.
• "Our data is JSON." PostgreSQL has JSONB with indexing. Schema flexibility also moves the schema into application code.
• "It is faster." Faster for which query, measured how?
• "It looks good on my résumé." Say it only to yourself.

:::warn Operational maturity is a requirement The team that has never run Cassandra will pay for compaction, repair and hot partitions. If the access pattern does not demand it, a managed relational or managed key-value service wins on risk. :::

The decision tree and the family table

Two gates place most workloads. Do the work out loud.

The one-page comparison. Later lessons go deep on each row.

Columnar warehouses (BigQuery and similar) belong beside time-series for analytics scale. Treat the table as a map of where to look, not a verdict. Within a family, products differ a lot.

Talk trade-offs without reciting CAP

Reciting "CP or AP" at an interviewer ends the conversation rather than advancing it. Name the price instead. There are four currencies:

• Consistency (freshness): how stale a read may be, and whether writes may conflict. This is the practical sense, not CAP's linearizability; the latency-versus-staleness trade is what PACELC describes.
• Availability: what happens to the user when a node or a region is unreachable.
• Latency: p99 per query, including cross-region round trips.
• Cost: instances, storage, IOPS, cross-region traffic and operator time.

You always trade between them, and the trade is per kind of data, not per system. A bank balance read before a transfer pays latency to hit the primary. A like counter reads a nearby replica and tolerates a second of staleness. The same product does both.

A strong sentence has this shape: "For this data, I tolerate this staleness, and I pay for it with that." For example: "Search results can be seconds behind the catalogue; I'm paying with freshness to keep the primary free of search load." Or: "Inventory at checkout reads the source of truth; I'm paying with latency so we never oversell."

Be ready for the follow-ups:

• What happens during a partition or a failover? Say which side refuses or serves, and what the user sees.
• What is your recovery point? Async replication means a region loss can lose the most recent writes. Say the number you accept.
• What does it cost? On RDS, Provisioned IOPS storage is billed at the provisioned IOPS rate regardless of IOPS consumed, and cross-region traffic is billed. Count more than compute.

The consistency lessons later in this topic cover the mechanics. Here the skill is saying the trade in one plain sentence.

One system of record, several derived stores

Real systems rarely use one store, and they rarely use six equals. The pattern that works is polyglot persistence with one system of record:

• The system of record is where a write is committed and where the truth lives. Usually relational.
• Derived stores are copies shaped for one job and rebuildable from the record: a cache for hot reads, a search index for relevance, a warehouse for analytics.
• Each derived store has its own freshness. Order of magnitude: cache in milliseconds if you invalidate on write, search in seconds, warehouse in minutes. State the freshness you promise.

Never write all the stores from the application. A write to the database followed by a write to the search index is not atomic. A crash between them leaves them disagreeing, and nothing in the design guarantees a repair. Two concurrent writers can also apply updates to each store in different orders.

The safe pattern is the transactional outbox. Write the business row and an event row in the same local transaction. A change-data-capture connector, or a relay that reads the table, publishes the committed event to the derived stores. Debezium's outbox router, for example, uses an aggregate id as the message key so events for one entity keep their order within a partition. Delivery is at-least-once, so consumers must deduplicate on the event id.

:::warn The index is not the source of truth If your search index is the only copy of some field, you cannot rebuild it. Keep a rebuild path: snapshot the record, replay the change stream into a new index, swap an alias. :::

Do not add a store without a reason. "Six stores for a toy problem" is as much a miss as one store for everything. Operations, backups and failure modes of each one are on you.

Worked example: three workloads, reasoning aloud

1. Billing for a software product. "Queries are by account and invoice, with joins. Writes are a few hundred a second. Money must not be lost or counted twice, so I need real transactions and constraints. That fits one relational primary with a replica, and I'd revisit at ten times the load. The failure I design for is the primary going down: a synchronous standby (or quorum of standbys) so no committed write is lost on failover, accepting the extra commit latency and configuring it so one standby outage does not block writes, plus tested point-in-time recovery." No exotic store is justified, so none is added.

2. Sensor metrics, 200,000 points a second. "These are append-only, never updated, read as time ranges and aggregates. A single unpartitioned table with one row per point is the trap: per-row overhead and index bloat, and retention by row-by-row DELETE. Time-partitioned tables, where dropping or detaching a partition is far faster than a bulk DELETE, or TimescaleDB, fix retention; at this rate a time-series or columnar store with compression and downsampling is still the better fit. I'd put a queue in front for burst absorption and write there with retention and downsampling. Raw data expires after a window; rolled-up data stays. Ingest is at-least-once, so writes are idempotent by series and timestamp. The cost I accept is eventual freshness of a few seconds."

3. People within three hops of a user. "In SQL each hop is another join, and the work grows with each hop. If this is the core query over a huge graph, I'd use a graph store that walks edges directly. If it is one feature on a modest graph, a recursive query in Postgres is fine and avoids a second system. I need the graph size and the query rate to decide." The honest answer names both branches and the number that selects between them.

Notice what each answer does: restates the access pattern, names the default, says what would break it, picks the store, and states the cost. Where the interviewer gave no numbers, the candidate asked or assumed aloud. The chosen store changes with the answers while the questions stay the same.

Common wrong statements, and what to say instead

A five-beat answer you can defend:

1. Restate the access pattern with numbers.
2. Name the default: relational.
3. Name the specific limit that breaks it, if any.
4. Name the store you would use instead, and what it costs you to operate.
5. Say what would make you revisit the choice.

If you do only one thing from this lesson: never name a database before you have said the queries and the numbers. The next lessons in this topic fill in each box of the decision tree: ACID and isolation, storage engines, each family in depth, modelling for NoSQL, distributed SQL, scaling PostgreSQL, consistency, operations and real case studies.

How to say it in the interview

"Before I name a database: the main queries are these, at roughly this rate, with this consistency need and this data size. That fits one relational primary with replicas, so that is my default. I'd leave it only if writes outgrow one primary, or the query shape is something relational does badly, and I'd tell you what that costs me to operate."

Checklist

• Stated the access patterns and rates before naming any database
• Estimated data size and growth with a calculation
• Named relational as the default and said what would make me leave
• Avoided the wrong reasons: web scale, joins are slow, it's JSON, résumé
• Described trade-offs as a price per kind of data, not as a CAP label
• Kept one system of record and treated cache, search and warehouse as derived
• Used a transactional outbox or change stream instead of writing every store from the app
• Named the operational cost of any non-default store

Related lessons

• ACID, precisely
• Isolation levels, in depth
• Data models: what each family is built for, and where it fails
• Storage engines: B-trees, LSM-trees, row vs column
• NoSQL modelling in practice: DynamoDB, Cassandra, MongoDB
• Distributed SQL: Spanner, CockroachDB, Aurora and friends