5 ms·

I am convinced that data migration is definitely one of the hardest problems in data management and systems engineering. There are basically no solutions today

I am convinced that data migration is definitely one of the hardest problems in data management and systems engineering. There are basically no solutions today that satisfy fundamental requirements such as minimizing downtime and guaranteeing correctness. It is _such_ a huge problem that most inexperienced developers see kicking the problem down the line with NoSQL document storage as a viable alternative (it isn't; you'll be either dealing with migrating all data forever and special-casing every old version of your documents, or writing even more convoluted migration logic). It's also clear that even the most modern ORMs and query builders have not been built in mind to consider the issues that arise in migrating data. It would be a refreshing thing to see more research devoted to this problem. Unfortunately, migrations end up being so different from each other with such heterogenous requirements that we'll probably be working on this for a really long time.

zepolen 8y ago
Migrations in a strict relation database always guarantee correctness. It's the need to roll back that causes issues. That is solved by not doing destructive changes (removing a column) until the software is stable and a few iterations have passed. The issue of downtime is semi-valid but can likewise be worked around by batch migrating data in manageable chunks vs nuking.
rb808 8y ago
Amen. Tools that let you work with version A and B of a db schema would be great. Then you could update DB independently of code release. Its one of the things that I fear the most (and prevents me from loving RMDBs) is the thought of doing a big DB upgrade then having to roll back because of software not performing as expected.
dcosson 8y ago
I think DB's could definitely do more to expose what the cost of various operations are, it would be great if you could "explain" a migration before you run it like you can with a query and it would calculate a rough cost, how many rows need to be touched, what resources need to be locked, even how likely the required locks are to cause contention with other frequently-taken locks based on system statistics, etc. But I was thinking about this recently and I feel like theres's some low hanging fruit in the migration frameworks themselves which, at least as far as I'm aware, all just completely punt on this problem. Rails, Alembic, and every other framework I've used will let you write a migration like adding a new non-nullable column, or renaming an existing column, things that can be really slow on a big or frequently written table and/or will cause problems during the rollout if an old version of the code is still running. It doesn't seem like it would be that hard to add a safe mode where the framework will block at least some of the most common variations of these unsafe migrations. Maybe it's harder than I realize, or maybe it's just a matter of anyone opening up some PR's and actually implementing this.
- aidos 8y ago
  I guess the issue with some of that stuff is that the solution is to do it in stages. Eg, adding a non-nullable column is made safe by doing part of the work and then updating rows in batches. Alembic runs all migrations in a transaction, so there’s not much point batching. It really needs to be handled outside alembic to make sense in the model. I guess that’s all a moot point now in Postgres 11 anyway.
  - pdimitar 8y ago
    What do you mean by your last sentence? How does PG 11 help?
    - koolba 8y ago
      Postgres v11 adds default values for NOT NULL columns without a full table rewrite.
- tomnipotent 8y ago
  > do more to expose what the cost of various operations are Both Postgres & SQL Server support EXPLAIN on DML without running the statement (with varying levels of depth). CREATE TEMPORARY TABLE tmp_blah (num INT); EXPLAIN INSERT INTO tmp_blah SELECT * FROM generate_series(1, 100000) ORDER BY random(); QUERY PLAN Insert on tmp_blah (cost=62.33..74.83 rows=1000 width=4) -> Subquery Scan on "*SELECT*" (cost=62.33..74.83 rows=1000 width=4) -> Sort (cost=62.33..64.83 rows=1000 width=12) Sort Key: (random()) -> Function Scan on generate_series (cost=0.00..12.50 rows=1000 width=12)
  - jclulow 8y ago
    One challenge with that is that the query plan often depends on the current table composition statistics. If a value is relatively common (or uncommon), or the size of the table has grown or shrunk, you may end up with a materially different (and conceivably substantially worse) plan.
