3 ms·

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

by rixed 2y ago

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

lucideer 2y ago
> 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)
- rixed 2y ago
  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.
  - lucideer 2y ago
    Great summary. Tysm.
hendiatris 2y ago
Sqitch is an incredibly under appreciated tool. It doesn’t have a business pushing it like flyway and liquibase, so it isn’t as widely known, but I vastly prefer it to comparable migration tools.
mdaniel 2y ago
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
- jokethrowaway 2y ago
  I don't think it's that bad but I used sqitch from the start of the project - so maybe I am the problem I also don't think rebasing is the nightmare everybody makes it out to be. git rebase -i follow the instructions and you are good to go
- filterfish 2y ago
  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.
rswail 2y ago
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 that a column has been added/removed is hard. There's the "physical" issues of modifying a schema, and sqitch is great for that. But handling the "logical" issues of schema migration is more than an automated tool for running scripts. This tool (pgroll) allows you to actually test the modified schema for logical validity without impacting the ongoing operations, which to me, seems like a win.
- rixed 2y ago
  > 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.
  - rswail 2y ago
    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.

by rixed 2y ago

lucideer 2y ago

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

rixed 2y ago
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.
- lucideer 2y ago
  Great summary. Tysm.

hendiatris 2y ago

Sqitch is an incredibly under appreciated tool. It doesn’t have a business pushing it like flyway and liquibase, so it isn’t as widely known, but I vastly prefer it to comparable migration tools.

mdaniel 2y ago

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

jokethrowaway 2y ago
I don't think it's that bad but I used sqitch from the start of the project - so maybe I am the problem I also don't think rebasing is the nightmare everybody makes it out to be. git rebase -i follow the instructions and you are good to go
filterfish 2y ago
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.

rswail 2y ago

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 that a column has been added/removed is hard. There's the "physical" issues of modifying a schema, and sqitch is great for that. But handling the "logical" issues of schema migration is more than an automated tool for running scripts. This tool (pgroll) allows you to actually test the modified schema for logical validity without impacting the ongoing operations, which to me, seems like a win.

rixed 2y ago
> 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.
- rswail 2y ago
  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.