Databases for System Design Interviews

Databases are the decision interviewers probe hardest because every shortcut eventually shows up there: duplicate orders, stale balances, overloaded hot partitions, a search index that lies, or an analytics query that slows checkout. The database choice is not a brand-name exercise. It is where you state the contract your system needs: which data is authoritative, which reads may be stale, which writes must survive crashes, which queries must stay under a few milliseconds, and which failures you are willing to recover from later. In this guide we build that judgment from first principles, then keep layering on indexes, transactions, isolation, replication, sharding, caching, distributed workflows, analytics, recovery and the interview decision framework. The goal is not to memorize ten database families. The goal is to say, calmly, "this is the source of truth, this is the read model, and here is the trade-off I am choosing."

01A database is a contract, not a folder for bytes

The easiest analogy is a bank locker plus a clerk. The locker gives you storage, but the clerk gives you rules: only the account owner can withdraw, the ledger balances, two withdrawals cannot spend the same rupee, and a power cut does not erase yesterday's deposit. The rules are the database.

That distinction matters in interviews. If you say "I'll use Postgres" or "I'll use Cassandra" too early, you have skipped the contract. Start by naming what the data needs from the system, then pick the store that gives you those guarantees with the least drama.

A good database decision answers five questions. I use these as my checklist when a candidate proposes a store.

The source-of-truth hierarchy

Most real systems have more than one place that looks like a database. The checkout service may write to Postgres, publish events through Kafka, hydrate Redis, feed Elasticsearch and land facts in a warehouse. Only one of those is usually the source of truth for an order.

Here is the rule I want you to say out loud: when derived stores disagree, the source of truth wins. A cache can be invalidated. A search index can be rebuilt. A warehouse can re-run yesterday's load. The source-of-truth database is where the system must not invent or lose business facts.

Many candidates make the answer too binary: SQL or NoSQL, consistent or scalable, normalized or denormalized. Strong designs combine stores with clear ownership. The write path can be conservative and transactional while the read path is denormalized and fast.

02Database fundamentals interviewers expect

Before comparing database families, get three foundations straight: OLTP vs OLAP, the write path, and the storage-engine shape underneath your indexes. These are small ideas, but they explain many production incidents.

OLTP vs OLAP

OLTP is product-facing transaction processing: user signup, checkout, message send, booking a ticket, changing a password. Operations are small, targeted and latency-sensitive, usually measured in single-digit or low tens of milliseconds inside the data tier.

OLAP is analytical processing: revenue dashboards, fraud exploration, cohort analysis, product usage reports. Queries scan many rows, aggregate by time or dimension, and can take seconds or minutes because they are not sitting in the user's click path.

What happens on a write

You do not need to implement a storage engine in an HLD round. You do need to know why a database can acknowledge a write before the final data page is neatly rewritten on disk. The trick is the log.

Here is the write path you should picture before we talk about durability, transactions or replication.

The write-ahead log is the database's flight recorder. PostgreSQL calls it WAL, InnoDB has redo logs, Cassandra has a commit log, and Kafka is essentially an append-only log exposed as a product. The names differ, but the idea repeats: append first because sequential writes are cheaper and easier to recover than random page rewrites.

The buffer pool is the other half of the picture. Databases keep hot pages in memory, modify them there, and flush dirty pages in the background. That is why a database can be both durable and fast: the durable record is the log, while the data files catch up later.

B-tree and LSM storage engines

Most OLTP stores lean toward one of two storage-engine shapes. B-tree style engines keep sorted pages and update them in place, which is excellent for point reads, range scans and stable indexes. PostgreSQL and MySQL are the familiar examples, with implementation differences we will cover in the indexing section.

LSM-tree style engines turn writes into appends. A write lands in a memory table and log, then flushes to immutable sorted files. Later, compaction merges those files. Cassandra, RocksDB, LevelDB and many high-write systems use this pattern.

