Databases for system design interviews

In a system design interview, the database is where most of the hard follow-up questions land. You are not being asked to recite every product. You are being asked to pick a store that fits the access patterns, show its key and index design, size it with numbers, say which operations need transactions and which can be stale, and explain what happens as it grows or fails. This page is the playbook that ties the rest of this module together.

Picture it first: the kirana shop’s four places

A busy neighbourhood shop keeps things in four places. The ledger under the counter records every sale and every credit customer’s balance; it must never be wrong, so only the owner writes in it, one entry at a time. The front shelf holds the fifty things people ask for most, so the owner can grab them without walking to the back. The godown at the back holds bulky sacks and cartons that are fetched whole. And a printed price list by the door lets customers look things up by name without disturbing the owner.

Nobody would keep the credit ledger on the front shelf, or look up “all biscuits under ₹20” by reading the ledger page by page. Each place matches one kind of question.

Map it back: the ledger is your relational system of record, the front shelf is a cache or in-memory store, the godown is object storage, and the price list is a search index rebuilt from the ledger. The interview skill is the same as the shopkeeper’s: match each question to the place that answers it cheaply, and know which one is the truth when they disagree.

What the interviewer is really testing

The question “which database would you use?” is a probe for reasoning, not product knowledge. Interviewers listen for five things:

• You derive the choice from access patterns. “Users read their last 50 messages, newest first” leads to a key; a brand name doesn’t.
• You know what each choice costs. Every store gives something up: joins, ad-hoc queries, strong consistency, cheap writes, or operational simplicity.
• You size it. Rows, bytes and operations per second at peak, with the arithmetic said out loud (back-of-envelope estimation).
• You separate the operations that need guarantees from the ones that don’t. A payment and a like counter don’t deserve the same machinery.
• You can go one level deeper on request: index column order, shard key, replication lag, how a search index stays in sync.

A short, correct design with clear reasons beats a diagram with six databases and no keys.

Start from a default

Unless the prompt clearly says otherwise, begin with one relational database as the system of record, and say PostgreSQL or MySQL by name. You get:

• Transactions and constraints: a payment row and an order row commit together, and a unique index stops two accounts claiming one username (ACID transactions, database constraints).
• Secondary indexes on any column, so new queries don’t force a new table (database indexes).
• Flexible querying: joins for the admin screen, aggregates for a quick report, JSONB for the semi-structured attributes of a product.
• A higher ceiling than people assume. OpenAI’s 2026 write-up describes ChatGPT running on a single PostgreSQL primary with nearly 50 read replicas at millions of queries per second. They moved write-heavy, shardable workloads to sharded systems and stopped adding new tables to that cluster, which is exactly the shape of the trade-off: reads scale out easily; writes on one primary don’t.

Then move off the default only for a reason you can name. The usual ones:

The broader landscape of store types is covered in SQL vs NoSQL and database types. This page is about using them under interview pressure.

The five-question method

Run the same five questions for every design, in this order. Each one feeds the next.

1. Entities. Name the nouns and their relationships, and mark the aggregate roots: the things that are created, owned and deleted together (an order and its items). These usually become tables or partition keys. Keep it to five or six; detail comes later (data modeling and denormalization).

2. Access patterns. Write the top reads and writes as sentences with a shape: “get order by id”, “list a user’s orders newest first, 20 per page”, “find available riders within 3 km”. Note the read-to-write ratio and any pattern that is not a lookup by key. Cassandra’s own data-modelling guide makes this the first step: list the application’s queries before designing a single table.

3. Numbers. Turn daily volumes into per-second rates and storage. For the food-delivery example: 3 million orders a day is 3,000,000 ÷ 86,400 ≈ 35 orders/s on average, so about 350/s at a 10× dinner peak. At about 2 KB per order with its items, that is 3M × 2 KB = 6 GB a day, about 2.2 TB a year. Riders are different: 200,000 online riders pinging every 5 seconds is 200,000 ÷ 5 = 40,000, so 40,000 writes/s, all overwriting the previous position. The numbers already say which data belongs where.

4. Guarantees. For each write, ask what a wrong answer costs compared with a slow one. Charging a card and creating the order must be atomic; the restaurant’s order count on a dashboard can be seconds stale. This is where ACID vs BASE and CAP and PACELC become concrete.

5. Keys and stores. For every access pattern, name the primary key, partition key or index that serves it, and the store it lives in. If no key serves a pattern cheaply, go back to step 2: denormalise, add an index, or add a store whose key fits.

Matching workloads to stores

Once the patterns and numbers are on the board, most choices fall out. Use this as a lookup, not a checklist to fill:

A few numbers make these choices sound grounded:

