Slides That Follow Your Voice: African American Accent Tech Tutorial

Slide 1: Rename a column without taking the site down

0:17Hey, welcome back. Today we're doing the thing everybody asks me about in the comments, which is how to change a database column in production without taking the site down. Renaming a column. That's it, that's the whole video, and it's going to take twenty-five minutes, and by the end you'll see why. [chuckles]

0:37Okay so, here's the setup. We have a table called orders. It has forty-one million rows. It has a column called ship date, which somebody named in twenty seventeen when the company shipped things, and the company now delivers things, and the uh, the product team wants it called delivery date, and they want it by Friday.

Slide 2: The setup: 41 million rows

0:59The naive way is one line of sequel. Rename column. On a table this size, on this database, that takes a lock for about sixty, no, ninety seconds, and for ninety seconds every checkout on the site fails.

1:15We did that once. In twenty twenty-one. The postmortem was fourteen pages. We're not doing that. [laughs] I wrote the postmortem. I was also the person who ran the rename, so I was writing about myself in the third person for fourteen pages, which is a very specific kind of humbling. My manager still has it. He brings it up at my reviews, kindly. Right. The pattern.

Slide 3: Four steps, in this order

1:44So here's the pattern, and it has four steps, and the steps have to happen in this order, and each one has to be fully done before the next one starts. Expand, backfill, switch, contract. Write that down. Put it on a sticky note. I have it, I was going to say tattooed, I don't, but it's on the wall.

2:07Step one, expand. We add the new column, delivery date, nullable, no default. Adding a nullable column with no default is a metadata change, it doesn't rewrite the table, and it takes about four milliseconds. Let me run it.

Slide 4: Step one: expand

2:25There. Done. Nobody noticed. The old column is still there, the application is still writing to it, and we've changed nothing the users can see.

2:37Step two, backfill. We copy the old column into the new one, and we do it in batches, because if you try to update forty-one million rows in one query you're back to the ninety-second lock. I'm doing batches of five thousand, with a fifty-millisecond sleep between them, ordered by uh, by primary key. That's eight thousand two hundred batches.

Slide 5: Step two: backfill in batches

3:02At this rate the backfill takes about eleven minutes, and the database barely notices. I'm going to start it and let it run while we talk. Somebody in chat is asking why not just do it at three in the morning. Right. Because there's no three in the morning any more, we have customers in every time zone, and also because I did do it at three in the morning once, see postmortem. Also that's my cat. She likes the server rack. It's warm. Okay.

3:32Step three, the switch, step three, okay. Step three, switch, and this is the part people get wrong. You don't switch reads and writes at the same time. First you deploy application code that writes to both columns. Every new order writes ship date and delivery date. The backfill is still running underneath.

Slide 6: Step three: switch

3:55Once the backfill finishes, and you've verified it, and I mean verified, I mean you run a query that counts the rows where the two columns differ and the answer is zero, then, and only then, you deploy code that reads from the new column.

4:13The old column is now a passenger. Nothing reads it. Everything still writes it, because disk is cheap and rollbacks are not, and rollbacks are not.

4:25Step four, contract. You wait. I mean it. You wait a week, maybe two, with the old column sitting there, written and never read, until you're sure no report, no nightly job, no one in finance with a spreadsheet, is still looking at ship date.

Slide 7: Step four: contract

4:45Then you stop writing to it. Then you wait again. Then you drop it. That drop's a metadata change too, four milliseconds again, and again nobody notices, which is the whole point of this work.

5:02Backfill's at sixty-one percent. Let me check the difference count so far on the rows it's done. Zero. Good. [sighs]

5:12So: expand, backfill, switch, contract. Four steps, two weeks, zero downtime, and one sticky note. I was going to say what next week is, and, it's a column type change, and it's worse, and the sticky note is bigger. That's all. Thanks for watching.