The trade-off is straightforward: B-trees pay more work at write time to keep reads tidy; LSMs make writes cheap and pay read amplification plus compaction later. If you are designing a feed or metrics system with very high ingest, LSM-shaped storage is attractive. If you need flexible reads, joins and transactions, a relational B-tree-backed database is often the calmer default.

03Data modeling: start with correctness, then shape for reads

Data modeling is where candidates often reveal whether they are designing a system or reciting products. The safe default is to model the write side for correctness, then build read models for the hot paths. That sounds slower at first, but it prevents a lot of expensive cleanup later.

Normalized write models

Normalization stores each fact in one place. In an e-commerce system, users, products, categories, orders, order items and payments should not all be one giant row. Splitting them lets the database enforce foreign keys, uniqueness, not-null constraints and transactional updates.

users(id, name, email)
products(id, name, current_price, category_id)
categories(id, name)
orders(id, user_id, status, created_at)
order_items(id, order_id, product_id, quantity, price_snapshot)
payments(id, order_id, amount, status, charged_at)

Notice price_snapshot. It is denormalized on purpose inside an otherwise normalized model, because an order must remember the price at purchase time, not today's catalog price. Good modeling is not dogma. It is deciding which facts should change together and which facts are historical snapshots.

Denormalized read models

Denormalization duplicates data to make a read cheap. An order-history card might store the user name, product names, thumbnail URLs and final price in one document or wide-column row so the mobile app does not join six tables for every screen.

{
  "order_id": "o123",
  "user_id": "u1",
  "user_name": "Asha Rao",
  "items": [
    { "product_id": "p7", "name": "Mechanical keyboard", "price_at_purchase": 4500 }
  ],
  "status": "DELIVERED",
  "created_at": "2026-01-01T10:00:00Z"
}

The cost is staleness and repair. If the user changes her display name, should old orders change? Sometimes no, because invoices need snapshot semantics. If the product thumbnail changes, maybe yes, but a few seconds of lag is acceptable. Name the business rule and the sync mechanism.

This is the modeling split I usually sketch on the board.

Query-driven modeling for NoSQL

Relational modeling starts with entities. NoSQL modeling starts with queries. If the hot query is "get the latest 50 messages in conversation C", a table keyed by conversation_id and sorted by created_at makes sense. If the query is "search messages by text", that same table is the wrong tool and a search index belongs in the design.

-- Cassandra-style table for one hot query
CREATE TABLE messages_by_conversation (
  conversation_id UUID,
  created_at      TIMESTAMP,
  message_id      UUID,
  sender_id       UUID,
  body            TEXT,
  PRIMARY KEY ((conversation_id), created_at, message_id)
) WITH CLUSTERING ORDER BY (created_at DESC);

The trap is treating Cassandra or DynamoDB like a relational database without joins. Filtering on arbitrary non-key columns turns into scans, secondary-index pain or a pile of global indexes you did not budget for. With NoSQL, first list the access patterns. Then create the key shapes.

Relationship patterns and unbounded growth

One-to-many is usually a foreign key or a partition key: orders by user, messages by conversation, payments by order. Many-to-many usually needs a join table in relational systems or two query-specific tables in NoSQL systems. The pattern is not hard. The growth boundary is where candidates slip.

Do not put an unbounded array inside one record because it looks convenient on day one.

// Bad: this can grow to millions of entries
{
  "user_id": "celebrity",
  "followers": ["u1", "u2", "u3"]
}

-- Better: each relationship is addressable and pageable
followers(follower_id, followee_id, created_at)
INDEX (followee_id, created_at)
INDEX (follower_id, created_at)

04The database families, compared without ten mini-essays

You will rarely get points for listing database categories. You get points for connecting the access pattern to the family, and for naming when that family is a bad fit. Use this section as a decision map, not a catalogue.

Here is the fast path I would use in an interview before diving into the individual families.

Relational / SQL databases

Relational databases store typed rows in tables and let you declare queries in SQL. The optimizer chooses access paths through indexes, joins and scans. PostgreSQL, MySQL, SQL Server and Oracle belong here.

