One of my seniors told me that knowing how to solve a problem makes you a great student, but knowing how to solve the same problem at scale makes you a great engineer. I saw a great example of this on an instagram reel recently and I wanted to share it with you.

The question

You have a table with 300 million rows and you need to add a new column. The catch is that the table is being read and written constantly by users. You cannot take the database offline and you cannot afford any downtime. How do you do it?

The naive approach

If this was one of my projects, I’d say to just add the column and fill it in for every row. That works because nobody is watching and nothing breaks if the database freezes. But if you tried this on your company’s live services, your boss would probably scream at you.

The experienced approach

Like any change on a scaled service, this needs to be broken into stages, and each stage should have minimal impact on users. Here’s how the creator approached the problem.

First you add the column as nullable with no default value. In most modern databases this is only a change to the table description, so the database does not rewrite a single row and the whole thing finishes in milliseconds. The column now exists but it is empty.

The instinct is to start filling in the old rows, but that is not the right next step. Users are still writing new rows every second, and those new rows keep arriving with the column empty. If you fix old rows while new empty rows keep coming, you will never catch up. So you fix the new rows first. You update the application so that every new row already includes the new column, and you deploy that.

Only now do you deal with the old data. You run a background job that fills the column for the old rows in small batches of a few thousand at a time while checking the database load. When that’s finished, the database is successfully miragted!

Why this makes me want a j*b

I used one paragraph to explain the naive approach, and four to explain the experienced approach. This just shows us how harder working on systems with real users can be.

This is the part of engineering I cannot really practice alone. My projects have one user, which is me, so I can take my database down whenever I feel like it and nothing bad happens. Problems like this only show up when a system already carries real traffic and real users. That is a big reason I want to work on real systems at real companies, because it is the one place I get to put my hands on systems at this scale and learn how people actually keep them running.