This page needs a newer browser to run its tools. You can still read everything here.

Database Management Systems (DBMS) Module 15

Distributed and cloud databases interview questions

Interview questions on distributed and cloud databases: warm-ups, then what each harder question tests and how a good answer starts.

15 questions: 4 warm-ups and 11 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher

What is the difference between synchronous and asynchronous replication?

Answered in the lesson Replication: leaders, followers and quorums
Fresher to mid

What is partition pruning, and when can it not happen?

Answered in the lesson Partitioning and sharding
Fresher to mid

Why is two-phase commit called a blocking protocol?

Answered in the lesson Distributed transactions: 2PC, consensus, sagas
Fresher to mid

Is the CAP theorem "pick any two of consistency, availability and partition tolerance"?

Answered in the lesson CAP, PACELC and consistency models

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Mid to senior

A PostgreSQL primary crashed and its asynchronous standby was promoted. Customers report orders confirmed in the last few seconds that are now missing, and the old primary's disk still holds them. What happened, how do you get the orders back, and what do you change?
What it tests

The asynchronous loss window, keeping the old primary from rejoining as it is, recovering lost transactions safely, and choosing synchronous replication with its latency cost.

First step

Before anything else, make sure the old primary cannot start and accept writes. Then find where the two histories split: the log position the standby had replayed when it was promoted. Every commit on the old primary after that position was confirmed to a customer and never reached the new primary.
Follow-ups
- How do you find exactly which orders were lost?
- How do you bring the old primary back as a standby?
- What does synchronous replication cost if the standby is 30 ms away?
From the lesson Replication: leaders, followers and quorums
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A key-value store runs with N = 3, W = 2 and R = 2. A user refreshes a page twice and sees a new value, then an older one. How can that happen when R + W > N, and what would you change?
What it tests

What quorum overlap promises and what it leaves out: writes in flight, failed writes, sloppy quorums, read repair and concurrent writes.

First step

Separate acknowledged writes from writes in flight. R + W > N only promises that a read meets the latest write that W replicas have acknowledged. Ask whether the first read ran while a write was still collecting its acknowledgements, and whether the store counts stand-in nodes towards W during outages.
Follow-ups
- Does read repair fix it, and when does it run?
- What would W = 3 and R = 1 change?
- How do sloppy quorums make it worse?
From the lesson Replication: leaders, followers and quorums
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

An orders service on one PostgreSQL server must grow to 16 shards. The busiest calls list one customer's orders, track one order by its id, and update stock per product. Which shard key do you choose, and what happens to each call?
What it tests

Choosing a key from the access patterns, counting the shards each call asks, keeping data used together on one shard, and serving the calls the key does not cover.

First step

List the calls with their rates and the columns each one filters by. For each candidate key, count the shards every call must ask: one when the call fixes the key, all 16 otherwise. Then check which tables are joined or changed in the same transaction.
Follow-ups
- How does tracking an order by its id find the right shard?
- Where does the stock table live, and why not with the orders?
- What changes if one customer places a tenth of all orders?
From the lesson Partitioning and sharding
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

A social app shards its posts by author_id over 32 shards. When a celebrity posts, the shard that holds them saturates while the other 31 are nearly idle. Why does consistent hashing not help, and how do you fix it without resharding everything?
What it tests

Separating where keys live from where requests go, splitting hot keys, caches and read replicas, and what each fix costs reads and writes.

First step

Measure before choosing: requests per key on the hot shard, split into reads and writes. One key always has one owner, whatever hash places it, so the fix must change the key or the number of copies, and the ratio of reads to writes decides which.
Follow-ups
- Into how many sub-keys do you split a hot key, and who decides which keys are hot?
- What does a write cost after the split?
- Would a cache in front of the shard be enough?
From the lesson Partitioning and sharding
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

Users are sharded by user_id across 8 PostgreSQL servers, and every email address must stay unique across all of them. Each shard has a unique index on email. Why is that not enough, and how would you enforce it?
What it tests

Why local indexes cannot enforce uniqueness across shards, a table keyed by the email with a claim step, and its failure cases.