Pick relational when correctness and query flexibility matter: payments, orders, bookings, inventory, accounts, admin workflows and anything with multi-row invariants. You get constraints, transactions, joins, mature indexes, migrations and a deep ecosystem.

Do not pick it as your only answer for very high write ingest, globally distributed low-latency writes, full-text relevance, blob storage or graph traversals with many hops. You can stretch relational databases far, especially with read replicas and partitioning, but name the point where a specialized read model or store enters the design.

Key-value stores

A key-value store is a dictionary: GET key, SET key value, DEL key. Redis, Memcached, Aerospike and DynamoDB in simple key-value usage fit this mental model, although several of them offer richer structures.

Pick key-value for sessions, cache-aside reads, rate-limit counters, feature flags, idempotency records, leaderboards and predictable primary-key lookups. Redis is often memory-first and excellent for sub-millisecond cache patterns. DynamoDB is managed, partitioned and durable, with partition key plus optional sort key access.

Do not pick it when you need ad hoc queries, joins or cross-item transactions as the main path. If your design needs "find all orders where status is pending and amount greater than 5,000", you either need planned indexes and access patterns or a different store.

A note on DynamoDB and single-table design

DynamoDB deserves a separate mention because it often appears in interviews as both a key-value and document-like store. The physical idea is still access-pattern driven: every item has a partition key, and many designs add a sort key so related items can live together and be scanned in order.

PK = USER#u1,  SK = ORDER#2026-05-01#o1
PK = USER#u1,  SK = ORDER#2026-05-02#o2
PK = ORDER#o1, SK = ITEM#p7
PK = ORDER#o1, SK = ITEM#p9

This is the single-table design idea: different entity types share one table, and the key prefixes encode the queries you already know. "Get a user's orders" reads PK = USER#u1 with a sort-key prefix. "Get order items" reads PK = ORDER#o1. A global secondary index handles a different access pattern, such as orders by status and date.

The trade-off is planning. DynamoDB is excellent when your access patterns are stable, traffic is spiky, and you want managed partitioning. It is painful when the product team keeps asking new ad hoc questions. In that case, pair it with a stream-fed search index or warehouse instead of pretending the base table can answer everything.

Document databases

Document databases store JSON-like documents, often BSON or JSON, grouped into collections. MongoDB, Couchbase, Firestore and DocumentDB are common examples. The modeling choice is embed versus reference.

Pick document when a record is naturally aggregate-shaped and usually read as one unit: user profiles with bounded addresses, product catalog entries, CMS pages, device configurations or event metadata. Documents are pleasant when the schema evolves and not every record has the same fields.

Do not pick it for unbounded children, heavy cross-document transactions or highly relational reporting. MongoDB supports multi-document ACID transactions since 4.0, but if every write path needs them, you may be fighting the model. Keep most operations single-document when possible.

Wide-column / column-family databases

Wide-column stores such as Cassandra, ScyllaDB, HBase and Bigtable are best understood as distributed sorted maps. A partition key chooses the nodes. Clustering columns sort rows inside that partition. This is not the same thing as columnar OLAP storage.

Pick wide-column for high write throughput and query patterns that are known in advance: messages by conversation, events by device and time, activity feeds, IoT data, and large append-heavy datasets spread across many partitions.

Do not pick it when you need joins, arbitrary filters, strong multi-row transactions or global secondary-query flexibility. Hot partitions are the classic failure. A low-cardinality key like status or one celebrity user's feed can concentrate traffic on a small set of nodes.

The partition and clustering-key layout is the most important picture for this family.

Graph databases

Graph databases store nodes and edges as first-class data. Neo4j, Neptune, JanusGraph and TigerGraph are typical names. Their strength is relationship traversal, not replacing every relational model.

Pick graph for variable-depth relationships: fraud rings, social recommendations, identity graphs, network topology, permission hierarchies and knowledge graphs. A query like "friends of friends who bought this item" can be a natural traversal instead of a growing chain of self-joins.

