An evening journey of renaming a database column

A few nights ago I stumbled upon this thread on reddit, discussing database migrations.Part of the discussion focused around a zero-downtime strategy.

The strategy of always adding and never changing an existing column came up. I later ended up reading this blog and watched this talk.

After this I really just wanted to do this myself — so a few hours later and I had a toy example.

I’ll write about the example I’ve created and won’t focus too much on the nitty gritty details in each blog/video. Follow the links if you want more :)

Before we start

Pre-requisites for following along the example are:

• Docker and docker-compose
• Postman (or similar)
• Postgres compatible client (if you want to inspect the database)
• Flyway
• Go
• Download the repo

The example simulates rolling updates by simply running two processes along side each other. If you follow along you will start and stop the processes manually — this way we can play around in different states of the migration. There are three versions of the app: v1, v2, v3. They are respectively run on ports: 5001, 5002, 5003. In reality, the different versions of the application would be rolled-out by some orchestration system and clients would be making requests to an ingress of some sort.

Here’s a really janky, but hopefully useful, video going through the steps :).

Alright, lets get into it :)

Step 0

The first step is to have a look at what we have to work with.

The application is about keep track of beers and consists of a two-endpoint API, one endpoint for listing all beers and one for creating new beers.

To list all beers make a GET /beer request and to create a beer use the endpoint POST /beer with a json body like this : {title: “Some title” }.

Navigate to the example projects root directory with a terminal of choice and run docker-compose up -d to start the Postgres database.
Now run flyway migrate -target=0.

A database should be up and running with our initial table structure, but without any data…

Start the first version of the app by running go run v1/main.go.
Use Postman to create a beer called From app.v1 step 0.

We now have a starting point for our migration adventure.

Step 1

Next step is to run our first real migration, run flyway migrate -target=1.

The column name now exists in our beer table. Run the second version of our app along side our first version. Run go run v2/main.go.

Unlike the first version of our app, this second version knows about our new column name. This version will duplicate its writes, so whatever value goes into title also ends up in the name column. When reading, the app will try to use the name column but fall back to title if the value is null. This will be the case for data that was created before app.v2 existence and will still happen as long as app.v1 is accepting requests. Lets created two new beers.

One via app.v1, call it From app.v1 step 1 and one via app.v2, call it From app.v2 step 1. The image below should represent our tables current state.

Let’s take down app.v1, aka simulating that the rolling update completely switched over to app.v2 and no instances of app.v1 is running.

Step 2

From now on we have an application reading from both columns and writing to both columns, great!. But now we need to transform our “pre app.v2 era” data rows so that name no longer is null. For this we can run a one time script/app, where we at our own pace can copy missing name data from the title column.

Run go run data-migration/main.go. This should give us the following.

We can now run the second migration step. Run flyway migrate -target=2

This will remove the not null constraint on our title column. This is in order for our next deployment to work. The migration also adds a CHECK constraint for the name, essentially acting as a regular not null constraint. You can read more about it here.

This allows us to add a not null constraint to a table with a large dataset without locking issues, which can result in your service being unresponsive. The NOT VALID-part lets us specify that only new data will be checked and any pre-existing data for this column will not have to obey this constraint… yet.

Go ahead and deploy the last and final version of the app.
Run go v3/main.go.

Now we should have both our v2 and our v3 running along side each other.

Create two beers, one called From app.v2 step 2 via app.v2 and one called From app.v3 step2 via app.v3.

App v3 is completely unaware of the title column and the value will default to being null now since we removed the not null constraint.

Lastly, take down app.v2.

Step 3

This is the last step. Run the migration flyway migrate -target=3. This step has two parts, one is to drop the column title which should only be done when you are completely sure you never want to use the title column again.

The second part of this migration is to switch the CHECK constraint for name to VALID. We now have a name column with a complete not null constraint.

Journeys end

There are several variations of this process to achieve the same result, some more use-case dependent than others. It comes down to details like the size of your dataset and so on, but I hope someone finds this little toy-example useful and interesting.

/ Robert