3 ms·

Others have described the how part if you do need truly zero downtime deployments, but I think it's worth pointing out that for most organisations, and most mig

Others have described the how part if you do need truly zero downtime deployments, but I think it's worth pointing out that for most organisations, and most migrations, the amount of downtime due to a db migration is virtually indistinguishable from zero, particularly if you have a regional audience, and can aim for "quiet" hours to perform deployments.

diggan 2y ago
> the amount of downtime due to a db migration is virtually indistinguishable from zero Besides, once you've run a service for a while that has acquired enough data for migrations to take a while, you realize that there are in fact two different types of migrations. "Schema migrations" which are generally fast and "Data migrations" that depending on the amount of data can take seconds or days. Or you can do the "data migrations" when needed (on the fly) instead of processing all the data. Can get gnarly quickly though. Splitting those also allows you to reduce maintenance downtime if you don't have zero-downtime deployments already.
- stephenr 2y ago
  Very much so, we handle these very differently for $client. Schema migrations are versioned in git with the app, with up/down (or forward/reverse) migration scripts and are applied automatically during deployment of the associated code change to a given environment. SQL Data migrations are stored in git so we have a record but are never applied automatically, always manually. The other thing we've used along these lines, is having one or more low priority job(s) added to a queue, to apply some kind of change to records. These are essentially still data migrations, but they're written as part of the application code base (as a Job) rather than in SQL.
- sgarland 2y ago
  Schema migrations can be quite lengthy, mostly if you made a mistake earlier. Some things that come to mind are changing a column’s type, or extending VARCHAR length (with caveats; under certain circumstances it’s instant).
  - lukevp 2y ago
    Not OP, but I would consider this a data migration as well. Anything that requires an operation on every row in a table would qualify. Really changing the column type is just a built in form of a data migration.
- globular-toast 2y ago
  Lengthy migrations doesn't matter. What matters is whether they hold long locks or not. Data migrations might take a while but they won't lock anything. Schema migrations, on the other hand, can easily do so, like if you add a new column with a default value. The whole table must be rewritten and it's locked for the entire time.
jakjak123 2y ago
Most are not affected by db migrations in the sense that migrations are run before the service starts the web server during boot. the database might block traffic for other already running connections though,in which case you have a problem with your database design.

diggan 2y ago

> the amount of downtime due to a db migration is virtually indistinguishable from zero Besides, once you've run a service for a while that has acquired enough data for migrations to take a while, you realize that there are in fact two different types of migrations. "Schema migrations" which are generally fast and "Data migrations" that depending on the amount of data can take seconds or days. Or you can do the "data migrations" when needed (on the fly) instead of processing all the data. Can get gnarly quickly though. Splitting those also allows you to reduce maintenance downtime if you don't have zero-downtime deployments already.

stephenr 2y ago
Very much so, we handle these very differently for $client. Schema migrations are versioned in git with the app, with up/down (or forward/reverse) migration scripts and are applied automatically during deployment of the associated code change to a given environment. SQL Data migrations are stored in git so we have a record but are never applied automatically, always manually. The other thing we've used along these lines, is having one or more low priority job(s) added to a queue, to apply some kind of change to records. These are essentially still data migrations, but they're written as part of the application code base (as a Job) rather than in SQL.
sgarland 2y ago
Schema migrations can be quite lengthy, mostly if you made a mistake earlier. Some things that come to mind are changing a column’s type, or extending VARCHAR length (with caveats; under certain circumstances it’s instant).
- lukevp 2y ago
  Not OP, but I would consider this a data migration as well. Anything that requires an operation on every row in a table would qualify. Really changing the column type is just a built in form of a data migration.
globular-toast 2y ago
Lengthy migrations doesn't matter. What matters is whether they hold long locks or not. Data migrations might take a while but they won't lock anything. Schema migrations, on the other hand, can easily do so, like if you add a new column with a default value. The whole table must be rewritten and it's locked for the entire time.

jakjak123 2y ago

Most are not affected by db migrations in the sense that migrations are run before the service starts the web server during boot. the database might block traffic for other already running connections though,in which case you have a problem with your database design.