djrobstep 8y ago
Strongly agree with this. So much so that I wrote a schema comparison tool that allows you to autogenerate migration scripts and explicitly test for correctness. Schemas are good, but the traditional tooling around changing them (rails/django migrations) is really bad. People inevitably cut corners because it's too hard to do things the right way.
- pdimitar 8y ago
  Have you open-sourced your tool?
  - djrobstep 8y ago
    Yes: https://migra.djrobstep.com https://migra.djrobstep.com
    - ruslan_talpa 8y ago
      How is it different/better then apgdiff?
rabi_penguin 8y ago
I recently discovered anchor modeling [1], which forms the relational underpinnings of sqlalchemy-continuum [2], which I haven't used but I'm quite curious about. I've done ad-hoc, manual versions of anchor modeling for most data migrations I've done in my career, and I agree with you on how hard it is. It's high risk, difficult to verify, and repair work after the fact if a botched migration happens is also expensive. With diligence and enough resources though, it is doable. But, it could be a lot easier. I think that more sophisticated static analysis and migration generation tools would really help out quite a bit in making this a reality, especially if you combine it with something. Having something like rope[3] for generating migrations and hypothesis[4] for using property-based testing to generate tested cases would make things nice as well. Definitely a hard problem, and definitely a worthwhile one to solve. If our team ever gets some free time to build a toolkit, we'd enjoy building some tooling to put all of this stuff together! [1] https://en.wikipedia.org/wiki/Anchor_modeling https://en.wikipedia.org/wiki/Anchor_modeling [2] https://github.com/kvesteri/sqlalchemy-continuum https://github.com/kvesteri/sqlalchemy-continuum [3] https://github.com/python-rope/rope https://github.com/python-rope/rope [4] https://hypothesis.readthedocs.io/en/latest/ https://hypothesis.readthedocs.io/en/latest/
emmelaich 8y ago
Category theory to the rescue -- as always! http://math.mit.edu/~dspivak/informatics/FunctorialDataMigration.pdf http://math.mit.edu/~dspivak/informatics/FunctorialDataMigra...
Scarbutt 8y ago
You say NoSQL document but have you work with graph databases like neo4j that allow for flexible schemas? I think they mostly solve this problem but don't have the uptake of RDBMs because of matureness.
- Daishiman 8y ago
  The problem is that without a table lock of some sort you cannot guarantee that all the documents in a store satisfy the schema definition. The NoSQL solution is basically say, "assume that nothing could ever possibly respect any semblance of a schema and build special cases around everything". It is a very unproductive way of thinking, taking defensive programming to the extreme. Also, joins will be a necessity for all sorts of data querying. At the end of the day, schemas are a necessity for 98% of all work, whether defined through types or ad-hoc.
JamesSwift 8y ago
I don't know, at my current job I've been introduced to DACPACs[1] and I really like it. The ability to do schema/data compares between arbitrary environments and generate the migrations in real time is awesome. [1] - https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/data-tier-applications?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/relational-databases/da...
- sansnomme 8y ago
  Doesn't Django already do this?
  - Izkata 8y ago
    Skimming through the page, it looks like there's a lot more functionality there than Django has.
mcintyre1994 8y ago
> NoSQL document storage as a viable alternative (it isn't; you'll be either dealing with migrating all data forever and special-casing every old version of your documents, or writing even more convoluted migration logic). In practice (with Mongo at least) you end up with migrations being from arbitrary JSON to different arbitrary JSON, and come to rely on the Javascript runtime for anything even a bit complex. It's definitely convoluted (albeit extremely powerful), but I think the biggest issue is that generating backward migrations even if you did non-destructive operations is completely impossible.
- pas 8y ago
  You are not barred from using a schema in your upper layer. And it is very much advised to do so to maintain sanity. It just means that the migration can be done on-the-fly as data is touched / read / written, without downtime, but with support for both new-and-old versions in your backend. (Or if you want the SQL style migration, then yes, you need to write a script. And perform a flag day style backend update. Which is not advised.) In reality, usually best migration strategies - sql or no - use a two phase process. First expansive changes run, which remain backward compatible with the old schema/structure, the backend is updated, then later when the new version of the backend is sort of verified to work well with the new schema/structure, a contraction can be run which removes the old structures/columns/tables/etc. This is especially helpful for providing blue-green automated deployment (which is great for continuous deployments). And what is missing is usually a tool to verify that your expansive changes are truly backward compatible, and that the contractive changes don't contract too much (don't delete any of the new structures). - And this is easier if you simply manage this as part of "the backend". (Or it can be simply extracted into a service, which wraps the backing/data store.)

