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

Pgroll – Zero-downtime, reversible, schema changes for PostgreSQL (new website)

pgroll.com

Post reply on HN
Print view View original

21–30 of 55 posts

Re: Pgroll – Zero-downtime, reversible, schema changes for PostgreSQL (new website)

#21

Post by hinkley »

tudorg wrote: post #20
ltbarcly3 wrote: post #13

This seems nice at first glance, but lets think about it for a minute: Before: You had a .sql file and if you messed up you had to revert manually. Maybe you would pre-write the revert script, maybe your site is down if you mess up. It's super easy to understand what is happening though. Now: you use pgroll. An absolute heaping ton of magic is happening behind the scenes. Every table is replaced by a view and tables…

I think it is fair criticism that this adds complexity. However, I do have a couple of counter-arguments: In order to avoid downtime and locking, you generally need multiple steps (e.g. some variation of add another column, backfill the data, remove previous column). You can codify this in long guidebooks on how to do schema changes (for example this one from gitlab [1]). You also need to orchestrate your app deploym…

I don't necessarily see friction as a bad thing. I had to explain a lot at my last job that yeah, we did in fact do a whole bunch of work to smooth out a process. Why are we "only" seeing a 70% reduction in error rate per unit time? Well that's because we're using the process 3x as much now. We reduced errors by 10x which makes people more likely to use the process. Supply and demand.

A bit of friction on tasks that can result in massive problems can cause people to tap the brakes a bit.

Re: Pgroll – Zero-downtime, reversible, schema changes for PostgreSQL (new website)

#22

Post by erulabs »

ltbarcly3 wrote: post #18
erulabs wrote: post #15

Earlier quoted context omitted.

What is good for the goose is not necessarily good for the gander. Obviously for startups, you're 100% right. Just announce a brief downtime and/or do migrations after-hours. Keep it simple, no one will care if their requests timeout once every week for 30 seconds. If your company has hundreds of developers making changes across every timezone and downtime (or developers being blocked waiting for scheduled merge wind…

The bigger and more important your company is, the less you should rely on a tool like this. You have more budget to invest in operations and less tolerance for being down for 3 days when a tool like this has a bug that takes your site down. You should hire DBA's and operations staff that understand how to apply db migrations in a safe way, and have them review them before and/or apply them during deployments. It's n…

You may be right about pgroll specifically, I haven't looked at it closely. However, you can't really say "just do db migrations in a safe way". The way your DBA staff would "apply migrations safely" would be to use ghost-tables and views and triggers and locks - ie: they would write pt-online-schema-change or gh-ost or VReplication or, well, pgroll. These tools were born at places like Facebook or Github by the team responsible for applying migrations safely.

The argument that "all software has bugs" applies to both the database itself as well as the software you're writing on top. Hence why "reversible" is the 2nd selling-point here.

Re: Pgroll – Zero-downtime, reversible, schema changes for PostgreSQL (new website)

#23

Post by Arctic_fly »

We've looked into this tool a couple times, haven't adopted it yet. The main issue is compatibility with prisma, which we use for defining our db's schema and automating migrations. If there were a slick integration between the two, that would go a long way to convincing us to adopt. In the meantime, we'll go with convenience and (slightly, almost immeasurably) lower reliability. We haven't had downtime due to a migration in months!

Re: Pgroll – Zero-downtime, reversible, schema changes for PostgreSQL (new website)

#24

Post by mdaniel »

rixed wrote: post #14

Pgroll shines if you are doing slow rollouts. Recently on the market for a tool to manage SQL migration patches with no need for slow rollouts, I reviewed many such tools and the one that impressed me was sqitch: https://github.com/sqitchers/sqitch So if you are interrested in this field and if Pgroll is not quite what you are looking for, I recommand you have a look at sqitch.

My experience with Sqitch was "all the fun of git rebase with all the fun of manual rollback and verification code" :-( I would never wish that upon my enemies

I'm open to the fact that we may have just had legacy antipatterns drug into the project, since it was shoehorned into the team by a similarly strongly opinionated advocate

Re: Pgroll – Zero-downtime, reversible, schema changes for PostgreSQL (new website)

#25

Post by notpushkin »

Arctic_fly wrote: post #23

We've looked into this tool a couple times, haven't adopted it yet. The main issue is compatibility with prisma, which we use for defining our db's schema and automating migrations. If there were a slick integration between the two, that would go a long way to convincing us to adopt. In the meantime, we'll go with convenience and (slightly, almost immeasurably) lower reliability. We haven't had downtime due to a migr…

Same but with Alembic! I'm wondering how hard would it be to open pgroll up for third party migration systems.

Re: Pgroll – Zero-downtime, reversible, schema changes for PostgreSQL (new website)

#26

Post by tudorg »

Arctic_fly wrote: post #23

We've looked into this tool a couple times, haven't adopted it yet. The main issue is compatibility with prisma, which we use for defining our db's schema and automating migrations. If there were a slick integration between the two, that would go a long way to convincing us to adopt. In the meantime, we'll go with convenience and (slightly, almost immeasurably) lower reliability. We haven't had downtime due to a migr…

Yes, this is the biggest issue right now and we’re working on a solution.

What we want to do is add the ability to generate the pgroll migrations based on the prisma generated migration files. Depending on the operation, you might need to add more info.

This will work fairly generally, not only prisma.

Re: Pgroll – Zero-downtime, reversible, schema changes for PostgreSQL (new website)

#27

Post by emmelaich »

erulabs wrote: post #15
ltbarcly3 wrote: post #13

This seems nice at first glance, but lets think about it for a minute: Before: You had a .sql file and if you messed up you had to revert manually. Maybe you would pre-write the revert script, maybe your site is down if you mess up. It's super easy to understand what is happening though. Now: you use pgroll. An absolute heaping ton of magic is happening behind the scenes. Every table is replaced by a view and tables…

What is good for the goose is not necessarily good for the gander. Obviously for startups, you're 100% right. Just announce a brief downtime and/or do migrations after-hours. Keep it simple, no one will care if their requests timeout once every week for 30 seconds. If your company has hundreds of developers making changes across every timezone and downtime (or developers being blocked waiting for scheduled merge wind…

I've found in practice, over many years in many industries that downtime is actually much easier to schedule and makes many things far far easier.

Re: Pgroll – Zero-downtime, reversible, schema changes for PostgreSQL (new website)

#29

Post by tudorg »

mdaniel wrote: post #28
colemannerd wrote: post #2

Are migrations still specified in json?

https://github.com/xataio/pgroll/issues/281 may be worth a :+1: even though it seems from tudorg's comment that they're really wedded to .json :-(

Ha ha, we're not wedded, I was only explaining why a .sql file is not quite enough. Using some json equivalent should be fine, thanks for pointing to the issue.
Post reply on HN