How to handle database changes in a zero downtime compliant way?

Zero downtime deployment allows some peace of mind.
How we handle database changes is a determining factor in achieving it.
And that’s exactly what we are going to talk about here!

Like many other things, splitting up the change/task into multiple steps helps.
So let’s do this and go baby step by baby step!

Big step versus Baby step by baby step

⚠️ Each step should allow the application to keep running ️⚠️

For instance, it means the current version of the application and the next version of the database schema must remain compatible.

We will only cover changes that I consider common based on my experience. Your mileage may vary 😛
Do not hesitate to share in comments if you have changes not listed here!

At the end, you’ll find a cheat sheet covering the entire article.

Each change is numbered to enable easy reference when needed.

Safe changes. Make the change, no worries.

Safe changes. No worries.

If you need to:

• ① Change a mandatory column to an optional one
• ② Add an optional column to an existing table. Optional means it may be NULL or have a default value.

Then you are SAFE.

Make the change, no worries.

Unsafe changes. Breaking up the task! Here we go!

These kind of changes won’t allow us to keep the application running safely. Let’s see how we may split them in different steps.

③ Delete optional column from existing table

Drop nullable

Instructions/Steps to follow:

1. The app should stop read/write the column
2. Deletion party is on 🎉

④ Change an optional column to a mandatory one

Nullable to not null

Instructions/Steps to follow:

1. Ensure the app should always write to this column
   Update existing data to avoid having null values.
2. Go make the column mandatory 🎉

⑤ Add mandatory column to an existing table

Add null

Instructions/Steps to follow:

1. Add optional column
   Ensure the app should always write to this column
2. Go make the column mandatory 🎉

Or in broad terms if you prefer: ② then/and ④.

⑥ Delete mandatory column from existing table

Drop null

Instructions/Steps to follow:

1. Change mandatory to optional
   The app should stop read/write the column
2. Deletion party is on 🎉

Or in broad terms if you prefer: ① then/and ③.

⑦ Rename optional column

Rename optional column

Instructions/Steps to follow:

1. Add an optional column to the table
   Ensure the app writes to both columns
2. Copy existing data from old to new column
3. Ensure the app reads from the new column
4. Follow the steps of ③ 🎉

⑧ Rename mandatory column

Rename mandatory column

Instructions/Steps to follow:

1. Add an optional column to the table
   Ensure the app should write to both columns
2. Copy existing data from old to new column
3. Ensure the app reads from the new column
4. Follow the steps of ⑥ 🎉

⑨ Rename table

Rename table

Instructions/Steps to follow:

1. Create a new table
   The app should write to both tables
2. Copy existing data from old to new table
3. Ensure the app reads from the new table
4. Drop the old table 🎉

The Cheat sheet

Cheatsheet to handle database changes in a zero-downtine compliant way

Feel free to download this cheatsheet, display it in your office (think about the planet if you do this) to remind you what to do!
You’ll find it here: https://gitlab.com/beram-presentation/handle-database-changes-in-a-zero-downtime-compliant-way

Note

Keep in mind that you may not need this all the time. It depends of the context!

If you:

• don’t mind to lose data
• don’t need to maintain compatibility between the application and the database
• are able to quickly recreate the data from other sources
• etc..

Then, of course, don’t bother; you can go at it like a bull at a gate.