Hacker News Reader

Comment by diggan | Hacker News Reader

> 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.

View on HN

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.

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.

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).

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.

Reply on news.ycombinator.com