3 ms·

JP, author of the post here - let me know if you have any additions / questions!

planckscnst 12y ago
This is great. I've been wondering how to do this, and this is exactly what I had come up with. I'm glad to have confirmation that it's the normal way people do zero-downtime migrations. I might add one change. You say add a column migration, ship it, then add the feature that uses the column and ship again. I was planning on shipping the code at the same time (or even before) the migration, but having the feature disabled with a feature-flipper until the migration is done.
- jipiboily 12y ago
  Depending on your framework and ORM and how its column cache is working, it sounds like a good solution too!
sokoloff 12y ago
Another principles that will help this work: Make sure that you don't have any code that does "select " elsewhere in your system. Otherwise, the result sets' shape changes when you start adding columns. (And you can get into naming collisions where suddenly a select is ambiguous where it wasn't before (when both a and b have a column named ID, for example).
- sokoloff 12y ago
  Both instances of the word select above were supposed to be "select * ". I ran afoul of the HN formatting where * italicizes... It's obviously fine to do some selects... :)
buckbova 12y ago
More complicated scenarios occur when migrating millions/billions of records to a completely new data structure. 1. Create new structure 2. Add trigger to old tables for insert/update to new tables. 3. Create batch job that migrates records that aren't added by 2 above. 4. Ship. When migration of data is completed. 1. Deploy new application code to all servers. 2. Remove old tables & triggers only after code is deployed and verified.
Sanddancer 12y ago
I'd suggest putting somewhere in there making sure you create backups with the CREATE TABLE AS SELECT command. I've used it a lot, and it's another one of those steps that makes rollbacks, etc a lot easier.

This is great. I've been wondering how to do this, and this is exactly what I had come up with. I'm glad to have confirmation that it's the normal way people do zero-downtime migrations. I might add one change. You say add a column migration, ship it, then add the feature that uses the column and ship again. I was planning on shipping the code at the same time (or even before) the migration, but having the feature disabled with a feature-flipper until the migration is done.

jipiboily 12y ago
Depending on your framework and ORM and how its column cache is working, it sounds like a good solution too!

sokoloff 12y ago

Another principles that will help this work: Make sure that you don't have any code that does "select " elsewhere in your system. Otherwise, the result sets' shape changes when you start adding columns. (And you can get into naming collisions where suddenly a select is ambiguous where it wasn't before (when both a and b have a column named ID, for example).

sokoloff 12y ago
Both instances of the word select above were supposed to be "select * ". I ran afoul of the HN formatting where * italicizes... It's obviously fine to do some selects... :)

buckbova 12y ago

More complicated scenarios occur when migrating millions/billions of records to a completely new data structure. 1. Create new structure 2. Add trigger to old tables for insert/update to new tables. 3. Create batch job that migrates records that aren't added by 2 above. 4. Ship. When migration of data is completed. 1. Deploy new application code to all servers. 2. Remove old tables & triggers only after code is deployed and verified.

Sanddancer 12y ago

I'd suggest putting somewhere in there making sure you create backups with the CREATE TABLE AS SELECT command. I've used it a lot, and it's another one of those steps that makes rollbacks, etc a lot easier.