Do not pick it for ordinary CRUD with a few joins. Graph stores add operational and modeling complexity. If your relationship depth is one or two and the query set is predictable, relational tables with indexes are often enough.

Time-series databases

Time-series databases make timestamped data cheap to ingest, compress, retain and aggregate. InfluxDB, TimescaleDB, Prometheus, QuestDB and Timestream live in this area.

Pick time-series for metrics, traces, sensor readings, monitoring, financial ticks and any workload that writes mostly append-only points and queries by time window plus labels. Features like chunking, compression, downsampling and retention policies are the product.

Do not pick it as the system of record for mutable business entities. A metric point is not an order. Also be careful with high-cardinality labels, such as user id as a metric label for every event, because index cardinality can explode.

Search databases

Search databases build inverted indexes: terms map to documents, then queries intersect terms and rank results. Elasticsearch, OpenSearch, Solr, Typesense and Meilisearch are common choices.

Pick search for full-text search, relevance ranking, autocomplete, faceted catalog filters, log exploration and geospatial-style search features. Elasticsearch also stores doc values for sorting and aggregations, which is why it shows up in logging stacks.

Do not pick it as the authoritative store for orders, payments or inventory. It is usually a derived store fed from a primary database through events or CDC. Elasticsearch has near-real-time refresh behavior, often around one second by default, so newly written documents may not be searchable immediately.

Object storage

Object storage is a flat namespace of blobs addressed by bucket and key. Amazon S3, Google Cloud Storage, Azure Blob Storage and MinIO are examples. It is not a relational database, but it is a database-adjacent building block in most large designs.

Pick object storage for images, videos, PDFs, backups, logs, data-lake files and static assets. S3 is designed for extreme durability, commonly described as 11 nines for object durability, and storage cost is far lower than SSD-backed OLTP databases.

Do not pick it when you need transactions, fine-grained updates or low-latency structured queries. Make blobs immutable where you can, store metadata in a database, and serve public objects through a CDN.

Vector databases

Vector databases store embeddings and answer nearest-neighbor queries: "which items are close to this vector?" Pinecone, Weaviate, Milvus, Qdrant, pgvector and FAISS-based systems live here.

Pick vector search for semantic search, recommendations, image similarity, anomaly detection and RAG retrieval. The key idea is approximate nearest-neighbor search, often with indexes such as HNSW, trading a little recall for much lower latency.

Do not pick it as your primary business database. Vectors answer similarity, not truth. You still need metadata filters, authorization, deletion semantics and a source-of-truth record to join back to.

Distributed SQL / NewSQL

Distributed SQL systems try to keep SQL and ACID transactions while distributing data across nodes or regions. Google Spanner, CockroachDB, YugabyteDB and TiDB are examples, each with different operational profiles.

Pick distributed SQL when you need relational semantics at horizontal scale, especially multi-region strong consistency or PostgreSQL-like querying after a single-node relational database is no longer enough. It is attractive for financial, inventory and global SaaS workloads that cannot accept eventually consistent writes.

Do not pick it to avoid thinking about latency. Cross-region consensus is bounded by physics. If a write must be agreed across continents, budget tens to hundreds of milliseconds depending on placement. For many systems, a regional relational primary plus replicas is simpler and faster.

That default is not boring. It is disciplined. It lets you spend your interview time on the actual hard parts: which invariants need transactions, which reads may be stale, how replicas lag, how shards are chosen, and how derived data is rebuilt when it drifts.

05Indexes: the read speed you pay for on every write

An index is not magic storage dust. It is a space and write-cost trade-off for read speed. Without an index on orders.user_id, the database may read every table page to answer WHERE user_id = ?. With a useful index, it walks the B-tree we met earlier, lands near the first matching key, then follows leaf pages for the matching range.

The part candidates often miss is the second half of the lookup. The index usually stores keys plus row pointers, so after finding matching keys the database still fetches table rows unless the index contains every column the query needs. That extra heap lookup is why a query can "use an index" and still be slower than expected.

