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

51–55 of 55 posts

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

#51

Post by pas »

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.

I guess it is because you migrate data between application versions. And sometimes this means simply applying an SQL script file, sometimes you also combine this with upgrading the DB server itself, then you might also need to do the whole export-import dance (async replication + export-upgrade-import on a secondary instance, then promote that instance and either drop the old and create a new secondary, or do the same exp-imp on the old-primary).

Good question though, it could be "schema upgrade", but sometimes there's a downgrade, so somehow people started calling it migration.

And it's especially important to note, that users don't care about the schema, operators care a bit, developers slightly more, and if there is a dedicated DBA in the loop, now that person might finally really care about it, but they rarely write the migration scripts. And developers care about data. (At least that's my impression.)

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

#52

Post by pas »

seanwilson wrote: post #24

Anyone else hold back on releasing side projects because having to do data migrations with stored user data prevents you from being able to aggressively refactoring your code? Is there a good compromise for this?

If the side project is "small", then the migration can be done in a few hours tops, so user impact is probably negligible. Have you encountered a more severe problem maybe?

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

#53

Post by seanwilson »

pas wrote: post #52
seanwilson wrote: post #24

Anyone else hold back on releasing side projects because having to do data migrations with stored user data prevents you from being able to aggressively refactoring your code? Is there a good compromise for this?

If the side project is "small", then the migration can be done in a few hours tops, so user impact is probably negligible. Have you encountered a more severe problem maybe?

I just meant I notice I feel a lot less freedom after initial release. When I haven't released something yet, I can wipe and recreate the database at any time, move, rename etc. as I want to try things out. Once I've released though, changes become significantly more painful and risky. Perhaps a migration is a few hours tops but that's compared to a few minutes tops when you don't have active users yet and when you're a solo developer each hour is a significant amount of your productivity.

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

#54

Post by pas »

seanwilson wrote: post #53
pas wrote: post #52

Earlier quoted context omitted.

If the side project is "small", then the migration can be done in a few hours tops, so user impact is probably negligible. Have you encountered a more severe problem maybe?

I just meant I notice I feel a lot less freedom after initial release. When I haven't released something yet, I can wipe and recreate the database at any time, move, rename etc. as I want to try things out. Once I've released though, changes become significantly more painful and risky. Perhaps a migration is a few hours tops but that's compared to a few minutes tops when you don't have active users yet and when you'r…

> I just meant I notice I feel a lot less freedom after initial release.

Well, there's no arguing with that. And it usually doesn't matter if you have 2 or 200000 users, the migration has to be perfect - and automated - anyway :)

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

#55

Post by benmmurphy »

pas wrote: post #50
benmmurphy wrote: post #15

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

> 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. Why is this bad? Can you handle this on the application side somehow? Even if it just means restarting Rails when the migration has finished. Or the problem is that you want 0 downtime and 0 UX impact migration?

It is bad that you have to write a bunch of extra code in your application to retry on this failure. I guess potentially you could modify the rails framework to automatically retry transactions in this situation and aggressively purge the prepared statement cache but this is really a rails change and not an application change. i don't think it is possible to hook the transaction logic in rails to retry transactions safely from application code.

as you guessed restarting rails does fix the problem but the issue is no zero downtime migrations for migrations that should be trivially safe. ie adding a column

Post reply on HN