Ch.22: System Design: Move a Database Without Losing Writes

0:00 The order table finishes copying. Then a shopper checks out, and the old database commits order 417. Confirmation email arrives. So we're done? The team flips traffic. Support searches the destination and finds nothing. The copier was finished before her order existed. And one old app instance still points at the old database. Oh. Her receipt is real, but support has an empty screen. The move missed a committed order, and that old instance could accept another one after the switch. Both break the same promise. The fix is to carry the later writes after the snapshot and keep the old writer dead after handoff.

0:39 Checkout pauses while we prove both. Now follow those two failure paths across the full route. Shoppers reach an application write gate. The old PostgreSQL cluster owns orders. A snapshot moves existing rows, and a stream follows new writes into the destination. A validator checks the copy. A cutover controller decides when the gate can point at the new primary. Hold on, a stale database connection is still on the picture. Which write path do we actually shut off? The source write path, including connections that bypass the application's gate.

1:13 Our cutover controller has to prove they are blocked before the target opens. Right. That controller is our proposed design, not a PostgreSQL button. Until the handoff, only the old cluster accepts orders. Afterward, only the new one may. Fair. The controller is ours to build. Keep that route in view; later we'll trace order 417 across it. Her confirmation and support's result have to agree. From that route, picture a shopper checking out while the old order table is being copied. She should still be able to read the catalog and place orders through most of the move. The operators must bring old orders over and carry every new one made during the copy. Right, her confirmation has to survive the move.

1:59 We also have to switch application reads and writes to the destination. What does the shopper see during the switch? A short, truthful write pause. A new checkout attempt gets a retry instruction instead of a fake success. An admitted request either finishes on the source or leaves an uncertain outcome for its caller. What if it committed there, but the browser never got the reply and retries after the switch? Each checkout stores a unique request key with its order. The key moves with the data and remains unique on the target.

2:29 A retry looks up that key before creating another order. If we abort before promotion, every accepted order and key remains on the source. Now that retries are explicit, the non-functional requirements start with durability. No acknowledged order write can be lost. I think a quiet cutover means nothing if her order vanishes. An HTTP success must trace to a durable commit on the authority for that moment. And exactly one authoritative writer. If source and target both accept independent order writes, copying faster cannot reconcile their divergent histories by magic.

3:01 We also need a pause we can measure and cap, data we can validate, and a compatible target schema. There's a source-safety limit too: can a stalled stream fill the database we're trying to protect? Yes. If the subscriber stalls, retained logs pile up on the source and can exhaust its disk. That needs an abort limit, just as cutover needs a measured pause. My first thought was to promise uninterrupted checkout. But I'd rather ask a shopper to retry than confirm an order that disappears. That lost-order risk starts at the copy boundary.

3:35 The order table's snapshot finishes; order 417 commits a moment later. The copier will never visit that table again. Right. It finished too soon for her. Could we stop checkout until the whole copy finishes? We could freeze writes for the whole transfer, but that turns a live move into a long outage. So keep orders flowing and follow them with a stream. Does that stream start before the copy, or after it? Next, the snapshot needs an overlap with later writes. PostgreSQL's logical replication provides it: an initial snapshot of tables selected for replication, then changes from the source's write-ahead log, the record it uses to describe committed changes.

4:15 The PostgreSQL docs are linked in the description. Wait. I take an unrelated dump, then start the stream when that finishes. If her confirmed checkout happens between them... ...it lands in neither. The migration tool captures a matching start position with the snapshot. I would not call the target safe just because a dashboard says it is almost caught up. Right. That closes the starting gap. It still doesn't freeze the last write while shoppers keep ordering. Once the snapshot and stream overlap, catch-up becomes a moving target.

4:47 The destination applies changes, but the finish line moves with each checkout. It can get close; it cannot certify the final write while the old writer remains open. The replication slot is the source's bookmark for the stream. It keeps log records until that destination subscriber consumes them. The target stops reading; the bookmark stops moving, but the source keeps writing. Retained log can consume storage. The migration itself can threaten the primary we promised to keep healthy. Ah, the move itself could take down the database we're protecting.

5:18 We watch source disk, retained log, apply errors and destination delay. If the stream falls behind beyond our safety budget, we abort this attempt before the source runs out of room. For example, the destination can contain order 417 yet miss the next shopper's update. We need a final boundary after source writes stop. Before the final boundary, the target must be able to replay real writes. PostgreSQL logical replication does not ship every schema change. We deploy compatible table definitions deliberately and hold incompatible changes during the migration. To replay an update, the destination needs to know which row to change.

5:56 Usually a primary key identifies it. PostgreSQL calls that setting replica identity. Without a usable identity, updates or deletes can fail. Rows have identities. Does the next order-number counter move with them? No. The rows move, but that counter doesn't. PostgreSQL doesn't replicate sequence state here, so after old writes stop we check and set it. Otherwise the new primary might generate an already-used order number. Wait, every order row could be right and the next generated number still wrong?

6:28 Yes. We need that counter in the cutover checklist. And a clean replay can still hide a bad copy. If the replay is clean but the copy is wrong, which rows would catch us? We scan the actual order, item and request-key tables. A row still changing can differ briefly; we repeat that check until the broad comparison is clean. Green lag alone proves nothing. Suppose order items were left out of PostgreSQL's publication, its list of replicated tables. Lag stays green while those rows are absent. Wait, the chart can be green because it never knew that table existed?

7:04 Exactly. A green chart can make us stop looking. It measures only the stream we selected, not the missing rows. First verify every required table, field, row and operation is included. If it wasn't, restart from a synchronized copy and stream; then compare all three tables. Right, so a clean comparison can go stale while we're still scanning. Before it starts, record every streamed insert, update and delete by table and row key. Never clear a key just because it matched once. Even an item deleted from order 417 halfway through?