Here is the picture I keep in my head for a normal B-tree index lookup.

Composite indexes: leftmost prefix is the rule to say out loud

A composite index sorts by the first column, then by the second inside each first-column value, then by the third. It behaves like a phone book sorted by surname, first name and city. You can quickly find all Patels, then Patels named Asha, but you cannot jump directly to everyone in Pune without scanning across every surname.

The interview move is to start from query patterns, not from columns. For an orders page, I might pick (user_id, created_at DESC, id DESC) because the hot query is "show this user's latest orders" and it also supports keyset pagination. If the hot query is "pending orders for this user", then (user_id, status, created_at DESC) is a better fit.

Covering, partial and expression indexes

A covering index contains all the columns needed by a query, so the engine can answer from the index without visiting the table. In PostgreSQL you may use INCLUDE columns for this; in MySQL InnoDB, secondary indexes already include the primary key, but other selected columns still need to be part of the index if you want a covering read.

-- Latest orders page: filter, sort and return from the index when possible.
CREATE INDEX idx_orders_user_feed
ON orders (user_id, created_at DESC, id DESC)
INCLUDE (status, total_amount);

A partial index stores only rows that match a predicate. This is excellent when the hot path is a small subset, like pending orders, active sessions or unprocessed jobs. A functional index stores an expression, for example LOWER(email), so a case-insensitive lookup does not scan every user.

CREATE INDEX idx_pending_orders
ON orders (user_id, created_at DESC)
WHERE status = 'PENDING';

CREATE INDEX idx_users_email_lower
ON users (LOWER(email));

Selectivity matters. An index on a boolean or a three-value status column often loses to a table scan if it matches 30% or 40% of rows, because the engine would bounce through many random row fetches. That same low-cardinality column can be useful as the second or third column after a selective prefix like user_id or tenant_id.

Use EXPLAIN, but read it like a detective

When you add an index, do not stop at "the query got faster on my laptop". Look at the plan. You are checking whether the engine estimates the right number of rows, whether it uses an index seek or a scan, and whether it sorts in memory because your index order does not match the ORDER BY.

This is also why cardinality estimates matter more than index count. If the planner thinks a predicate returns 10 rows but it returns 1 million, it may pick a nested-loop plan that explodes. In a design interview you do not need to recite planner internals, but saying "I would verify with EXPLAIN ANALYZE against realistic data" signals production judgment.

OFFSET pagination and N+1 queries are index problems too

OFFSET 100000 LIMIT 20 still asks the database to walk and discard 100,000 ordered entries before returning 20. Keyset pagination uses the last seen key as a cursor, so the engine seeks into the index and reads the next page. The index must match the ordering, for example (created_at DESC, id DESC).

The N+1 query problem is the other common performance leak. One query loads 100 orders, then the code runs 100 more queries to load users or payments. Batch with WHERE id IN (...), join where appropriate, or use a DataLoader-style batcher in GraphQL. An index helps each lookup, but avoiding 100 round trips helps more.

06Transactions and ACID: where the safe boundary ends

A money transfer is the classic example because it is small and unforgiving. Debit account A, credit account B, then commit. If the process crashes halfway through, a transaction keeps money from disappearing or doubling. The database gives you a safe box around those changes.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
  UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;

ACID is the set of promises around that box. Atomicity means all or nothing. Consistency means constraints and valid states are preserved, although business rules still need application logic. Isolation means concurrent transactions should not step on each other in ways your chosen isolation level forbids. Durability means committed data survives crashes.

Durability is where a practical detail matters. Databases write changes to a write-ahead log before acknowledging commit, then flush that log to durable storage depending on configuration. If you disable or relax fsync for throughput, you may acknowledge commits that vanish on a crash. That can be fine for clickstream ingestion. It is not fine for wallet balances.

The recommended default is boring: keep strongly coupled invariants inside one database transaction for as long as your architecture allows it. Split databases when ownership, scaling or compliance forces the split. Then be honest that you moved from ACID to workflow coordination.

07Concurrency control: locks, versions and the rows in between

