Database Migrations

Introduction

We use alembic to do migrations. The migrations live in notifications-api.

They are run in a separate migration ecs task (application_name migration in logit).

Migrations can and have caused issues. We run all the statements inside a transaction.

We haven't yet rolled back a migration and don't have the machinery to do this in concourse. It is still useful for local development though.

Squawk

Squawk is a linter for migrations. It should be run locally but it won't block deployment or block committing as it lints destructive events. It will help with basic linting and should flag up things like deletions to make sure you are sure.

What makes a bad migrations

In general it is database operations that take locks. In modern times these tend to be operations that delete columns (it used to be the case that adding columns with default data took exclusive locks too). Making the migration as small as possible reduces the risks.

One example of a migration that caused problems was this one that deleted two columns off the notifications database as well as deleting tables.

It is hard to specify what makes migration dangerous, performance on staging might not be enough. It is advisable to have a broad kick (involving people with lots of experience of postgres) if a migration is involved. This is especially true if you are touching a large table like notifications.

What makes a good postgres migration

Doing operations by hand against production when doing things that are risky. For example this migration was not run against prod as the creation of the indexes could not be done inside the transaction in a concurrent fashion so needed to be done in another way. This is more important against tables with large number of rows.