4 ms·
If any devs read this, for MySQL, you may want to consider using the update method adopted by many larger companies - something like pt-online-schema-change (or
by falcolas 13y ago
If any devs read this, for MySQL, you may want to consider using the update method adopted by many larger companies - something like pt-online-schema-change (or make the alter method pluggable so someone else can).
Basically, create a table as a copy of the old table, set up triggers to update the new table as data pours into the old table, alter the new table, and then do a rename.
This buys you some rollback capabilities, but more importantly it limits the impact on production traffic as the alters run. Of course, this is only really an issue on tables with hundreds of thousands of rows, but it's better than the naïve approach.
- famousactress 13y agoI've come to think this is something that I'll do earlier rather than later on projects moving forward. The last few projects I've worked on have put this off "until we get bigger", which inevitably ends up affecting agility and the types of migrations you're willing to do and when you're willing to release them. As complex as the approach is, I think I'd rather bite it off early in future projects. Or, use postgres!
- falcolas 13y agoPersonally, I don't start worrying about it until it actually becomes a problem (i.e. when an alter actually takes more than a few seconds to run). At which point it's easy to migrate to using pt-online-schema-change. Then again, I do most of my DB maintenance manually (I have DBA experience), so while I do use Django in production, I have less need to rely on generic automation tools. Or use PostgreSQL, Oracle, MSSQL, or... lots of options there. [EDIT] As a final thought on the topic, never be afraid of making schema changes, even in MySQL. It may require a bit more work to implement, but there are a bevy of solutions (most of which can be automated) which can limit or eliminate the "pain" caused by such alters.
- thezilch 13y agoOr, assuming you already run, at least, one secondary write or read-only server, use Percona XtraDB Cluster / Galera. There are caveats for pt-online-schema-change that make it not usable in all cases, and they are not necessarily edge cases.
- falcolas 13y agoThere are caveats for PXC as well, which may prevent you from using it regularly which are completely unrelated to schema maintenance. Plus, PXC uses innodb under the covers as well, which means you still have to wait while alters on tables occur, and that they can not be done in transactions (well, not entirely true, but close enough for these purposes).
- deathanatos 13y agoYou still suffer a brief outage though, no? We thought about this a lot in a previous life of mine, and the problem with what I think you describe is that once everything was said and done, you'd be left with: ALTER TABLE RENAME foo foo_old; -- You're down right here! ALTER TABLE RENAME foo_new foo; -- …and we're back! (For those new to MySQL, ALTER TABLE is immune to transactions.) Admittedly, it might be less than applying the migration direct, so it might be worthwhile. (We just ate the downtime, and tested things to avoid surprises.)