Here is the problem in one line: two users read available = 1, both decide they can buy, and both write back a valid-looking result. The database needs help deciding whether the second write should wait, fail, retry or be allowed.

Pessimistic locking: block first, avoid retry later

Pessimistic locking assumes conflict is likely. You lock the row before making the decision, usually with SELECT ... FOR UPDATE. Other transactions that want the same row wait until you commit or roll back.

BEGIN;
SELECT available
FROM inventory
WHERE product_id = 42
FOR UPDATE;

UPDATE inventory
SET available = available - 1
WHERE product_id = 42 AND available > 0;
COMMIT;

This is a good fit for high-contention rows like seats, inventory and account balances, where a failed retry is expensive or confusing. It is a bad fit for long user think-time flows, because locks held for seconds can pile up blocked transactions behind them.

Deadlocks are normal; unhandled deadlocks are bugs

Locks introduce another failure mode: deadlock. Transaction A locks row 1 then waits for row 2, while transaction B locks row 2 then waits for row 1. Databases detect this cycle and abort one transaction so the other can continue.

The fix is partly design and partly discipline. Touch rows in a consistent order, keep transactions short, avoid waiting on network calls while holding locks, and retry transactions that fail with a deadlock or serialization error. A retry loop is not a bandage here; it is part of the contract when you choose locking or serializable isolation.

Optimistic concurrency: proceed, then detect conflict

Optimistic control assumes conflicts are rare. You read a version, do the work, and update only if the version is unchanged. If the update affects zero rows, somebody else got there first and you retry or show a conflict.

UPDATE inventory
SET available = available - 1,
    version = version + 1
WHERE product_id = 42
  AND version = 7
  AND available > 0;

This is the right default for profile edits, drafts and low-contention business objects. It keeps latency low when conflicts are rare, but under high contention it can turn into a retry storm. In interviews, say the trade-off: locks cost waiting, optimistic control costs retries.

MVCC: readers get old versions instead of blocking writers

Modern relational databases usually use MVCC, multi-version concurrency control. Instead of overwriting a row in place from every reader's perspective, the database keeps row versions with visibility metadata. A transaction reads the version visible to its snapshot, while a writer can create a newer version.

This is why ordinary reads in PostgreSQL and InnoDB do not block ordinary writes, and ordinary writes do not block ordinary snapshot reads. The price is cleanup. Old versions must stay around while transactions can still see them, so long-running transactions can cause bloat and slow vacuum or purge work.

08Isolation levels and anomalies: the table interviewers expect

Start with the anomalies. A dirty read reads data that later rolls back. A non-repeatable read reads the same row twice and sees different committed values. A phantom reruns a predicate and sees new matching rows. A lost update overwrites another committed update. Write skew lets two transactions update different rows after reading the same predicate, breaking a cross-row invariant.

The clean version of the table is below. Treat it as the SQL-standard mental model, then add the engine notes under it because PostgreSQL and MySQL InnoDB do not line up perfectly with the simple labels.

Now the database-specific part. PostgreSQL defaults to READ COMMITTED. Its REPEATABLE READ is snapshot isolation, so a transaction gets a stable snapshot and PostgreSQL prevents dirty reads, non-repeatable reads and phantoms for ordinary reads, but write skew can still happen. PostgreSQL SERIALIZABLE uses SSI, serializable snapshot isolation, and may abort one transaction with a serialization failure. Your application must retry.

MySQL InnoDB defaults to REPEATABLE READ. InnoDB uses consistent snapshots for plain reads, and next-key locks for many locking reads and writes, which prevents many phantom patterns in practice. It is still not the same as SERIALIZABLE for every invariant. If you are protecting "at least one doctor on call" or "total reserved seats must not exceed capacity", name the invariant and choose explicit locks, constraints or SERIALIZABLE.

Write skew is the anomaly that separates memorising from understanding, so here is the timeline interviewers like.

09Consistency models: what a read is allowed to see