zepolen 8y ago

Migrations in a strict relation database always guarantee correctness. It's the need to roll back that causes issues. That is solved by not doing destructive changes (removing a column) until the software is stable and a few iterations have passed. The issue of downtime is semi-valid but can likewise be worked around by batch migrating data in manageable chunks vs nuking.

rb808 8y ago

Amen. Tools that let you work with version A and B of a db schema would be great. Then you could update DB independently of code release. Its one of the things that I fear the most (and prevents me from loving RMDBs) is the thought of doing a big DB upgrade then having to roll back because of software not performing as expected.

dcosson 8y ago

I think DB's could definitely do more to expose what the cost of various operations are, it would be great if you could "explain" a migration before you run it like you can with a query and it would calculate a rough cost, how many rows need to be touched, what resources need to be locked, even how likely the required locks are to cause contention with other frequently-taken locks based on system statistics, etc. But I was thinking about this recently and I feel like theres's some low hanging fruit in the migration frameworks themselves which, at least as far as I'm aware, all just completely punt on this problem. Rails, Alembic, and every other framework I've used will let you write a migration like adding a new non-nullable column, or renaming an existing column, things that can be really slow on a big or frequently written table and/or will cause problems during the rollout if an old version of the code is still running. It doesn't seem like it would be that hard to add a safe mode where the framework will block at least some of the most common variations of these unsafe migrations. Maybe it's harder than I realize, or maybe it's just a matter of anyone opening up some PR's and actually implementing this.

aidos 8y ago
I guess the issue with some of that stuff is that the solution is to do it in stages. Eg, adding a non-nullable column is made safe by doing part of the work and then updating rows in batches. Alembic runs all migrations in a transaction, so there’s not much point batching. It really needs to be handled outside alembic to make sense in the model. I guess that’s all a moot point now in Postgres 11 anyway.
- pdimitar 8y ago
  What do you mean by your last sentence? How does PG 11 help?
  - koolba 8y ago
    Postgres v11 adds default values for NOT NULL columns without a full table rewrite.
tomnipotent 8y ago
> do more to expose what the cost of various operations are Both Postgres & SQL Server support EXPLAIN on DML without running the statement (with varying levels of depth). CREATE TEMPORARY TABLE tmp_blah (num INT); EXPLAIN INSERT INTO tmp_blah SELECT * FROM generate_series(1, 100000) ORDER BY random(); QUERY PLAN Insert on tmp_blah (cost=62.33..74.83 rows=1000 width=4) -> Subquery Scan on "*SELECT*" (cost=62.33..74.83 rows=1000 width=4) -> Sort (cost=62.33..64.83 rows=1000 width=12) Sort Key: (random()) -> Function Scan on generate_series (cost=0.00..12.50 rows=1000 width=12)
- jclulow 8y ago
  One challenge with that is that the query plan often depends on the current table composition statistics. If a value is relatively common (or uncommon), or the size of the table has grown or shrunk, you may end up with a materially different (and conceivably substantially worse) plan.

djrobstep 8y ago

Strongly agree with this. So much so that I wrote a schema comparison tool that allows you to autogenerate migration scripts and explicitly test for correctness. Schemas are good, but the traditional tooling around changing them (rails/django migrations) is really bad. People inevitably cut corners because it's too hard to do things the right way.

pdimitar 8y ago
Have you open-sourced your tool?
- djrobstep 8y ago
  Yes: https://migra.djrobstep.com https://migra.djrobstep.com
  - ruslan_talpa 8y ago
    How is it different/better then apgdiff?

rabi_penguin 8y ago

