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

41–50 of 55 posts

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

#41

Post by rixed »

lucideer wrote: post #16
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.

> if Pgroll is not quite what you are looking for If you don't need slow rollouts, what would you say the downsides of using Pgroll over Sqitch would be? (I've used neither, but I got the impression from the op that slow rollouts was a feature, not a requirement)

What Pgroll offers does not come for free: it puts some constraints on your schema and what migrations you can perform. Most "automatic migration" tools I've seen are designed for the easy migrations ("horizontal" changes, ie adding/removing/altering columns). But a schema migration is not equivalent to a view and a pair of triggers in general, even if in practice it's often limited to that. (simplest if a bit contrived example I can come up with: imagine you have a short table with 3 columns and 4 lines, and you wish to use instead a transposition of this table with 4 columns and 3 lines)

It's also why I'm dubious of "revert patches" in general. If there exist a revert patch for a migration, that's an easy migration. Sqitch can use a revert patch, like it can use a verify statement, but just for the convenience; it does not require them.

Yet, 9 schema migrations out of 10 are easy ones that pgroll handle nicely. It's a bit like using an ORM : if you just need a DB to store objects manipulated only in your program, sure go ahead use an ORM; but if your DB is the core of your business then you'd better not let an ORM anywhere near your schema.

At the end of the day, I'm under the impression that if one wants to handle the general case then one has to keep the whole previous DB and apps in one hand and the new DB and apps in the other, and transition customers from the former to the later. That's much less work if you don't need slow rollout.

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

#42

Post by filterfish »

mdaniel wrote: post #24
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

I've been using sqitch for years and haven't had any problems with it. Though they haven't been big projects with lots of devs so I may be using in it's sweet spot.

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

#43

Post by rixed »

rswail wrote: post #38
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.

Using sqitch in production, the main issue is that it effectively has no knowledge of what is happening except for running psql files for you. So it's hard to keep track of what the actual resulting schema should look like after each change has been applied to work out whether it's correct. Getting people to write deploy/revert scripts are relatively easy. Asking them to write verify scripts that do more than check t…

> Using sqitch in production, the main issue is that it effectively has no knowledge of what is happening except for running psql files for you.

I came to the conclusion that you can not have anything more than that without compromising on how you can change your schema.

> So it's hard to keep track of what the actual resulting schema should look like after each change has been applied to work out whether it's correct.

At first sight, this is a gripe that has to be addressed to SQL itself that the SQL to change a schema can not trivially be inferred from the "before" and "after" schema definitions. But probably there is no way around it, il all generality.

One can store the current version of the schema in the source repository and make sure that the schema extracted after one or several applications of the migration match that.

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

#44

Post by radicalbyte »

polskibus wrote: post #37

Does anyone know of similar tool for MS SQL?

Not public.. I build something very similar a decade ago but that was part of a low-code system so had access to far more information than most databases (the full history of schema changes with a little bit of metadata over the change).

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

#45

Post by rswail »

rixed wrote: post #43
rswail wrote: post #38

Earlier quoted context omitted.

Using sqitch in production, the main issue is that it effectively has no knowledge of what is happening except for running psql files for you. So it's hard to keep track of what the actual resulting schema should look like after each change has been applied to work out whether it's correct. Getting people to write deploy/revert scripts are relatively easy. Asking them to write verify scripts that do more than check t…

> Using sqitch in production, the main issue is that it effectively has no knowledge of what is happening except for running psql files for you. I came to the conclusion that you can not have anything more than that without compromising on how you can change your schema. > So it's hard to keep track of what the actual resulting schema should look like after each change has been applied to work out whether it's correc…

Asking devs to build their own dependency system for the changes is also part of the issue.

Really the only way I can think of is some sort of DAG of DDL blobs, but I have no idea whether you can express a combination of DDL and DML changes into the one tree so that they can be properly diff'ed.

Changes in production are not just changes in the schema, there are changes to data as well. Really the entire RDBMS needs to have "time" and DDL incorporated into the relations.

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

#46

Post by joelhaasnoot »

tudorg wrote: post #4
colemannerd wrote: post #2

Are migrations still specified in json?

Yes, but we're working on a converter from DDL sql to pgroll json. The reason for JSON is because the pgroll migrations are "higher level". For example, let's say that you are adding a new unique column that should infer its data from an existing column (e.g. split `name` into `first_name` and `last_name`). The pgroll migration contains not only the info that new columns are added, but also about how to backfill the…

Lol, it's funny you need a higher level language and choose... JSON.

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

#49

Post by thayne »

ltbarcly3 wrote: post #35
thayne wrote: post #34

Earlier quoted context omitted.

And if you don't use a tool like that (whether off the shelf, or home built), then your DBAs will be a bottleneck to any schema changes, which will lead to them being overworked and stressed, which will lead to them making more mistakes.

I think you are way out of your realm of experience here. Nope, schema changes are not that common compared to other tasks and will only account for a small fraction of your DBA's day. I can say this from copious (over 20 years) experience. They will make FAR fewer mistakes than some random engineer. It takes less time (like a fraction, 1/20 or less) to validate and give feedback on schema changes than it does to unt…

> It takes less time (like a fraction, 1/20 or less) to validate and give feedback on schema changes

But that's the thing, with tools like this, pt-online-schema-change etc., it is reasonable for a DBA to review changes written by other engineers. Without them, when you have extremely large data sets, it is necessary to do magic with triggers, views, etc. to safely make certain kinds of changes. And doing that is beyond the experience of most non-dba engineers, and has a lot of nuance to it. I'm not saying DBAs shouldn't have to approve schema changes. I'm saying they shouldn't have to write complicated migration scripts by hand.

> I think you are way out of your realm of experience here.

Or maybe my experience is different than yours.

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

#50

Post by ltbarcly3 »

thayne wrote: post #49
ltbarcly3 wrote: post #35

Earlier quoted context omitted.

I think you are way out of your realm of experience here. Nope, schema changes are not that common compared to other tasks and will only account for a small fraction of your DBA's day. I can say this from copious (over 20 years) experience. They will make FAR fewer mistakes than some random engineer. It takes less time (like a fraction, 1/20 or less) to validate and give feedback on schema changes than it does to unt…

> It takes less time (like a fraction, 1/20 or less) to validate and give feedback on schema changes But that's the thing, with tools like this, pt-online-schema-change etc., it is reasonable for a DBA to review changes written by other engineers. Without them, when you have extremely large data sets, it is necessary to do magic with triggers, views, etc. to safely make certain kinds of changes. And doing that is bey…

Dude, if you have "extremely large datasets" then why are you acting like your advice is generally applicable. Fine, for db's with trillions of rows then you need to be extremely careful when doing migrations.

However, migrations won't be faster when you use a tool like this, they will be slower. So maybe you are fine with migrations taking weeks and weeks to complete, but in that case how many migrations are you running that your DBA's are overwhelmed by the volume of migrations to approve? Which one is true?

Post reply on HN