Schema Changes: Two Stages, Not a Friday Night

From the field: 80,000 rows versus 47 million

The engineer who wrote that migration did everything right. The change was reviewed, it ran in the test environment, and the duration was measured: 4.2 seconds. Friday night was chosen so traffic would be low. Everything the book says had been done.

The one thing we missed: the test database was not a smaller copy of production. It was a different database. Rewriting a table of 80,000 rows takes seconds. Rewriting 47 million takes minutes. The number we measured was correct, but the thing we measured was wrong.

The second mistake was mine. When I said “let us do it Friday night” I felt safer, but that sentence does not reduce risk. It reduces witnesses. A change made at night is one that fewer people watch and that gets fixed by a more tired brain. If you want less risk, make the operation smaller, not later.

Why one shot does not work

The reason is not in the database. It is in the deploy. When a release goes out, the old pods do not disappear at once; the new version comes up while the old one is still serving requests. That window can be a few seconds, or hours with a gradual rollout (or days, in the setup I described in the release management post).

So right now two versions of your code are writing to the same table. If you change the schema in one shot to match the new version, the old version starts failing immediately. That gives you the rule: every schema change has to work with both the old and the new code.

Expand / contract: five steps

A request as simple as “let us split the phone number into country code and number” looks like this in the field:

1) EXPAND     : add the new column, allow NULL
                ALTER TABLE customer ADD COLUMN country_code varchar(5) NULL;
                code unchanged. rollback: leave the column, harmless.

2) WRITE BOTH : code writes to BOTH columns on every new record
                reads still come from the old column. ships alone.
                rollback: go back a version, new column stays empty.

3) BACKFILL   : fill old rows in batches (below)
                code unchanged. stop and resume whenever you like.

4) SWITCH READS: code starts reading the new column
                it still writes to both. this is the critical release:
                rollback is one step, because the old column is current.

5) CONTRACT   : first remove the double write (one release),
                then drop the old column (a separate release).
                ALTER TABLE customer DROP COLUMN phone_old;
                this step is NOT REVERSIBLE. wait at least a week.

Five steps sounds like a lot, but each one is an ordinary release, and the total work is not much more than the single-shot migration. The difference: no step takes a lock, and no step has a point of no return. Up to step 4, everything is undone with one command.

Do not miss why step 4 is the critical one: when you switch reads, you are still writing to both. So if something goes wrong, switching reads back is enough — the old column stayed current, so no data is lost. Teams that remove the double write in the same release as the read switch are walking through a door that does not open again.

Backfill: not one UPDATE

The classic mistake in step 3 is this single line:

# DO NOT - locks 47 million rows in one transaction
UPDATE customer SET country_code = substring(phone_old, 1, 3);

# DO THIS - batched, resumable, observable
last_id = 0
while True:
    batch = "UPDATE customer SET country_code = substring(phone_old,1,3)
             WHERE id > {last_id} AND country_code IS NULL
             ORDER BY id LIMIT 5000
             RETURNING id"
    rows = run(batch)
    if not rows: break
    last_id = max(rows)            # RECORD where you stopped
    sleep(200)                     # let the replicas breathe
    if replica_lag() > 5:          # seconds
        sleep(5000)                # slow down, do not stop

Three details matter: record where you stopped (a backfill will be interrupted, and it has to continue), watch replica lag (the most common side effect of a backfill is read replicas falling behind, which reaches the user as “my record is not there”), and pause between batches (writing without a break starves every other query).

Which operations are safe

The real message of the table: most one-line commands are not one step. RENAME is one word but two changes. NOT NULL is two words but a scan of 47 million rows.

When do we drop the old column?

The honest answer in most teams is “never”. Step five never arrives, because it is never urgent. The same happened to us: a year later, three tables still had columns with “old” in the name, two of them were still being written to, and nobody knew why.

The fix was the same rule that worked for feature flags: make the decision when you start the work. We added one line to the top of the migration file, and CI watches that date:

# migration : 2026_04_12_add_country_code
# expand-contract : YES
# old column      : customer.phone_old
# drop by         : 2026-05-20      <-- CI warns after this date
# owner           : one name

Once the date passes, CI warns. Two weeks later it breaks the build. That is harsh, and it is the only version that works: during the period when we only warned, not a single column was dropped.

What to track

What did not work for me

• Growing the test database. We said “let us load production-size data”. Three weeks later nobody was keeping that environment up to date. What worked instead: run the migration against a restored production backup and measure the duration there.
• Not separating the migration from the application deploy. While they ran in the same pipeline, a slow migration also blocked the deploy, and rollbacks got complicated. We split them: the schema change ships on its own, the application separately.
• Declaring a maintenance window. That was my first reflex. Scheduling half an hour of downtime every month was the price we paid for not learning expand/contract — and the window was always full, and never long enough.

Checklist

Conclusion

We lost 40 minutes that Friday night, and the following week we made the same change again — this time in five steps, during the day, with nobody sitting up. The total time was longer: four days. The downtime was zero.

What makes schema changes hard is not the database. It is that old and new code have to live together for a while. Once you accept that, the answer follows on its own: split the change into small pieces, each of which is correct on its own.

The test is this: could I run this migration at midday on a Tuesday, at peak traffic? If the answer is no, doing it at night does not make it safe. It just moves it to an hour when nobody is watching.