I recently discovered anchor modeling [1], which forms the relational underpinnings of sqlalchemy-continuum [2], which I haven't used but I'm quite curious about. I've done ad-hoc, manual versions of anchor modeling for most data migrations I've done in my career, and I agree with you on how hard it is. It's high risk, difficult to verify, and repair work after the fact if a botched migration happens is also expensive. With diligence and enough resources though, it is doable. But, it could be a lot easier. I think that more sophisticated static analysis and migration generation tools would really help out quite a bit in making this a reality, especially if you combine it with something. Having something like rope[3] for generating migrations and hypothesis[4] for using property-based testing to generate tested cases would make things nice as well. Definitely a hard problem, and definitely a worthwhile one to solve. If our team ever gets some free time to build a toolkit, we'd enjoy building some tooling to put all of this stuff together! [1] https://en.wikipedia.org/wiki/Anchor_modeling https://en.wikipedia.org/wiki/Anchor_modeling [2] https://github.com/kvesteri/sqlalchemy-continuum https://github.com/kvesteri/sqlalchemy-continuum [3] https://github.com/python-rope/rope https://github.com/python-rope/rope [4] https://hypothesis.readthedocs.io/en/latest/ https://hypothesis.readthedocs.io/en/latest/

emmelaich 8y ago

Category theory to the rescue -- as always! http://math.mit.edu/~dspivak/informatics/FunctorialDataMigration.pdf http://math.mit.edu/~dspivak/informatics/FunctorialDataMigra...

Scarbutt 8y ago

You say NoSQL document but have you work with graph databases like neo4j that allow for flexible schemas? I think they mostly solve this problem but don't have the uptake of RDBMs because of matureness.

Daishiman 8y ago
The problem is that without a table lock of some sort you cannot guarantee that all the documents in a store satisfy the schema definition. The NoSQL solution is basically say, "assume that nothing could ever possibly respect any semblance of a schema and build special cases around everything". It is a very unproductive way of thinking, taking defensive programming to the extreme. Also, joins will be a necessity for all sorts of data querying. At the end of the day, schemas are a necessity for 98% of all work, whether defined through types or ad-hoc.

JamesSwift 8y ago

I don't know, at my current job I've been introduced to DACPACs[1] and I really like it. The ability to do schema/data compares between arbitrary environments and generate the migrations in real time is awesome. [1] - https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/data-tier-applications?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/relational-databases/da...

sansnomme 8y ago
Doesn't Django already do this?
- Izkata 8y ago
  Skimming through the page, it looks like there's a lot more functionality there than Django has.

mcintyre1994 8y ago

> NoSQL document storage as a viable alternative (it isn't; you'll be either dealing with migrating all data forever and special-casing every old version of your documents, or writing even more convoluted migration logic). In practice (with Mongo at least) you end up with migrations being from arbitrary JSON to different arbitrary JSON, and come to rely on the Javascript runtime for anything even a bit complex. It's definitely convoluted (albeit extremely powerful), but I think the biggest issue is that generating backward migrations even if you did non-destructive operations is completely impossible.

pas 8y ago
You are not barred from using a schema in your upper layer. And it is very much advised to do so to maintain sanity. It just means that the migration can be done on-the-fly as data is touched / read / written, without downtime, but with support for both new-and-old versions in your backend. (Or if you want the SQL style migration, then yes, you need to write a script. And perform a flag day style backend update. Which is not advised.) In reality, usually best migration strategies - sql or no - use a two phase process. First expansive changes run, which remain backward compatible with the old schema/structure, the backend is updated, then later when the new version of the backend is sort of verified to work well with the new schema/structure, a contraction can be run which removes the old structures/columns/tables/etc. This is especially helpful for providing blue-green automated deployment (which is great for continuous deployments). And what is missing is usually a tool to verify that your expansive changes are truly backward compatible, and that the contractive changes don't contract too much (don't delete any of the new structures). - And this is easier if you simply manage this as part of "the backend". (Or it can be simply extracted into a service, which wraps the backing/data store.)