Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Move Fast and Migrate Things: How We Automated Migrations in Postgres

benchling.engineering

Post reply on HN
Print view View original

11–20 of 55 posts

Re: Move Fast and Migrate Things: How We Automated Migrations in Postgres

#11

Post by rabi_penguin »

Daishiman wrote: post #3

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;…

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

[2] https://github.com/kvesteri/sqlalchemy-continuum

[3] https://github.com/python-rope/rope

[4] https://hypothesis.readthedocs.io/en/latest/

Re: Move Fast and Migrate Things: How We Automated Migrations in Postgres

#12

Post by emmelaich »

Daishiman wrote: post #3

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;…

Category theory to the rescue -- as always!

http://math.mit.edu/~dspivak/informatics/FunctorialDataMigra...

Re: Move Fast and Migrate Things: How We Automated Migrations in Postgres

#13

Post by Scarbutt »

Daishiman wrote: post #3

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 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.

Re: Move Fast and Migrate Things: How We Automated Migrations in Postgres

#14

Post by BenMorganIO »

vineetg wrote: post #6
existencebox wrote: post #5

This 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.

If it's just edge cases, you can just open source it and document the edge cases. The community will likely fork and help with those edge cases.

Re: Move Fast and Migrate Things: How We Automated Migrations in Postgres

#15

Post by benmmurphy »

In postgresql if you are using prepared statements and are doing a 'select star' and drop or add a column then the prepared statement will start failing. This is kind of bad when you are doing transactions because the bad statement will taint your transaction and you will need to restart from the beginning. Select star is incompatible with prepared statements and postgresql which might also explain why SQL Alchemy explicitly names columns in select statements. Rails will do 'Select star' so prepared statements are the first thing I turn off in Rails/Postgresql projects. [Maybe they have a fix now?]

Re: Move Fast and Migrate Things: How We Automated Migrations in Postgres

#16

Post by JamesSwift »

Daishiman wrote: post #3

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;…

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/da...

Re: Move Fast and Migrate Things: How We Automated Migrations in Postgres

#17

Post by wwweston »

How did it come to be that some portion of the industry use the term "migration" to describe changes/updates to a database?

As far as I can tell, it's a really poor fit. It generates the expectation that movement of existing schema + maybe data from one host to another or one environment to another. What's usually happening instead is essentially a schema diff / mutation.

Re: Move Fast and Migrate Things: How We Automated Migrations in Postgres

#19

Post by Ixiaus »

wwweston wrote: post #17

How did it come to be that some portion of the industry use the term "migration" to describe changes/updates to a database? As far as I can tell, it's a really poor fit. It generates the expectation that movement of existing schema + maybe data from one host to another or one environment to another. What's usually happening instead is essentially a schema diff / mutation.

Because when you change the representation of data at rest, you need to "migrate" that data to the new representation.

I agree that schema changes are not migration, but I think the author correctly uses the word "migration" to mean migrating their data in the database to some new schema representation.

Re: Move Fast and Migrate Things: How We Automated Migrations in Postgres

#20

Post by pdimitar »

aidos wrote: post #10
dcosson wrote: post #8

Earlier quoted context omitted.

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.

What do you mean by your last sentence? How does PG 11 help?
Post reply on HN