Move Fast and Migrate Things: How We Automated Migrations in Postgres
benchling.engineering
Move Fast and Migrate Things: How We Automated Migrations in Postgres
1–10 of 55 posts
Re: Move Fast and Migrate Things: How We Automated Migrations in Postgres
#2These are the types of challenges I've been thinking about for the last year -- nice to be able to read the approach taken by others.
Re: Move Fast and Migrate Things: How We Automated Migrations in Postgres
#3I 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.
Re: Move Fast and Migrate Things: How We Automated Migrations in Postgres
#4I 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;…
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.
Re: Move Fast and Migrate Things: How We Automated Migrations in Postgres
#5This is an amazing writeup. I'm currently solving the "migrations" problem for a side project of my own, and have basically resolved myself in the short term to be OK with short downtime for the sake of making migrations somewhat trivial.
And honestly? I hate this answer. As a solo dev it's pragmatic, but the solutions described in this article are _SO NICE_ that I'd love to leverage them.
If there's any way that those deprecate_column and rename functionalities could make their way into OSS/upstream support, I'd have a field day. (Those who know more about PG than I do and perhaps may be able to suggest another workaround, feel free, I'm very much learning this space as I go)
If nothing else, thanks to the benchling team for taking the time to write such a clear yet technical expose. This really hit the sweet spot of "explanations without uneccessary verbosity, technical without being impenetrable, and giving sufficient explanations of motivations and tradeoffs/pitfalls" and will give me a north star for where I aim my own DB work.
Re: Move Fast and Migrate Things: How We Automated Migrations in Postgres
#6This is an amazing writeup. I'm currently solving the "migrations" problem for a side project of my own, and have basically resolved myself in the short term to be OK with short downtime for the sake of making migrations somewhat trivial. And honestly? I hate this answer. As a solo dev it's pragmatic, but the solutions described in this article are _SO NICE_ that I'd love to leverage them. If there's any way that tho…
Original author here. Thanks for the kind words!
We would love open source some of the work we did - there are a few edge cases to still work out with deprecated_column and renamed_to before I’d be comfortable doing that, but definitely agree that may be generally useful.
Re: Move Fast and Migrate Things: How We Automated Migrations in Postgres
#7I 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;…
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.
Re: Move Fast and Migrate Things: How We Automated Migrations in Postgres
#8I 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;…
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.
Re: Move Fast and Migrate Things: How We Automated Migrations in Postgres
#9I 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;…
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.
Re: Move Fast and Migrate Things: How We Automated Migrations in Postgres
#10I 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;…
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 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.