Changing a running system

Why did renaming one column stop lunch for eleven minutes?

The column was called addr. Nobody remembered whether it meant the kitchen’s address or the customer’s, and the new rider app had already sent three boxes to kitchens instead of homes. The fix seemed obvious: rename it delivery_address, once and properly.

The change was small: one line of SQL and a search-and-replace in the code. At 12:35 one weekday both went out together. The deploy tool ran the migration first, then began replacing the ten app servers, one a minute.

The order page froze at once. For seventy seconds nothing loaded at all. Then pages came back, but most of them were errors, and the errors faded slowly, a tenth at a time, over the next ten minutes.

Riders stood at kitchens with no address on their screens, in the middle of the lunch rush.

The accountant had been running the monthly totals report at the time. The report had not failed; it had only been slow, as it always is. It reads the table, and it has read the table every month for a year. Why did one reader stop everyone else?

The cost of the freeze alone is easy to work out. The order page sends about 40 requests a second, and each gives up after 5 seconds. Every request that arrived in the first 65 of those 70 seconds waited its full 5 seconds and failed, so the freeze cost 40 x 65 = 2,600 failed requests, and it was only the first part of the eleven minutes.

One proposal is blue-green deploys: switch every server to the new code at once, so there is never a moment with two versions running. That does not settle it, because all the servers share one database, and the rename still has to happen while customers are using the table.

Two things went wrong, and neither was the SQL. A rename needs the whole table to itself for a moment, and it waited for that moment at lunch. And for ten minutes, old code and a new schema ran side by side, and they disagreed about a name.

Pause and predict For ten minutes old code and the new schema disagreed about a name. How can a column be renamed with no moment of disagreement?

Two failures in one change

Pause and predict The rename itself takes milliseconds, but it needs the table to itself. The monthly report has been reading the table for a minute. An order query arrives while the rename waits. What happens to it?

Step through what happened at 12:35. The first failure is a lock; the second is a disagreement about the schema between two versions of the code.

Failure one: the lock queue

In PostgreSQL, most forms of ALTER TABLE, including RENAME COLUMN, ADD COLUMN and DROP COLUMN, take an ACCESS EXCLUSIVE lock: the one mode that conflicts with everything, even a plain SELECT. The rename itself takes milliseconds. Getting the lock does not, if anything else is using the table, such as a long report (the locks of lesson 2.3).

The surprise is what happens while it waits. A lock request that is waiting blocks later requests that conflict with it: PostgreSQL calls this a soft block, a process “ahead of it in the wait queue”. So every order query that arrived after the ALTER queued behind it, behind the report. A change that needed a millisecond held the table shut for seventy seconds.

lock_timeout

A limit on how long one statement may wait for a lock before giving up with an error.

For example: SET lock_timeout = '2s'; before a migration: if the ALTER cannot get its lock in 2 seconds it fails, the queue behind it moves on, and the tool tries again later.

Failure two: two versions at once

A rolling deploy (lesson 1.1) replaces servers a few at a time, so for ten minutes some servers ran the old code and some the new. The old code asked for addr; the schema no longer had it. Had the code been deployed first, the new code would have asked for delivery_address before it existed. There is no order of “migrate, then deploy” or “deploy, then migrate” that works for a rename, because each step breaks the version that is still running.

A mental model

Expand and contract

Change a shared interface in steps that are each backward compatible: add the new thing beside the old, move every user over, then remove the old.

For example: Add delivery_address; deploy code that writes both columns; copy old rows; switch reads; stop writing addr; drop addr.

Ways to put new code in front of customers

• Rolling. Replace servers a few at a time behind the load balancer. It needs no extra servers, but old and new code serve traffic side by side for the whole deploy.
• Blue-green. Start a full second fleet with the new code, then switch the load balancer to it at once; switch back to roll back. It needs double capacity for a while, and the database is shared, so the schema must still suit both colours.
• Canary. Send a small share of traffic to the new version and compare its error rate and latency with the rest (the SLIs of lesson 5.7) before going further. Google’s SRE workbook calls it “a partial and time-limited deployment of a change in a service and its evaluation”.
• Feature flags. Ship the code switched off, then turn it on with a setting, for some customers or all. Deploying and releasing become two separate acts, and turning a flag off is faster than any rollback. Flags left in place for ever become a second, untested configuration language, so each one gets an owner and a removal date.

What eleven minutes cost

The safe path takes longer in total and involves two deploys and a backfill, and customers notice none of it. Most of its waits are 2-second pauses that the app absorbs.

How big should a backfill batch be?

Copying 3 million rows with one UPDATE is a single transaction: every row it touches stays locked until it commits, it creates 3 million dead row versions at once for VACUUM (lesson 2.3), and the replica must replay all of it (lesson 3.1). Batches keep each of those small. In the simulator’s model (100,000 rows a second on the primary, 40,000 replayed on the replica, a 100 ms pause between batches):

The fastest backfill puts the replica more than a minute behind, so every read routed there (lesson 3.1) is a minute stale, and locks some orders for 30 seconds. The batch of 1,000 keeps the replica within a few milliseconds and costs only a few extra minutes, which nobody outside the team notices.

A maintenance window, or expand and contract every time?

After the incident there are two defensible rules for next time. One goal is the fewest deploys and nothing half-done to clean up. The other is a change that can go out at any hour without anyone noticing.

A maintenance window is a legitimate choice: many businesses still use one, and for a change that truly cannot be made compatible (a huge rewrite, a new primary key) it may be the only sane path. This service, though, takes orders from 6 a.m., from nurses coming off night shifts, and the second city planned in Part 7 will have its own lunch hour. The default way of changing the schema should not need the service to stop at any hour.

One more rule followed from the incident, and it has nothing to do with databases: a rename is rarely worth it. A clear comment in the schema and in the API documentation would have fixed the rider app’s confusion without any of this.

What to remember

1. A schema change and a code change never take effect at the same instant. Old and new code run together for minutes, so every step must suit both: expand, migrate, contract.
2. Most ALTER TABLE forms need an ACCESS EXCLUSIVE lock. While one waits, every later query on the table waits behind it. Set lock_timeout and retry.
3. Backfill in batches sized for the replica and for row locks, not for speed. Switch reads only after the copy is complete, behind a flag you can turn off.
4. Rolling, blue-green and canary decide how code reaches servers; feature flags decide when customers see it. None of them makes an incompatible schema change safe.

Every schema change must work with the code already running: add first, switch later, remove last, and set a lock timeout.

In the real world

Real migrations add what the simulator leaves out: long-running transactions that hold locks for hours, autovacuum, connection pools that hide which session is waiting, and tools that write each step for you. Several teams have open-sourced tools that apply lock timeouts and retries automatically, and some databases can change schemas online with their own background copy. Each still rests on the same two rules: never hold a lock on a busy table for long, and never break the version of the code that is running.

The rename did happen, three weeks later, on a Tuesday at lunch, in three deploys. Nobody noticed. The next question about the same servers is a different one: what do they cost?