4 ms·

I'd think they create "append-only" migrations, that can only add columns or tables. Otherwise it wouldn't be possible to have migrations that work with both ol

I'd think they create "append-only" migrations, that can only add columns or tables. Otherwise it wouldn't be possible to have migrations that work with both old and new code.

derefr 5y ago
> Otherwise it wouldn't be possible to have migrations that work with both old and new code. Sure you can. Say that you've changed the type of a column in an incompatible way. You can, within a migration that executes as an SQL transaction: 1. rename the original table "out of the way" of the old code 2. add a new column of the new type 3. run an "INSERT ... SELECT ..." to populate the new column from a transformation of existing data 4. drop the old column of the old type 5. rename the new column to the old column's name 6. define a view with the name of the original table, that just queries through to the new (original + renamed + modified) table for most of the original columns, but which continues to serve the no-longer-existing column with its previous value, by computing its old-type value from its new-type value (+ data in other columns, if necessary.) Then either make sure that the new code is reading directly from the new table; or create a trivial passthrough view for the new version to use as well. (IMHO, as long as you've got writable-view support, every application-visible "table" should really just be a view, with its name suffixed with the ABI-major-compatibility-version of the application using it. Then the infrastructure team — and more specifically, a DBA, if you've got one — can do whatever they like with the underlying tables: refactoring them, partitioning them, moving them to other shards and forwarding them, etc. As long as all the views still work, and still produce the same query results, it doesn't matter what's underneath them.)
- freedomben 5y ago
  I wrote a blog about this for anyone who would like to learn more. The query strings get you around the paywall if it comes up: https://freedomben.medium.com/the-rules-of-clean-and-mostly-painless-migrations-75b8b0dec194?sk=789b59e1ce45077303daea7866acf508 https://freedomben.medium.com/the-rules-of-clean-and-mostly-... If anyone doesn't know what migrations are: https://freedomben.medium.com/what-are-database-migrations-5ad203d7d955?sk=c02e997ac674b3dc54f2e3f70d7e64f6 https://freedomben.medium.com/what-are-database-migrations-5...
- ransom1538 5y ago
  Not to be rude but this isn't how this works at all. Things like 'run an "INSERT ... SELECT ..."' can't happen at scale due to locking. How they actually do it is super rad: 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... tl;dr; They setup a system of triggers (updates,inserts,etc) , copy the data over, then run through all the data in the trigger system. percona developed all these fancy features as well to monitor replica data etc. Another way with cloud vms (terabyte+ tables), you image a replica, do the alter, let the replica catch up, image it, build replicas off that, promote this to master. Facebook's internal one I hear is close to this.
  - derefr 5y ago
    "This isn't how things work at all" implies that more than 0.0000001% of DBs are running "at scale." Most DBs people will ever deal with — including within big enterprises! — are things like customer databases that have maybe 100k records in them. The tables lock waiting for lower-xact-ID read traffic to clear, yes — for about 400ms. Because queries running on the DB are all point queries that don't run any longer than that. Or they're scheduled batch jobs that happen during a maintenance window. At scale, you're hopefully not using an RDBMS as a source of truth in the first place; but rather, using it as a CQRS/ES aggregate, downstream of a primary event store (itself likely some combination of a durable message-queue, and archival object storage for compacted event segments.) In that kind of setup, data migrations aren't the job of the RDBMS itself, but rather the job of the CQRS/ES framework — which can simply be taught a new aggregate that computes a single new column, and will start catching that aggregate up and making the data from it available. If your RDBMS is columnar (again, hopefully), you can just begin loading that new column in, no locking on the rest of the table required. IMHO, the trigger-based approach is a weak approximation of having a pre-normalization primary source. It's fine if you want to avoid rearchitecture, but in a HOLAP system (which is usually inevitable for these sorts of systems when their data architects have tried to focus on simplicity, as this leads to eschewing denormalized secondary representations in OLAP-focused stores) it will cause your write perf to degrade + bottleneck, which is actually the worst thing for locking. (I should know; I'm dealing with a HOLAP system right now with large numbers of computed indices, append-only OLTP inserts, and random OLAP reads; where the reads hold hundreds of locks each due to partitioning. Any time a write tx stays open for more than a few hundred milliseconds, the whole system degrades due to read locks piling up to the point that the DB begins to choke just on allocating and synchronizing them. The DB is only a few TB large, and the instance it's on has 1TB of memory and as many cores as one can get... but locking is locking.)
ninth_ant 5y ago
That is largely the case. For other, more complex cases where that is not possible, you migrate a portion of the userbase to a new db schema and codepath at the same time.
strken 5y ago
My impression is that once you're at Facebook scale, most of your migrations are massive undertakings that need to take into account things like "How many terabytes more space do we need?", "How do we control the load on our DB nodes while the migration is going on?", "How do we get data from cluster A to cluster B?", "Is adding this index going to take hours and break everything?", and so on. Some of the time you'll be spinning up an entirely new cluster rather than changing the schema of an old one, and when you do migrate an existing cluster there's some five-page document specifying a week-long plan with different phases. Then internally, they work around the inflexible db schemas by using offline batch processing tools or generic systems that can handle arbitrary data, for tasks that would be handled by the one DB in smaller systems.

