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

21–30 of 55 posts

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

#21

Post by pdimitar »

djrobstep wrote: post #9
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;…

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.

Have you open-sourced your tool?

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

#22

Post by djrobstep »

pdimitar wrote: post #21
djrobstep wrote: post #9

Earlier quoted context omitted.

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.

Have you open-sourced your tool?

Yes: https://migra.djrobstep.com

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

#23

Post by koolba »

pdimitar wrote: post #20
aidos wrote: post #10

Earlier quoted context omitted.

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?

Postgres v11 adds default values for NOT NULL columns without a full table rewrite.

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

#25

Post by alecbenzer »

Ixiaus wrote: post #19
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.

This is true, but it's a bit of an implementation detail. All I'm doing is a schema change; a migration is what the database does under the hood to facilitate it.

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

#26

Post by sebastian »

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.

I would love to see the code open sourced! (it's ok if you have edge cases pending as long as they have been documented or at least mentioned in a README). Managing and running migrations smoothly without downtime is a very common challenge that a lot of startups face and I think a lot of us have been reinventing the wheel over and over to handle this due to the lack of current offerings.

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

#28

Post by tomnipotent »

dcosson wrote: post #8
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 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…

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

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

#29

Post by jclulow »

tomnipotent wrote: post #28
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…

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

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.

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

#30

Post by tomnipotent »

jclulow wrote: post #29
tomnipotent wrote: post #28

Earlier quoted context omitted.

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

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.

I can't tell you how many times over 20 years I've heard a DBA tell me "the statistics weren't updated" after an incident.
Post reply on HN