• DynamoDB caps each partition at 3,000 read units and 1,000 write units per second, where a write unit is one write of up to 1 KB, and an item can be at most 400 KB. Tables allow up to 5 local and, by default, 20 global secondary indexes. More in Cassandra and DynamoDB.
• Redis persists with snapshots (which can lose minutes of writes) or an append-only file whose default is an fsync every second, so “Redis as the only copy” means accepting up to about a second of loss on a crash (Redis).
• Elasticsearch refreshes indices every second by default, so a newly indexed document becomes searchable within about a second, plus whatever lag your sync pipeline adds (how search engines work).
• Amazon S3 gives strong read-after-write consistency for object PUTs and DELETEs, so a client that uploads a photo can read it back immediately (blob storage).

A worked example: the food-delivery data layer

Put the method to work on the app from the figure. The access patterns and numbers point to a polyglot data layer: one system of record and a few derived or specialised stores, each justified by a pattern.

Walk through it the way you would at the whiteboard:

• Orders, payments, menus: PostgreSQL. About 350 orders a second at peak is comfortable for one primary; the payment row and the order row commit in one transaction. The index that matters is orders (user_id, created_at DESC) for “my orders”, plus orders (restaurant_id, status) for the restaurant’s live queue (composite index design). At 2.2 TB a year, partition orders by month so old months can be archived by dropping a partition (table partitioning).
• Rider locations: Redis GEO. 40,000 writes a second that each overwrite the last value, read only by “who is near this restaurant?”, is the wrong job for a durable relational table and the right job for an in-memory geo index. Losing a few seconds of positions on a restart is harmless; the next ping repairs it (geospatial indexes).
• Menu search: a search index fed by change data capture. Customers type “paneer” with typos and expect relevance and filters. The index is derived from PostgreSQL, so a dish edit becomes searchable a second or two later, which nobody notices (change data capture).
• Photos: object storage plus a CDN. The database stores only the object key and metadata.
• Reports: a warehouse fed by the same stream, so analysts never run a 10-minute scan on the primary (OLTP vs OLAP).

Notice what is not there: no sharding, no NoSQL system of record, no second transactional database. The numbers don’t ask for them yet, and saying so is part of the answer. If the interviewer pushes to 100× growth, you climb the database scaling ladder: read replicas, caching the menu, then sharding orders by a key such as city or restaurant (database sharding).

Designing keys out loud

Interviewers love one level deeper: “show me the schema for that”. The skill is translating an access pattern into a key, and it looks almost the same in SQL and NoSQL.

Take a chat feature: “show the latest 50 messages in a conversation, newest first”. In PostgreSQL that is a table plus a composite index. In DynamoDB or Cassandra it is the table’s key itself: the conversation is the partition key (which machine), and the timestamp is the sort or clustering key (the order within it).

Three habits make this part strong:

• Start the key with the filter, continue with the sort. In SQL that is the column order of a composite index; in NoSQL it is the partition key followed by the sort key. A range or sort on a column that isn’t next in the key means scanning and sorting (composite index design).
• Do the hot-key arithmetic. If one cricket match’s live chat peaks at 5,000 messages a second, one DynamoDB partition (1,000 one-KB writes a second) can’t take it: 5,000 ÷ 1,000 = 5 partitions at the very least, so spread the conversation over 10 suffixed keys and merge on read. The same thinking applies to a celebrity’s followers or a flash-sale product (database sharding).
• Name the second access path. “Find a message by its id” or “all messages a user sent” don’t fit the conversation key. In SQL you add an index; in DynamoDB you add a global secondary index, which is updated asynchronously and costs extra write capacity. Saying that unprompted signals depth.

Guarantees per operation

Don’t give the whole system one consistency label. Go through the writes and say what each needs; most designs end up with a small transactional core and eventually consistent edges.

For the deeper engine behaviour (which anomalies each isolation level allows in PostgreSQL and MySQL, and how to retry serialization failures) see transaction isolation levels in practice. For multi-service workflows, see ACID vs BASE and two-phase commit vs sagas.

The follow-up ladder

After you place a store, expect the interviewer to climb a ladder of “and then?” questions. They come in a fairly predictable order, and each rung has an answer shape that scores well.

A few model answers, compressed:

• “Why not MongoDB here?” “The order and payment must commit together and the admin team runs ad-hoc queries, so I want relational transactions and secondary indexes. MongoDB would work for the menu documents, but one store is simpler to run.”
• “What if orders grow 100×?” “35,000 orders a second at peak is beyond one primary. Reads go to replicas and cache first; for writes I’d shard orders by restaurant or city, because nearly every query is scoped to one, and route by a shard map with logical shards so I can rebalance” (sharding a relational database).
• “What if the primary dies?” “A replica is promoted in tens of seconds. With asynchronous replication I can lose the last few acknowledged writes, so for payments I’d use a synchronous standby in another zone and accept the extra millisecond or two” (data replication).
• “What if Redis loses the rider positions?” “They are soft state. Riders ping every 5 seconds, so the index is rebuilt within one ping interval; matching degrades for 5 seconds rather than failing.”

