System Design.

Lesson 07 · Migrating a live database

Five Minutes

Notion moved a production Postgres monolith into 480 shards and took the product down for five minutes to do it. The interesting part is not the five minutes — it is the months of machinery that made five minutes possible, and the deadline they were racing.

The win in this lesson: you will know the shape of every safe data migration — the same four phases whether you are sharding a database, changing a column type, or moving to a different store entirely — and you will know what forces the timetable.

1. The deadline nobody chooses

The ordinary symptoms came first: "Engineers on-call often woke up to database CPU spikes, and simple catalog-only migrations became unsafe and uncertain." Annoying, survivable, easy to defer.

Then a different kind of symptom. The Postgres VACUUM process "began to stall consistently, preventing the database from reclaiming disk space from dead tuples" — and that turns a capacity problem into a countdown:

"TXID wraparound would pose an existential threat to the product"

Notion, Herding elephants: lessons learned from sharding Postgres at Notion
What that actually means

Postgres stamps every row with the transaction id that created it, and that counter is 32 bits — it wraps. VACUUM is the process that walks old rows and marks them as permanently visible ("frozen") before the counter can lap them. If VACUUM cannot keep up, the database approaches the point where it can no longer tell old rows from future ones — and rather than corrupt data, Postgres refuses all writes. It is not a slowdown. It is a hard stop, on a schedule set by your write rate, and no amount of hardware buys it off.

This is the cleanest example in the workspace of a scaling deadline that is not about performance. Nobody was complaining about latency. The system was going to stop.

2. Choosing the key, again

Lesson 02 said a partition key is a bet that your traffic is shaped like your data, and that every query must supply it. Notion's reasoning is that rule applied cleanly:

  • "each block belongs to exactly one workspace" — the data has a natural, non-overlapping owner.
  • "users typically query data within a single workspace at a time" — so the key is present in nearly every query, which "avoid[s] most cross-shard joins".
  • They sharded "all tables reachable from the block table via some kind of foreign key relationship" — the unit of sharding is not a table, it is a connected subgraph of the schema.

That third point is the one people underestimate. You cannot shard one table by workspace and leave a related table unsharded, because a join between them would then cross machines. The blast radius of a partition key is everything the foreign keys can reach.

3. The four phases

This is the reusable part. Every safe migration of live data has the same shape:

1 · DOUBLE WRITE every write goes to both stores from now on 2 · BACKFILL copy the history that predates step 1 3 · VERIFY read both, compare, measure the mismatch 4 · FLIP reads move over, old store retires Only phase 4 is irreversible, and only phase 4 is visible to users. Phases 1–3 can run for weeks, can be paused, and can be abandoned entirely with nothing lost but time. Notion's phases 1–3 took months. Phase 4 took five minutes.
The whole art is loading phases 1–3 with as much work as possible so that phase 4 has almost nothing left to do. A risky migration is one where phase 4 is doing something you have not already rehearsed.

Phase 1 — double write, via an audit log

"Create an audit log table to keep track of all writes to the tables under migration. A catch-up process iterates through the audit log and applies each update."

Notion

Note they did not write to both stores inline in the request path. A write to two systems inside one request is two things that can fail independently — and if the second fails, you have diverged silently. Instead the write goes to one place plus an audit log, and a separate process replays it. That makes the second write retryable, restartable, and observably behind rather than invisibly wrong.

Which is the same structural idea as Lesson 01's mcsqueal and the replication stream: record the intent durably, apply it asynchronously. And it works for the same reason — applying a logged update twice is harmless, so the catch-up process can be restarted freely. Idempotence, for the fifth lesson running.

Phase 2 — backfill

"Around three days to backfill the production environment." Everything written before phase 1 has to be copied across while the system keeps running.

Phase 3 — verify with dark reads

"we added a flag to fetch data from both the old and new databases… comparing these records"

Notion

Read from both, return the old one to the user, and compare in the background. Every real request becomes a test case, running against production data at production scale, with no user-visible risk. This is the phase people skip, and it is the one that converts "we believe the data is correct" into a number.

Phase 4 — flip

Five minutes of scheduled maintenance. And their own assessment afterwards is the most useful sentence in the post: "Had we spent another week optimizing the script… it may have been possible to hot-swap at the load balancer level without downtime." Even the five minutes was optional. They chose a week of their time over a week of engineering.

4. Their regret, and the tension with Lesson 06

Notion's first lesson learned is two words: "Shard earlier." They were forced into custom tooling because by the time they started, the database was already under such load that gentler options were gone.

Hold both of these

Lesson 06 says take the cheapest rung and defer the expensive one. Notion says shard earlier. They are not in conflict, and the resolution is the thing worth carrying:

Defer the migration; do not defer the decision. Figma bought runway deliberately, knowing what would end it and watching for the signal. Notion arrived at the same migration in an emergency, with VACUUM stalling and a hard stop approaching. Same technique, same order — the difference is entirely whether you are choosing your timetable or being handed one. The question is not "should we shard yet?" but "what will force us to, and how far away is it?"

Their second regret is a Lesson 02 callback: using both an id column and space_id was awkward, and "we could've combined both keys into a single new column". The partition key is permanent, so its ergonomics are permanent too.

5. Check yourself

6. Back to your world

You will run this four-phase shape far more often than you will shard a database: changing a column type on a large table, moving a feature's data to a new service, replacing a queue, switching a payment provider. Double write, backfill, verify, flip. The two questions that decide whether it is calm or memorable:

  • Is phase 4 doing anything you have not already rehearsed? If yes, move that work earlier.
  • Did you actually do phase 3? Without dark reads you are not migrating, you are hoping.
Ask me things. "explain VACUUM and freezing properly" · "how do you do this without any downtime at all?" · "what do you do when dark reads disagree?" · "how would I double-write from an app I don't control?" · "I think five minutes of downtime is unacceptable. Grill me."