First step

Show the race first: two sign-ups with the same email land on two shards, each unique index checks only its own rows, and both succeed. Then pick one place that sees every email: a table sharded by the email itself.
Follow-ups
- The email table and the user's row live on different shards. How do you keep them in step?
- What happens when a user changes their email?
- Can PostgreSQL enforce it inside one server with a partitioned users table?
From the lesson Partitioning and sharding
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes
Mid to senior

After a deploy, an UPDATE on a busy PostgreSQL table hangs, and pg_prepared_xacts shows a transaction prepared 40 minutes ago by a payment coordinator that has since been replaced. What is going on, what do you do with the prepared transaction, and how do you prevent a repeat?
What it tests

Recognising an in-doubt transaction, finishing it from the coordinator's decision instead of guessing, and the monitoring and settings that stop it recurring.

First step

Do not roll it back yet. The prepared transaction holds its row locks until someone commits or rolls it back, so the UPDATE is waiting on it. Find the coordinator's record for that global identifier first: its decision, and the other participants of the same transaction.
Follow-ups
- The coordinator's log is lost. What now?
- What else does a 40-minute prepared transaction harm besides the locked rows?
- How would you alert on this before users notice?
From the lesson Distributed transactions: 2PC, consensus, sagas
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

A teammate argues: "Every shard is a Raft group of three servers, so we can drop two-phase commit for transfers between shards." Is that right? Explain what each protocol guarantees and design a cross-shard transfer that does not block when a server fails.
What it tests

Separating agreement among replicas from atomic commit across shards, and combining them as two-phase commit over consensus groups, with the cost in round trips.

First step

State what each one decides. Raft makes the three replicas of one shard agree on that shard's log. Atomic commit makes two shards agree to apply a transfer or not, and either shard may refuse its part. Then ask which failure the teammate wants to survive.
Follow-ups
- Where does the coordinator's decision live in your design?
- How many round trips does a cross-shard commit cost compared with a single-shard one?
- Could a saga do the transfer instead, and what would users see?
From the lesson Distributed transactions: 2PC, consensus, sagas
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A travel app books a train seat, a hotel room and a payment through three services with separate databases. Design the saga: the order of the steps, the compensations, how messages travel, and what happens when the hotel cannot be cancelled after the payment has been refunded.
What it tests

Ordering steps around the pivot, compensations that are idempotent and retried, the outbox and inbox, and failures of the compensations themselves.

First step

Sort the steps by whether they can be undone. Reservations that expire or can be cancelled go first, the payment capture is the pivot, and anything that cannot be undone comes after it. Then write each step's compensation and the message that triggers it.
Follow-ups
- Where does each service write the message it sends, and why?
- The payment service receives the same request twice. What prevents a double charge?
- What does the user see while the saga is half done?
From the lesson Distributed transactions: 2PC, consensus, sagas
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A bank runs in two data centres, and the link between them fails for ten minutes. Customers on both sides want to check balances, withdraw cash and pay bills. Which operations would you keep available, which would you refuse, and what has to happen when the link returns?
What it tests

Applying CAP per operation instead of per system, bounding the risk of the operations kept available, and reconciling afterwards.

First step

Classify the operations by what a stale answer costs. A balance shown slightly old harms little; two withdrawals on two sides from the same balance can overdraw it. Then decide, operation by operation, what each side may do alone and within which limits.
Follow-ups
- Would you allow withdrawals at all, and under what limit?
- How do you reconcile the two sides afterwards?
- What does the customer see on each side during the outage?
From the lesson CAP, PACELC and consistency models
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

Your reports run under SERIALIZABLE on a PostgreSQL standby, so a teammate says they always see the latest committed data. Is that right? Explain serializability, linearizability and strict serializability using what PostgreSQL actually does on a primary and on a standby.
What it tests

Precise definitions, the real-time gap between serializable and strict serializable, write skew, and what PostgreSQL offers on hot standbys.

First step