Common mistakes that cost points

• “NoSQL because it scales.” Scale is a number. If the numbers fit one primary, say so; choose a partitioned store for a reason the default can’t meet, and name its key.
• Choosing the store before the access patterns. You end up defending a brand instead of a design.
• No indexes or keys on the board. Every frequent query needs a key or index that serves it; “we’ll query the orders table” is not a design (indexes in practice).
• Sharding in the first ten minutes. It adds cross-shard queries, resharding and operational load. Show you know the cheaper rungs first.
• The cache or the search index as the source of truth. Both are derived copies; say where the truth lives and how they are rebuilt.
• Dual writes to the database and the search index (or a queue) from application code. One write fails and they drift forever; use CDC or an outbox.
• Blobs in the database. Store files in object storage and keep the key and metadata in the row.
• Unbounded reads. “List all orders” without pagination or a limit; use keyset pagination for deep pages (offset vs keyset pagination).
• Ignoring deletes and retention. Data that only grows eventually becomes the problem; say how old data is archived or dropped.
• Ignoring the hot key. One celebrity, one flash-sale item, one live match: do the per-key arithmetic, not just the total.
• Hand-waving consistency. “It’s eventually consistent” without saying which reads may be stale, for how long, and why that’s acceptable.

What good looks like by level

Cheat sheet

How to say it in the interview

“I’ll make PostgreSQL the system of record: orders and payments need transactions, and at about 350 orders a second at peak one primary is plenty, with read replicas for history pages. The key access pattern is a user’s orders newest first, so I’ll index (user_id, created_at DESC). Rider locations are 40,000 overwrites a second and only queried by proximity, so they go in Redis GEO as soft state. Menu search needs typo tolerance, so I’ll build a search index from the database’s change stream and accept about a second of lag. Photos go to object storage behind a CDN. If writes grow 100×, I’d shard orders by city, because nearly every query is scoped to one city.”

Why not use one NoSQL database for everything? You could, but you’d give up multi-row transactions and ad-hoc queries for a write scale the numbers don’t require. I’d rather start relational and move one hot workload off it when a measured limit appears.

When would you pick Cassandra or DynamoDB as the primary store? When writes are very high and almost always keyed one way, such as messages by conversation or events by device, when I need multi-region writes, or when I want a managed store with predictable latency at any size. I’d design tables per query and do the per-partition throughput arithmetic.

How do you keep the search index consistent with the database? I don’t write to both from the app. I stream row changes with CDC or an outbox into an idempotent indexer, accept a second or two of lag, and can rebuild the index from the database at any time.

What if the interviewer says the data must never be lost? Then I say what “never” costs: synchronous replication to another zone or region before acknowledging, backups with point-in-time recovery, and tested restores. Each adds latency or money, so I apply it to the ledger, not to view counts.

How do you decide between a cache and a read replica? A cache is for hot, repeated reads that tolerate staleness and need sub-millisecond latency; a replica is for the long tail of queries and full SQL. Most read-heavy designs use both: cache first, replicas behind it.

Where to go next

• SQL vs NoSQL and database types: the store families in more depth.
• Database scaling: the ladder from tuning to distributed SQL, with numbers.
• Database sharding and sharding a relational database: shard keys, hot shards and live migrations.
• Composite index design: turning access patterns into index column order.
• Data replication: lag, failover and what can be lost.
• ACID vs BASE: choosing guarantees per feature.
• Cassandra and DynamoDB and Redis: the specialised stores you’ll name most often.
• The delivery framework: where the data layer fits in the whole interview.

References and further reading

1. Scaling PostgreSQL to power 800 million ChatGPT users (OpenAI, 2026)openai.comOne primary plus nearly 50 read replicas, the 12-table join behind past incidents, and moving write-heavy, shardable workloads to sharded stores.
2. Amazon DynamoDB: best practices for designing partition keysdocs.aws.amazon.comPer-partition ceilings of 3,000 read units and 1,000 write units per second, and what a read or write unit buys.
3. Amazon DynamoDB: quotasdocs.aws.amazon.comDefault table throughput quotas and the limits of 5 local and 20 global secondary indexes per table.
4. Amazon DynamoDB: naming rules and data typesdocs.aws.amazon.comThe 400 KB maximum item size.
5. Apache Cassandra documentation: defining application queriescassandra.apache.orgThe query-first approach: list the application’s queries before designing tables.
6. Redis persistenceredis.ioRDB snapshots that can lose minutes of writes, and append-only-file fsync every second as the default trade-off.
7. What is Amazon S3? (data consistency model)docs.aws.amazon.comStrong read-after-write consistency for PUT and DELETE of objects in all Regions.
8. Elasticsearch: near real-time searchelastic.coIndices refresh every second by default, so new documents become searchable within about a second.