Interview prompt. A team tells you "Postgres cannot handle our scale - we are moving to a document store next quarter". The table in question has 400 million rows and the complaint is p99 read latency. Take it from here.

What the interviewer is testing

Whether you can separate a data-model problem, a capacity problem and a query problem before agreeing to a year of migration work - and whether you ask for evidence before offering an opinion. The stated diagnosis ("Postgres cannot handle it") and the stated symptom ("p99 read latency") are not obviously connected, and a strong candidate says so politely in the first minute.

The clarifying questions that change the answer

• Is the constraint reads or writes? Replicas fix reads. Nothing about a store change fixes writes that one machine cannot absorb - and that is the only threshold where sharding is genuinely forced.
• What is the working set against available memory? A 400-million-row table whose hot set fits in RAM behaves nothing like one whose does not, and the fix differs completely.
• Which queries are slow, and what do their plans say? Estimated against actual rows, one query at a time. A p99 driven by one unindexed predicate is an afternoon's work.
• Is the slow path one query or a fan-out? A request issuing 50 sequential queries has a latency problem that no storage engine will fix.
• How many indexes are on the table? Each one is write amplification, and an over-indexed table is a common cause of degradation that reads as "the database is slow".
• What does replica lag look like at peak? Reads served from a lagging replica produce both latency and correctness complaints.

The ladder a strong answer walks

1. Measure: buffer cache hit rate, plans for the top queries, index bloat, lock waits.
2. Fix the query layer: indexes, plans, N+1 fan-outs, pagination that scans.
3. Separate workloads: read replicas for analytics, a connection proxy if connection count is the real limit.
4. Partition by time or tenant, which often restores the locality that was lost as the table grew, with no application change.
5. Scale vertically, which remains astonishingly effective and is the cheapest step by far.
6. Shard the relational store before changing data model. Notion's documented path is the reference: they moved to 480 logical shards across 32 physical Postgres instances in 2021, and in 2023 redistributed the same 480 logical shards over 96 instances - the resharding was a rebalance rather than a re-architecture, because the logical shard count had been chosen well above the host count on day one.

What the proposed migration actually costs

Every query rewritten, transactions and joins given up or re-implemented in application code, a dual-write period with reconciliation, and a team learning a new failure model while on call for it. For a latency symptom that may be an index, that is an enormous bet. Say it in terms of the decision being reversible or not: an index is reversible in minutes; a store change is not reversible at all.

Common weak answers

• Agreeing and designing the new store. It answers the question asked and not the problem.
• Refusing on principle. Document stores are the right answer for some shapes - highly variable documents, no cross-entity transactions, access always by one key.
• "Just add caching." Which may help, and hides the cause while adding an invalidation problem.

What a strong answer adds

A falsifiable decision rule, agreed in advance: "we shard or change stores when sustained write throughput or working-set size exceeds what one primary can hold, measured as X; read latency is not that signal." Plus a benchmark on a restored copy rather than a debate, and the observation that the migration's own risk - dual writes, reconciliation, a new on-call surface - has to be compared against a fix whose blast radius is one CREATE INDEX.