Start from what the standby is: a copy that replays the primary's log a little behind, and PostgreSQL refuses SERIALIZABLE on a hot standby. Then separate a serial order of transactions from a real-time order, since a stale snapshot can still be a valid serial order.
Follow-ups
- Give a history that is serializable but not strictly serializable.
- Give a history that is linearizable for every key but not serializable.
- How would you make the reports see every transaction committed before they start?
From the lesson CAP, PACELC and consistency models
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

A product has users in Mumbai, Frankfurt and Virginia and one primary database in Mumbai. Product managers want every read to show the latest data everywhere and pages to load fast. Use PACELC to explain what you can promise, with numbers, and propose a design.
What it tests

The latency branch of PACELC, round-trip arithmetic between regions, per-operation consistency, and session guarantees as a middle ground.

First step

Put numbers on the round trips first: a linearizable read from Frankfurt or Virginia must reach the primary in Mumbai or a replica known to be up to date, which costs a long-distance round trip. Then ask which reads really need the latest data and which need only a user's own writes.
Follow-ups
- What does each read cost from Virginia if it must be linearizable?
- Which pages can use local replicas, and with what guarantee?
- What changes if writes must also be accepted locally in every region?
From the lesson CAP, PACELC and consistency models
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes

The lessons of this module

Replication: leaders, followers and quorums Free lesson, Pro answers: 35 minutes
Partitioning and sharding Free core, Pro depth: 12 of 35 minutes free
Distributed transactions: 2PC, consensus, sagas Free core, Pro depth: 13 of 40 minutes free
CAP, PACELC and consistency models Free core, Pro depth: 12 of 35 minutes free

All Database Management Systems (DBMS) interview questions · The Database Management Systems (DBMS) track

Warm-up questions

What is the difference between synchronous and asynchronous replication?

What is partition pruning, and when can it not happen?

Why is two-phase commit called a blocking protocol?

Is the CAP theorem "pick any two of consistency, availability and partition tolerance"?

Questions with model answers

A PostgreSQL primary crashed and its asynchronous standby was promoted. Customers report orders confirmed in the last few seconds that are now missing, and the old primary's disk still holds them. What happened, how do you get the orders back, and what do you change?

A key-value store runs with N = 3, W = 2 and R = 2. A user refreshes a page twice and sees a new value, then an older one. How can that happen when R + W > N, and what would you change?

An orders service on one PostgreSQL server must grow to 16 shards. The busiest calls list one customer's orders, track one order by its id, and update stock per product. Which shard key do you choose, and what happens to each call?

A social app shards its posts by author_id over 32 shards. When a celebrity posts, the shard that holds them saturates while the other 31 are nearly idle. Why does consistent hashing not help, and how do you fix it without resharding everything?

Users are sharded by user_id across 8 PostgreSQL servers, and every email address must stay unique across all of them. Each shard has a unique index on email. Why is that not enough, and how would you enforce it?

After a deploy, an UPDATE on a busy PostgreSQL table hangs, and pg_prepared_xacts shows a transaction prepared 40 minutes ago by a payment coordinator that has since been replaced. What is going on, what do you do with the prepared transaction, and how do you prevent a repeat?

A teammate argues: "Every shard is a Raft group of three servers, so we can drop two-phase commit for transfers between shards." Is that right? Explain what each protocol guarantees and design a cross-shard transfer that does not block when a server fails.

A travel app books a train seat, a hotel room and a payment through three services with separate databases. Design the saga: the order of the steps, the compensations, how messages travel, and what happens when the hotel cannot be cancelled after the payment has been refunded.

A bank runs in two data centres, and the link between them fails for ten minutes. Customers on both sides want to check balances, withdraw cash and pay bills. Which operations would you keep available, which would you refuse, and what has to happen when the link returns?

Your reports run under SERIALIZABLE on a PostgreSQL standby, so a teammate says they always see the latest committed data. Is that right? Explain serializability, linearizability and strict serializability using what PostgreSQL actually does on a primary and on a standby.

A product has users in Mumbai, Frankfurt and Virginia and one primary database in Mumbai. Product managers want every read to show the latest data everywhere and pages to load fast. Use PACELC to explain what you can promise, with numbers, and propose a design.