-
Notifications
You must be signed in to change notification settings - Fork 4
Database Migrations
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 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.
This is a based on what we have experienced on notify. It is not a exhaustive guide but will hopefully give people a start on to think about migrations. If you think your migration might be problematic, this document is not a substitute to reading postgres migration documentation and consulting others about it.
When there is migration consider that the old code will be interacting with the new migration for a time period before the workers are the rolled. So it needs to be backwards compatible. After merging PR into master keep an eye on the pipelines.
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.
Another was adding a constraint to the notifications table without a not valid option which caused a long table scan.
It is hard to specify what makes migration dangerous, performance on staging might not be enough. It is advisable to have a broad kick off (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.
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.