7:38 Yes. After the fence and catch-up, recheck every listed key. Both copies must show that item gone. A mismatch blocks promotion. What if the rows match, but checkout reads them wrong on the new cluster? So let's try shadow reads. Run the same application read against the destination without showing that answer to the shopper. Yes, shadow reads can reveal query or schema surprises. But we need to label the source and target positions. During catch-up, a difference can mean ordinary delay rather than bad data.

8:08 Reading the target is one thing. Imagine a product manager asks, “Can checkout write to both databases during the switch so shoppers never see a retry?” We could, with a coordinator that records both outcomes and reconciles failures. But suppose the source commits and the target times out. We don't know whether that second write landed. So the source accepted the order, but the target's result is unknown. What could we safely tell the shopper? Only that the source committed. Until we resolve the target outcome, claiming the move worked would be dishonest.

8:42 That's another design and another failure path. This one keeps a single primary and an explicit pause. We rehearse target writes in a separate staging copy; the live target gets no application writes before handoff. Okay. What stops that old app connection from writing while we hand over checkout? So the application gate still won't stop your old connection. We close checkout to new requests, so they hear "please retry." But one shopper may already be past the gate, waiting on a commit. Imagine the sign on the door says closed while a shopper is still at the register.

9:18 Do we take the final log position now? No. First let those admitted transactions settle. If one commits and loses its reply, her recorded key lets us find the accepted order. Then fence source writers at the database and prove no more commit can slip in. That's when we capture the final position. A checkout that never settles forces us to cancel this cutover. Target stays unwritable; source still owns orders. The caller can retry with its key, because a timeout doesn't tell us whether the write landed.

9:49 Now the database fence is harder than closing checkout. A sleeping process can retain a source connection. If it wakes after promotion and commits there, we get two histories. Right. A routing flip is not a lock on a lingering session. The fence sits below the application: block new source write connections, terminate existing write sessions after the drain, and verify the application write role cannot commit on the source. Replication still needs its read path to finish catch-up. That's the app role.

10:20 We tend to inventory it and forget the job with its own credential. Take the nightly order job: what happens when it wakes after the fence? Classic. If that credential still works, the job writes to source after target opens. The rollout checklist can say "done" while the databases diverge. So we fixed the copy gap and created a second order history anyway. Then put its credential, consoles and admin paths in the writer inventory. If any can still write source, we do not hand over. Make that old job fail loudly.

10:52 Now the old writer is closed. Record the last source log position, and wait until the destination reports that it has applied through that point. This is the barrier her confirmed purchase has to cross. Okay, the stream got that far. But did the item row deleted during our scan disappear on the target too? Right. The position proves delivery, not equality. Recheck every row touched since the broad scan began, including deletes. Then verify constraints and set the sequence safely. And what if that list is too large to finish inside the pause budget?

11:25 Then keep the new writer closed and cancel this attempt. We can return traffic to the source because the destination has not accepted unique writes. So the changed-row queue got too long, and checkout returns to the original database. Annoying pause, but nobody gets a fake receipt. Once that bounded delta check passes, promote the destination and repoint the application, but keep shopper checkout paused. The controller admits one target-write session for a rollback-only probe. Every other app, job and admin writer stays blocked.

11:56 The stale source connection must still be denied. If that probe's rollback fails, or the stale session sneaks in a source write, can we still return to the source? A source commit after that final position cancels cutover. Source remains authority. Return only if the target database confirms probe rollback and the controller confirms no other target writer got in. If either fact is uncertain, both paths stay closed. With that proof, close the probe and target write role, restore the source route and write role, then verify source alone can write before reopening checkout. So a rolled-back transaction tests the route, not a durable checkout.

12:35 What proves the first real order? Then we need a real shopper order. Reopen checkout; that's when the pause ends. Order 419 is the first target-only commit. It must read back through the application before we send its confirmation. A slow drain or final check can stretch that interruption, so we rehearse those before the switch. And our earlier shopper has a different test: if order 417 committed but her reply got lost, the same request key finds it. If checkout was rejected at the gate, her retry creates one purchase. Either way, we confirm the active database committed before saying success.

13:12 That's the success path. Suppose the final row check fails before promotion. Where does checkout go? With that failed comparison, the target stays closed; we restore source writes. A green stream didn't save the missing row. We know the temptation: patch that row and try again. But we'd be hiding which component failed. We investigate the source application, publication coverage, and target apply before another attempt. Support can still find the confirmed order on source. Now the abort story changes.

13:41 Imagine the destination accepts order 419, then fails. Can we point traffic back? Not safely. The source lacks that new order. Hitting undo on the traffic route would make another confirmation disappear, the exact failure we designed against. Oh, right. Recovery has to start from the new authority, or we need a deliberate reverse migration that carries its unique writes back before source checkout reopens. That plan has its own tested gates and may require a longer write pause. Exactly. The first unique destination write turns rollback from a routing change into a data recovery job. Let's try to break that map. Order 417 missed the copy.

14:19 Is it there when we open the target? Yes. The stream carried its source commit. Its order, item lines and retry key matched, and we rechecked changed rows after the fence. Then I try to write through my old source connection. Denied. That's the arrow we had to kill. Then order 419 commits on the new primary. What breaks if I flip the route back to source? The customer loses that confirmation; source never had 419. We'd repair the destination or move its new orders back before reopening the old checkout path. So the pause costs customers a retry.

14:53 The alternative could erase a confirmed order. The migration has four moves: copy, stream the later changes, compare the data, then transfer write authority under a fence. Yes. For the customer with that confirmation email, the promise is simple: if we said the order committed, it remains in the system that now owns orders. Thanks for listening to Learning Podcasts.