I'd think they create "append-only" migrations, that can only add columns or tables. Otherwise it wouldn't be possible to have migrations that work with both old and new code.

derefr 5y ago

> Otherwise it wouldn't be possible to have migrations that work with both old and new code. Sure you can. Say that you've changed the type of a column in an incompatible way. You can, within a migration that executes as an SQL transaction: 1. rename the original table "out of the way" of the old code 2. add a new column of the new type 3. run an "INSERT ... SELECT ..." to populate the new column from a transformation of existing data 4. drop the old column of the old type 5. rename the new column to the old column's name 6. define a view with the name of the original table, that just queries through to the new (original + renamed + modified) table for most of the original columns, but which continues to serve the no-longer-existing column with its previous value, by computing its old-type value from its new-type value (+ data in other columns, if necessary.) Then either make sure that the new code is reading directly from the new table; or create a trivial passthrough view for the new version to use as well. (IMHO, as long as you've got writable-view support, every application-visible "table" should really just be a view, with its name suffixed with the ABI-major-compatibility-version of the application using it. Then the infrastructure team — and more specifically, a DBA, if you've got one — can do whatever they like with the underlying tables: refactoring them, partitioning them, moving them to other shards and forwarding them, etc. As long as all the views still work, and still produce the same query results, it doesn't matter what's underneath them.)

freedomben 5y ago
I wrote a blog about this for anyone who would like to learn more. The query strings get you around the paywall if it comes up: https://freedomben.medium.com/the-rules-of-clean-and-mostly-painless-migrations-75b8b0dec194?sk=789b59e1ce45077303daea7866acf508 https://freedomben.medium.com/the-rules-of-clean-and-mostly-... If anyone doesn't know what migrations are: https://freedomben.medium.com/what-are-database-migrations-5ad203d7d955?sk=c02e997ac674b3dc54f2e3f70d7e64f6 https://freedomben.medium.com/what-are-database-migrations-5...
ransom1538 5y ago
Not to be rude but this isn't how this works at all. Things like 'run an "INSERT ... SELECT ..."' can't happen at scale due to locking. How they actually do it is super rad: 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... tl;dr; They setup a system of triggers (updates,inserts,etc) , copy the data over, then run through all the data in the trigger system. percona developed all these fancy features as well to monitor replica data etc. Another way with cloud vms (terabyte+ tables), you image a replica, do the alter, let the replica catch up, image it, build replicas off that, promote this to master. Facebook's internal one I hear is close to this.
- derefr 5y ago
  "This isn't how things work at all" implies that more than 0.0000001% of DBs are running "at scale." Most DBs people will ever deal with — including within big enterprises! — are things like customer databases that have maybe 100k records in them. The tables lock waiting for lower-xact-ID read traffic to clear, yes — for about 400ms. Because queries running on the DB are all point queries that don't run any longer than that. Or they're scheduled batch jobs that happen during a maintenance window. At scale, you're hopefully not using an RDBMS as a source of truth in the first place; but rather, using it as a CQRS/ES aggregate, downstream of a primary event store (itself likely some combination of a durable message-queue, and archival object storage for compacted event segments.) In that kind of setup, data migrations aren't the job of the RDBMS itself, but rather the job of the CQRS/ES framework — which can simply be taught a new aggregate that computes a single new column, and will start catching that aggregate up and making the data from it available. If your RDBMS is columnar (again, hopefully), you can just begin loading that new column in, no locking on the rest of the table required. IMHO, the trigger-based approach is a weak approximation of having a pre-normalization primary source. It's fine if you want to avoid rearchitecture, but in a HOLAP system (which is usually inevitable for these sorts of systems when their data architects have tried to focus on simplicity, as this leads to eschewing denormalized secondary representations in OLAP-focused stores) it will cause your write perf to degrade + bottleneck, which is actually the worst thing for locking. (I should know; I'm dealing with a HOLAP system right now with large numbers of computed indices, append-only OLTP inserts, and random OLAP reads; where the reads hold hundreds of locks each due to partitioning. Any time a write tx stays open for more than a few hundred milliseconds, the whole system degrades due to read locks piling up to the point that the DB begins to choke just on allocating and synchronizing them. The DB is only a few TB large, and the instance it's on has 1TB of memory and as many cores as one can get... but locking is locking.)

ninth_ant 5y ago

That is largely the case. For other, more complex cases where that is not possible, you migrate a portion of the userbase to a new db schema and codepath at the same time.

strken 5y ago

My impression is that once you're at Facebook scale, most of your migrations are massive undertakings that need to take into account things like "How many terabytes more space do we need?", "How do we control the load on our DB nodes while the migration is going on?", "How do we get data from cluster A to cluster B?", "Is adding this index going to take hours and break everything?", and so on. Some of the time you'll be spinning up an entirely new cluster rather than changing the schema of an old one, and when you do migrate an existing cluster there's some five-page document specifying a week-long plan with different phases. Then internally, they work around the inflexible db schemas by using offline batch processing tools or generic systems that can handle arbitrary data, for tasks that would be handled by the one DB in smaller systems.