We have two migration directories: db/migrate and db/post_migrate. If a
migration adds something (e.g. a table or a column) or migrates data that
existing code can deal with (e.g. populating a new table) then it goes in
db/migrate. If a migration removes or updates something that first requires a
code deploy, it goes in db/post_migrate. We combine this with a variety of
helpers in various places to allow for zero downtime upgrades (if you're using
PostgreSQL). For example, to remove a column we take these steps:
1. Deploy a new version of the code that ignores the column we will drop (this
is a matter of adding `ignore_column :column_name` in the right class).
2. Run a post-deployment migration that removes said column.
3. In the next release we can remove the `ignore_column` line (we require that
users upgrade at most one minor version for online upgrades).
The migrations in db/post_migrate are executed by default but you can opt-out by
setting an environment variable. In case of GitLab.com this results in the
following deployment procedure:
1. Deploy code with this variable set (so we don't run the post-deployment
migrations)
2. Re-run `rake db:migrate` on a particular host without setting this
environment variable.
For big data migrations we use Sidekiq to run them in the background. This
removes the need for a deployment procedure taking hours, though it comes with
some additional complexity.
More information about this can be found at the following places:
Its worth noting that there are some edge cases where this could lead to potential problems. Its enough of an edge case for many apps to ignore the possibility. Take your example with the addition of the fact that the field you're removing has a unique database constraint. With your deploy process this would look something like this.
1. Run predeploy migration in db/migrate to remove the unique constraint
2. Deploy new version of code that ignores the column
3. Run post-deploy migration that removes column
4. Remove the part of code that ignores the column
Failure to do step 1 would result in multiple rows being created with null values, which would cause errors for all but one (or zero) insert. The edge case, however, is a little more subtle. Between steps 1 and 2, there's a period where the database constraint doesn't exist, but the old code still expects the constraint. Validating uniqueness in the code can mitigate this, but doesn't ensure consistency.
If you absolutely have to be in a spot to support this a trigger-based approach is by far the best way to handle it, either by pervasive use of database views (and have INSERT triggers to handle writes, that way you can version the view and handle any extra validation logic during migration with a temporary performance hit) or having a trigger on the old table feeding the new schema and then swap the tables upon upgrade of the software.
Both solutions suck in some way compared to the downtime required schema changes as far as simplicity is concerned, but they let you maintain consistency throughout the change at least and let you bail out midway if needed
On that note, I'm glad all but a single system I maintain is only used by employees - just schedule a maintenance window after-hours and I'm free to take things down for a couple hours if needed.
This is not how we do it. Constraints, columns, etc are removed using a post-deployment migration. This means you can't end up with NULL values and the likes.
This helps ensure database consistency, but might not actually avoid errors. In this scenario, the period of time between the app code being deployed and the post-deploy migration running there could potentially be application level errors because the app is now ignoring the deleted field and the database expects unique values (or non-null, or whatever constraints you have).
You can solve these issues with some creative migration paths both at the app level and at the database level, but those changes aren't always trivial, and may not necessarily fit in the pre-deploy post-deploy migration strategy.
This may also not be an issue for you. You may be willing to accept a brief period of time where an error is unlikely, but technically possible. Personally, most of the time this is acceptable to me, but I always find it important to think about these scenarios in case something does happen.
Comments
At GitLab we do the following:
We have two migration directories: db/migrate and db/post_migrate. If a migration adds something (e.g. a table or a column) or migrates data that existing code can deal with (e.g. populating a new table) then it goes in db/migrate. If a migration removes or updates something that first requires a code deploy, it goes in db/post_migrate. We combine this with a variety of helpers in various places to allow for zero downtime upgrades (if you're using PostgreSQL). For example, to remove a column we take these steps:
1. Deploy a new version of the code that ignores the column we will drop (this is a matter of adding `ignore_column :column_name` in the right class).
2. Run a post-deployment migration that removes said column.
3. In the next release we can remove the `ignore_column` line (we require that users upgrade at most one minor version for online upgrades).
The migrations in db/post_migrate are executed by default but you can opt-out by setting an environment variable. In case of GitLab.com this results in the following deployment procedure:
1. Deploy code with this variable set (so we don't run the post-deployment migrations)
2. Re-run `rake db:migrate` on a particular host without setting this environment variable.
For big data migrations we use Sidekiq to run them in the background. This removes the need for a deployment procedure taking hours, though it comes with some additional complexity.
More information about this can be found at the following places:
1. https://docs.gitlab.com/ee/update/README.html#upgrading-with...
2. https://docs.gitlab.com/ee/development/what_requires_downtim...
3. https://docs.gitlab.com/ee/development/background_migrations...
Its worth noting that there are some edge cases where this could lead to potential problems. Its enough of an edge case for many apps to ignore the possibility. Take your example with the addition of the fact that the field you're removing has a unique database constraint. With your deploy process this would look something like this.
1. Run predeploy migration in db/migrate to remove the unique constraint 2. Deploy new version of code that ignores the column 3. Run post-deploy migration that removes column 4. Remove the part of code that ignores the column
Failure to do step 1 would result in multiple rows being created with null values, which would cause errors for all but one (or zero) insert. The edge case, however, is a little more subtle. Between steps 1 and 2, there's a period where the database constraint doesn't exist, but the old code still expects the constraint. Validating uniqueness in the code can mitigate this, but doesn't ensure consistency.
If you absolutely have to be in a spot to support this a trigger-based approach is by far the best way to handle it, either by pervasive use of database views (and have INSERT triggers to handle writes, that way you can version the view and handle any extra validation logic during migration with a temporary performance hit) or having a trigger on the old table feeding the new schema and then swap the tables upon upgrade of the software.
Both solutions suck in some way compared to the downtime required schema changes as far as simplicity is concerned, but they let you maintain consistency throughout the change at least and let you bail out midway if needed
On that note, I'm glad all but a single system I maintain is only used by employees - just schedule a maintenance window after-hours and I'm free to take things down for a couple hours if needed.
This is not how we do it. Constraints, columns, etc are removed using a post-deployment migration. This means you can't end up with NULL values and the likes.
This helps ensure database consistency, but might not actually avoid errors. In this scenario, the period of time between the app code being deployed and the post-deploy migration running there could potentially be application level errors because the app is now ignoring the deleted field and the database expects unique values (or non-null, or whatever constraints you have).
You can solve these issues with some creative migration paths both at the app level and at the database level, but those changes aren't always trivial, and may not necessarily fit in the pre-deploy post-deploy migration strategy.
This may also not be an issue for you. You may be willing to accept a brief period of time where an error is unlikely, but technically possible. Personally, most of the time this is acceptable to me, but I always find it important to think about these scenarios in case something does happen.