6 ms·

Zero downtime migrations

187 points by KiranRao0 4y ago

antoncohen 4y ago
MySQL has some robust tooling in this space. Some of the tools use triggers to copy to a new table. GitHub's gh-ost[1] is probably the state of the art, and uses the binary log stream to replicate the data. [1] https://github.com/github/gh-ost https://github.com/github/gh-ost
- taspeotis 4y ago
  Postgres has some less robust tooling, at least both of these carry the "experimental" tag: https://news.ycombinator.com/item?id=29825520 https://news.ycombinator.com/item?id=29825520 https://github.com/shayonj/pg-osc https://github.com/shayonj/pg-osc But, still, they do it for you.
fabianlindfors 4y ago
Anybody interested in this subject might also be interested in a tool for Postgres I’ve been working on, Reshape: https://github.com/fabianlindfors/reshape https://github.com/fabianlindfors/reshape. It aims to fully automate away all the pain and manual steps zero-downtime migrations normally requires :)
darkwater 4y ago
If you are using MySQL I also recommend Percona's Online Schema Change [1] which is basically a robust implementation of this post's algorithm, handles more edge-cases, has been existing for at least 10 years now and works better out of the box with AWS RDS than Gh-ost. (if you have production experience on 100s of millions rows tables with Gh-ost on RDS please share your experience/tricks, I'd love to hear them!) [1] https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html https://www.percona.com/doc/percona-toolkit/3.0/pt-online-sc...
- boudin 4y ago
  I have been using gh-ost with RDS for years no with no issues. What problems did you have?
  - darkwater 4y ago
    Actually I never tried but I was scared by the small print of GH not using RDS themselves [1] and Ghost relying on lower-level features that might be not easily available in RDS. Also I had the impression you have to setup a normal non-RDS replica attached to your RDS master? [1] https://github.com/github/gh-ost/blob/master/doc/rds.md https://github.com/github/gh-ost/blob/master/doc/rds.md
    - WJW 4y ago
      At my previous employer we've run it just fine from an EC2 instance talking to a MySQL RDS database. At some point the triggers from pt-osc were really hurting when trying to migrate big (several billion rows) table with heavy traffic and gh-ost really helped with that. That said, gh-ost is definitely a big boy tool and it took us a few tries to get the settings just right. No downtime or anything, we just had to delete the new table and start over. Still annoying when it wastes several hours :)
    - boudin 4y ago
      It works with a standard RDS (not aurora) replica. There are a few constraints when using gh-ost but it's all documented.
      - earless1 4y ago
        It also works fine with Aurora, as long as you have binary logging enabled.
        evanelias 4y ago
        That's true. However, if you don't already have any "traditional" (binlog-based) replicas on Aurora, you might not even need gh-ost or pt-osc in the first place -- at least for many common ALTER operations. Aurora supports physical replication at the storage level, which means you can use MySQL online DDL (ALGORITHM=INPLACE, LOCK=NONE) without having to worry about replication lag. Recent blog post with findings from Percona: https://www.percona.com/blog/zero-impact-on-index-creation-with-amazon-aurora-3/ https://www.percona.com/blog/zero-impact-on-index-creation-w... Caveats: * This is safe on Aurora 3 due to MySQL 8's atomic DDL support. On previous versions, there may be crash-safety risks, which make external tools safer for altering very large tables. * External OSC tools can provide other advantages, such as throttling, safer to cancel, ability to time the final table switchover, ability to rollback (pt-osc supports "reverse" triggers), etc. * Some forms of ALTER TABLE don't support online DDL, and for those you'd still need an external OSC tool like gh-ost or pt-osc, but it's less common.
stephenanand 4y ago
[flagged]
stalluri 4y ago
I think PlanetScale folks have blogged well about how schema migration tools work and their traffic splitting, rewinding abilities are very nice. https://docs.planetscale.com/learn/how-online-schema-change-tools-work https://docs.planetscale.com/learn/how-online-schema-change-... https://planetscale.com/blog/its-fine-rewind-revert-a-migration-without-losing-data https://planetscale.com/blog/its-fine-rewind-revert-a-migrat...
ewuhic 4y ago
Side Question - could anyone suggest a tool for db cloning in context of on-commit preview environments? I am only aware of this tool: https://github.com/postgres-ai/database-lab-engine https://github.com/postgres-ai/database-lab-engine But it looks like too much manual work to do.
tzahifadida 4y ago
I thought of another trick. Haven"t tried it, should work though. Where applicable put tenants on multi schema. If the tables are now very small in comparison then a write lock is not that important. Simply migrate. Do a blue green deployment between code versions. Done. One downside is the downgrade part. If you still want it you have to do it as in the article.
yakkomajuri 4y ago
> Now the most challenging part: explaining to Product why their seemingly small request took 3x longer than expected. If it helps you can send them this article. Good luck! We had to solve this problem in a way that also took our self-hosted users into account. Essentially "changing Tires at 100mph" in environments we don't control. Still polishing it but will plug a post about it here if anyone finds it relevant: https://posthog.com/blog/async-migrations https://posthog.com/blog/async-migrations
harryvederci 4y ago
Something I'm doing in my CV application is using 1 SQLite DB file per user. When a user logs in, a check is done: "Does this user's DB file have the latest migration?" If not, the migrations are applied. That way, you only get a slight delay as a user when a migration is needed. None of the other users their DB files are affected. More technical details are in the FAQ: https://withoutdistractions.com/cv/faq https://withoutdistractions.com/cv/faq In terms of the article: I'm only changing 1 tire at a time at 100mph, not all of them. PS: I recently did a "Show HN" about the app, it got some interesting feedback: https://news.ycombinator.com/item?id=31246696 https://news.ycombinator.com/item?id=31246696
- ramraj07 4y ago
  Wait , are you running this site on a raspberry pi? The biggest issue with SQLite is what happens if your server serving stuff goes down mid write? Power surge maybe. You lose data from that day till previous cronjob?
  - lights0123 4y ago
    > The biggest issue with SQLite is what happens if your server serving stuff goes down mid write? Why would that matter? SQLite is still fully ACID compliant like other DBs.
    - WJW 4y ago
      I imagine that GP means a hardware failure severe enough that it permanently borks the disk drive of the machine. In that case vanilla SQLite would indeed lose data written since the time of the last offsite backup. MySQL/Postgres/etc typically have streaming replication which means you can promote one of the replicas and not lose any (or at least much less) data. There are some new-ish projects like Litestream (https://litestream.io/ https://litestream.io/) that provide a similar functionality for SQLite by streaming the WAL file changes to S3.
  - harryvederci 4y ago
    The "boots up our application within a second on a Raspberry Pi" comment in the FAQ[0] is actually about my development setup, as I don't own a fancy computer myself. It was mainly meant to illustrate the fast startup time of the Janet[1] language. In fact, that startup time is so fast, you can run your app as a CGI script. That has some more possible advantages, see the FAQ for more details. [0] FAQ: https://withoutdistractions.com/cv/faq#technology https://withoutdistractions.com/cv/faq#technology [1] Janet: https://janet-lang.org/ https://janet-lang.org/
jfk13 4y ago
Ah, it's about databases... the title here immediately made me think of a much more hardcore "migration" effort: https://www.amusingplanet.com/2019/10/an-incredible-move-indiana-bell.html https://www.amusingplanet.com/2019/10/an-incredible-move-ind...
- dboreham 4y ago
  Keeps happening: something that had a name (schema evolution) gets given another name by people who never heard the first name. Recurse.
jbverschoor 4y ago
I dunno.. people seem to like 0 downtime migrations, but really, for most companies a little bit of downtime or degraded functionality doesn't matter. Most applications aren't even large enough for the downtime to be more than a couple of minutes. Having some scheduled downtime saves you a lot of complexity of writing, monitoring, and finalizing these migrations. It also makes it a lot easier to have a consistent state in terms of code+data. The article doesn't mention how to deal with different datamodels / constraints etc.
- XorNot 4y ago
  It's simpler then that: "zero downtime" isn't free, and has a direct cost to implement - one you potentially pay every single migration, for which you do not necessarily make enough or any money back on. In particular most SaaS providers with a subscription model would be hard-pressed to care: they're not selling ads per click, so provided you don't lose users over it, there's zero value. In fact it's probably more valuable to take the downtime and use the savings in dev time and effort to ensure you have an expedient rollback and recovery strategy - that will cost you users.
  - KiranRao0 4y ago
    > "zero downtime" isn't free, and has a direct cost to implement Thank you for writing this. I was implicitly trying to convey this in the article, but glad to have it be explicit.
  - WJW 4y ago
    At least for Rails there are several gems available (ie https://github.com/WeTransfer/ghost_adapter https://github.com/WeTransfer/ghost_adapter or https://github.com/departurerb/departure https://github.com/departurerb/departure) that seamlessly hook into the existing migration system and will run all eligible migrations through gh-ost or pt-osc as needed. You're right that it's not free but it isn't all that far off either. That said, online schema migrations are a specialized tool designed for very big tables that take hours to run an ALTER TABLE on. If all your tables are small enough that alterations take less than a second or so, don't bother and just block the table for a bit. It's fine.
kasey_junk 4y ago
When I was first starting out writing applications it was very common for the DBA in charge of a database to require all writes to go to a stored procedure and all reads to go through views. It was at times onerous but it made migrations like these a snap. With most migrations/rdms it could even be atomic.

187 points by KiranRao0 4y ago

MySQL has some robust tooling in this space. Some of the tools use triggers to copy to a new table. GitHub's gh-ost[1] is probably the state of the art, and uses the binary log stream to replicate the data. [1] https://github.com/github/gh-ost https://github.com/github/gh-ost

taspeotis 4y ago
Postgres has some less robust tooling, at least both of these carry the "experimental" tag: https://news.ycombinator.com/item?id=29825520 https://news.ycombinator.com/item?id=29825520 https://github.com/shayonj/pg-osc https://github.com/shayonj/pg-osc But, still, they do it for you.

fabianlindfors 4y ago

Anybody interested in this subject might also be interested in a tool for Postgres I’ve been working on, Reshape: https://github.com/fabianlindfors/reshape https://github.com/fabianlindfors/reshape. It aims to fully automate away all the pain and manual steps zero-downtime migrations normally requires :)

darkwater 4y ago

If you are using MySQL I also recommend Percona's Online Schema Change [1] which is basically a robust implementation of this post's algorithm, handles more edge-cases, has been existing for at least 10 years now and works better out of the box with AWS RDS than Gh-ost. (if you have production experience on 100s of millions rows tables with Gh-ost on RDS please share your experience/tricks, I'd love to hear them!) [1] https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html https://www.percona.com/doc/percona-toolkit/3.0/pt-online-sc...

boudin 4y ago
I have been using gh-ost with RDS for years no with no issues. What problems did you have?
- darkwater 4y ago
  Actually I never tried but I was scared by the small print of GH not using RDS themselves [1] and Ghost relying on lower-level features that might be not easily available in RDS. Also I had the impression you have to setup a normal non-RDS replica attached to your RDS master? [1] https://github.com/github/gh-ost/blob/master/doc/rds.md https://github.com/github/gh-ost/blob/master/doc/rds.md
  - WJW 4y ago
    At my previous employer we've run it just fine from an EC2 instance talking to a MySQL RDS database. At some point the triggers from pt-osc were really hurting when trying to migrate big (several billion rows) table with heavy traffic and gh-ost really helped with that. That said, gh-ost is definitely a big boy tool and it took us a few tries to get the settings just right. No downtime or anything, we just had to delete the new table and start over. Still annoying when it wastes several hours :)
  - boudin 4y ago
    It works with a standard RDS (not aurora) replica. There are a few constraints when using gh-ost but it's all documented.
    - earless1 4y ago
      It also works fine with Aurora, as long as you have binary logging enabled.
      - evanelias 4y ago
        That's true. However, if you don't already have any "traditional" (binlog-based) replicas on Aurora, you might not even need gh-ost or pt-osc in the first place -- at least for many common ALTER operations. Aurora supports physical replication at the storage level, which means you can use MySQL online DDL (ALGORITHM=INPLACE, LOCK=NONE) without having to worry about replication lag. Recent blog post with findings from Percona: https://www.percona.com/blog/zero-impact-on-index-creation-with-amazon-aurora-3/ https://www.percona.com/blog/zero-impact-on-index-creation-w... Caveats: * This is safe on Aurora 3 due to MySQL 8's atomic DDL support. On previous versions, there may be crash-safety risks, which make external tools safer for altering very large tables. * External OSC tools can provide other advantages, such as throttling, safer to cancel, ability to time the final table switchover, ability to rollback (pt-osc supports "reverse" triggers), etc. * Some forms of ALTER TABLE don't support online DDL, and for those you'd still need an external OSC tool like gh-ost or pt-osc, but it's less common.

stephenanand 4y ago

[flagged]

stalluri 4y ago

I think PlanetScale folks have blogged well about how schema migration tools work and their traffic splitting, rewinding abilities are very nice. https://docs.planetscale.com/learn/how-online-schema-change-tools-work https://docs.planetscale.com/learn/how-online-schema-change-... https://planetscale.com/blog/its-fine-rewind-revert-a-migration-without-losing-data https://planetscale.com/blog/its-fine-rewind-revert-a-migrat...

ewuhic 4y ago

Side Question - could anyone suggest a tool for db cloning in context of on-commit preview environments? I am only aware of this tool: https://github.com/postgres-ai/database-lab-engine https://github.com/postgres-ai/database-lab-engine But it looks like too much manual work to do.

tzahifadida 4y ago

I thought of another trick. Haven"t tried it, should work though. Where applicable put tenants on multi schema. If the tables are now very small in comparison then a write lock is not that important. Simply migrate. Do a blue green deployment between code versions. Done. One downside is the downgrade part. If you still want it you have to do it as in the article.

yakkomajuri 4y ago

> Now the most challenging part: explaining to Product why their seemingly small request took 3x longer than expected. If it helps you can send them this article. Good luck! We had to solve this problem in a way that also took our self-hosted users into account. Essentially "changing Tires at 100mph" in environments we don't control. Still polishing it but will plug a post about it here if anyone finds it relevant: https://posthog.com/blog/async-migrations https://posthog.com/blog/async-migrations

harryvederci 4y ago

Something I'm doing in my CV application is using 1 SQLite DB file per user. When a user logs in, a check is done: "Does this user's DB file have the latest migration?" If not, the migrations are applied. That way, you only get a slight delay as a user when a migration is needed. None of the other users their DB files are affected. More technical details are in the FAQ: https://withoutdistractions.com/cv/faq https://withoutdistractions.com/cv/faq In terms of the article: I'm only changing 1 tire at a time at 100mph, not all of them. PS: I recently did a "Show HN" about the app, it got some interesting feedback: https://news.ycombinator.com/item?id=31246696 https://news.ycombinator.com/item?id=31246696

ramraj07 4y ago
Wait , are you running this site on a raspberry pi? The biggest issue with SQLite is what happens if your server serving stuff goes down mid write? Power surge maybe. You lose data from that day till previous cronjob?
- lights0123 4y ago
  > The biggest issue with SQLite is what happens if your server serving stuff goes down mid write? Why would that matter? SQLite is still fully ACID compliant like other DBs.
  - WJW 4y ago
    I imagine that GP means a hardware failure severe enough that it permanently borks the disk drive of the machine. In that case vanilla SQLite would indeed lose data written since the time of the last offsite backup. MySQL/Postgres/etc typically have streaming replication which means you can promote one of the replicas and not lose any (or at least much less) data. There are some new-ish projects like Litestream (https://litestream.io/ https://litestream.io/) that provide a similar functionality for SQLite by streaming the WAL file changes to S3.
- harryvederci 4y ago
  The "boots up our application within a second on a Raspberry Pi" comment in the FAQ[0] is actually about my development setup, as I don't own a fancy computer myself. It was mainly meant to illustrate the fast startup time of the Janet[1] language. In fact, that startup time is so fast, you can run your app as a CGI script. That has some more possible advantages, see the FAQ for more details. [0] FAQ: https://withoutdistractions.com/cv/faq#technology https://withoutdistractions.com/cv/faq#technology [1] Janet: https://janet-lang.org/ https://janet-lang.org/

jfk13 4y ago

Ah, it's about databases... the title here immediately made me think of a much more hardcore "migration" effort: https://www.amusingplanet.com/2019/10/an-incredible-move-indiana-bell.html https://www.amusingplanet.com/2019/10/an-incredible-move-ind...

dboreham 4y ago
Keeps happening: something that had a name (schema evolution) gets given another name by people who never heard the first name. Recurse.

jbverschoor 4y ago

I dunno.. people seem to like 0 downtime migrations, but really, for most companies a little bit of downtime or degraded functionality doesn't matter. Most applications aren't even large enough for the downtime to be more than a couple of minutes. Having some scheduled downtime saves you a lot of complexity of writing, monitoring, and finalizing these migrations. It also makes it a lot easier to have a consistent state in terms of code+data. The article doesn't mention how to deal with different datamodels / constraints etc.

XorNot 4y ago
It's simpler then that: "zero downtime" isn't free, and has a direct cost to implement - one you potentially pay every single migration, for which you do not necessarily make enough or any money back on. In particular most SaaS providers with a subscription model would be hard-pressed to care: they're not selling ads per click, so provided you don't lose users over it, there's zero value. In fact it's probably more valuable to take the downtime and use the savings in dev time and effort to ensure you have an expedient rollback and recovery strategy - that will cost you users.
- KiranRao0 4y ago
  > "zero downtime" isn't free, and has a direct cost to implement Thank you for writing this. I was implicitly trying to convey this in the article, but glad to have it be explicit.
- WJW 4y ago
  At least for Rails there are several gems available (ie https://github.com/WeTransfer/ghost_adapter https://github.com/WeTransfer/ghost_adapter or https://github.com/departurerb/departure https://github.com/departurerb/departure) that seamlessly hook into the existing migration system and will run all eligible migrations through gh-ost or pt-osc as needed. You're right that it's not free but it isn't all that far off either. That said, online schema migrations are a specialized tool designed for very big tables that take hours to run an ALTER TABLE on. If all your tables are small enough that alterations take less than a second or so, don't bother and just block the table for a bit. It's fine.

kasey_junk 4y ago

When I was first starting out writing applications it was very common for the DBA in charge of a database to require all writes to go to a stored procedure and all reads to go through views. It was at times onerous but it made migrations like these a snap. With most migrations/rdms it could even be atomic.