Strong consistency means that once a write succeeds, later reads see it, no matter which replica serves the read. The cost is coordination: writes may wait for a leader, quorum or consensus, and partitions may force the system to reject work rather than lie.

Eventual consistency means replicas may disagree for a while, then converge if no new writes arrive. This is acceptable for like counts, feed rankings, recommendations and search indexes. It is not acceptable for "did my payment succeed?" unless the UI clearly models pending state.

Between those poles are user-facing guarantees that are often enough.

CAP is useful only if you say it carefully. During a network partition, a distributed system chooses between returning available but possibly stale answers, or preserving consistency by rejecting or delaying some operations. Partition tolerance is not a feature you opt into; networks fail.

10Replication: copies, lag and failover

In leader-follower replication, one leader accepts writes and followers replay its log. PostgreSQL streams WAL; MySQL streams the binlog or uses group replication, depending on setup. Reads may go to followers, but writes normally go to the leader.

The first trade-off is synchronous versus asynchronous replication. Synchronous replication waits for at least one replica acknowledgement before reporting success, which reduces data loss risk but adds latency and can reduce availability if the replica is slow. Asynchronous replication acknowledges after the leader commits locally, then ships changes in the background. It is lower latency, but a leader crash can lose acknowledged writes that had not reached a replica.

Read-your-writes is the practical problem. After a user changes their email, their next page load must not show the old email because it hit a lagging follower. Good options are: route that user's reads to the leader for 30 seconds after a write, attach a version token to the session and choose a replica that has caught up, or fall back to the leader when lag crosses a threshold.

Failover is the second problem. If the leader dies, a follower must be promoted. Manual failover can take minutes and is common for planned maintenance. Automated failover can take tens of seconds in managed systems, but it must prevent split brain, where two nodes both think they are leader and accept divergent writes.

Multi-primary replication: low local latency, hard conflicts

Multi-primary, sometimes called active-active, lets more than one region accept writes. The upside is local write latency and regional availability. The catch is conflict resolution: if Mumbai and Singapore both update the same account, the system needs a rule, and "last write wins" can silently lose valid work.

I only like multi-primary for data with natural merge rules, such as shopping cart additions, user preferences with field-level merges, or append-only events. For balances, inventory and uniqueness constraints, a single write leader or consensus-backed path is usually the safer default. If the interviewer asks for multi-region writes, name the invariants that cannot tolerate conflict.

Quorums: N, R and W in one minute

Some replicated databases let you tune how many replicas must participate. With N replicas, W write acknowledgements and R read responses, the rule of thumb is R + W > N if you want reads and writes to overlap on at least one up-to-date replica. For N = 3, W = 2 and R = 2 is a common quorum shape.

Quorums are not a free lunch. Higher W increases write latency and reduces write availability. Higher R increases read latency and fan-out. The default I would say in an interview is: use leader-follower for relational systems unless the problem calls for a Dynamo-style store, then tune quorum per data type.

11Partitioning and sharding: splitting data without splitting your brain

First separate vertical partitioning from horizontal sharding. Vertical partitioning splits by concern: identity fields in one table, profile fields in another, audit logs elsewhere. It can reduce hot row width and clarify ownership. Horizontal sharding splits rows of the same logical table across nodes, usually because one machine no longer handles the data size or traffic.

The shard key is the decision you should slow down for. A good key has high cardinality, spreads traffic uniformly, keeps common queries local to one shard and avoids hotspots. For a user-centric product, user_id is often a good starting point. For a multi-tenant SaaS, tenant_id gives locality, but large tenants may need dedicated handling.

Modulo sharding, hash(key) % N, is easy until you resize. Add a fifth shard to a four-shard cluster and many keys move because the modulo changed. Consistent hashing places nodes and keys on a ring, so adding a node moves only part of the keyspace, especially when you use virtual nodes.

Hot partitions are the failure mode to name. If you shard by created_at, today's shard gets nearly all writes. If one product goes viral, product_id can overload one shard. Common fixes are salting or bucketing the hot key, moving large tenants to dedicated shards, or changing the write path so hot counters are aggregated asynchronously.

Global secondary indexes are the hidden sharding tax

Once you shard by user_id, a lookup by email is no longer local unless the email index is partitioned the same way. You either scatter the lookup to every shard, keep a separate lookup table from email to user id, or use a search/indexing system fed asynchronously. Each option changes consistency.

This is why I ask for access patterns before choosing a shard key. If login is by email, we may keep a strongly consistent identity table keyed by email while sharding high-volume user activity by user id. The goal is not to make every query single-shard. The goal is to keep the hot, user-facing queries single-shard and move the rest to explicit supporting systems.

Cross-shard operations are where the bill arrives. A query like "total revenue across all users" becomes scatter-gather: ask every shard, wait for the slowest one, then merge. Cross-shard transactions need two-phase commit or a saga, both of which add failure modes. Global ordering requires a distributed sort or an external index.

12Caching and databases: a useful lie with an expiry date

A cache is a performance tool, not a truth machine. The database remains the source of truth, and the cache gives you lower latency by accepting that a value may be stale for a small window. The design question is not "can we cache this?" It is how wrong can this answer be, and for how long?

Use that question to separate safe caches from dangerous ones. A product description can be stale for a few minutes. A restaurant availability flag can be stale for a few seconds. A payment status shown as final should not be stale unless your product explicitly has a "pending" state.

Cache-aside is the default pattern

Cache-aside, also called lazy loading, means the application controls the cache. On a read, you check Redis or Memcached first. On a miss, you read the database, store the result with a TTL, then return it. On a write, you update the database and invalidate the relevant cache key.

def get_product(product_id):
    key = f"product:{product_id}"
    product = cache.get(key)
    if product is not None:
        return product

    product = db.query_product(product_id)
    cache.set(key, product, ttl_seconds=300)
    return product


def update_product_price(product_id, price):
    db.update_product_price(product_id, price)
    cache.delete(f"product:{product_id}")

The small but important detail is delete, not update. Updating the cached value sounds fresher, but two writers can race and leave an older computed value in cache after a newer database write. Deleting is safer because deletes commute: if two writers delete the same key, the next read goes back to the database and repopulates from the latest committed state.

Here is the read and write flow I would draw before talking about Redis settings.

TTL, event invalidation and versioned keys

TTL-based invalidation is the cheapest backstop. If you cache product:42 for 300 seconds, the worst-case staleness is roughly five minutes, assuming clocks and eviction behave normally. A shorter TTL reduces staleness but increases database load. A longer TTL protects the database but risks users seeing old data.

Event-driven invalidation makes the stale window smaller. When a product changes, the product service publishes ProductUpdated, and cache listeners delete product:42. This is more work because the event path must be monitored, retried and made idempotent, but it is the right move for data where five minutes is too stale.

Versioned keys avoid in-place invalidation. Instead of product:42, the app reads product:42:v7, where v7 comes from the database row version. A new product version uses a new cache key, so stale keys become unreachable and expire naturally. The cost is extra key lookup and old versions consuming memory until TTL or LRU eviction clears them.

Stampedes and negative caching

A cache stampede happens when a hot key expires and many requests miss at the same time. If 1,000 requests for product:42 all miss at once, the database receives 1,000 identical reads and the cache gets 1,000 identical fills. This is how a cache meant to protect the database becomes the thing that takes it down.

The usual fixes are request coalescing, stale-while-revalidate and TTL jitter. Request coalescing lets one request rebuild the key while the rest wait briefly or serve stale data. Stale-while-revalidate returns the old value immediately and refreshes in the background. TTL jitter adds randomness, for example 270 to 330 seconds instead of exactly 300, so a batch of keys does not expire together.

Negative caching is the same idea for absence. If attackers or buggy clients ask for millions of non-existent users, cache NOT_FOUND for a short TTL such as 30 or 60 seconds. Be careful with permissions: caching "not found" for a private object can leak or hide access changes if the key does not include the caller context.