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

51–55 of 55 posts

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

#51

Post by thayne »

ltbarcly3 wrote: post #50
thayne wrote: post #49

Earlier quoted context omitted.

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

> Dude, if you have "extremely large datasets" then why are you acting like your advice is generally applicable

I'm not. At least that wasn't my intention. My reply was in the context of "bigger and more important" companies, which are also more likely to have extremely large datasets, and where taking long, or even short periods of downtime for a database migration is unacceptable.

> migrations won't be faster when you use a tool like this, they will be slower

Sure, but they can be run without taking downtime.

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

Both can be true. Most migrations will take a lot less time, maybe minutes, maybe hours, sometimes days. Migrations that take weeks are rare, but they do happen. But, having a migration take several hours with no downtime can often be preferable to taking a few minutes of downtime, even if you are ok with having scheduled downtime, because it will unblock work dependent on the new schema sooner. Of course, it depends on your situation. If schema changes are rare, you probably don't need to worry about something like this. If your tables are small enough that schema changes can be done quickly with minimal or no downtime, then you probably don't need this. But there are cases where tools like this fill a need.

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

#52

Post by evanelias »

ltbarcly3 wrote: post #50
thayne wrote: post #49

Earlier quoted context omitted.

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

> migrations won't be faster when you use a tool like this, they will be slower

That statement is conflating some things, re: "tool like this". In most cases, schema management tooling is a separate layer above online schema change tooling.

Online schema change tools (e.g. pt-online-schema-change) are designed to execute table alterations in a non-disruptive fashion, even if the table is huge and/or being written heavily. These tools are indeed often slower than running an ALTER TABLE directly in the database, because the non-disruptive design involves a more complex set of operations occurring in the background. No real way around that; it's a trade-off.

In contrast, schema management tools -- ranging from simple migration tools, to more complex services/daemons -- are focused on tracking schema information (either imperative migrations or declarative desired-state) in a source control repo, and executing SQL either directly or by shelling out to another tool. They sometimes include more complex components such as schema linters, verification logic, schedulers, shard mappings, web GUIs, APIs for checking whether a change has been completed yet, etc.

Pgroll is rather unusual in that it overlaps into both domains. That approach has pros and cons, but that isn't my point here; rather, I'd say it's best to avoid generalizing about "tools like this" when discussing a tool that works differently than most other software in the same space.

That aside, as someone who has spent most of the majority of the past two decades working on database infrastructure at a wide range of different scales and companies, I largely disagree with your statement about schema changes being a trivial part of the database team's day.

In my experience, that only happens in two scenarios, at opposite ends of a spectrum: either a company that isn't making many product changes and therefore doesn't have many corresponding schema changes; OR a company that has enough schema changes for safe self-service schema management automation to be a worthwhile investment.

Most "tech" companies do fall into that latter extreme though, and greatly benefit from self-service schema management automation. This is about product development velocity, not table size.

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

#53

Post by lucideer »

rixed wrote: post #41
lucideer wrote: post #16

Earlier quoted context omitted.

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

Great summary. Tysm.

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

#54

Post by tianzhou »

cmcconomy wrote: post #47

We use liquibase for these purposes, but I welcome any tool that has people take their versioning and historical artifacts seriously!

We have been building such a tool as well, please check out https://github.com/bytebase/bytebase

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

#55

Post by brycethornton »

paulryanrogers wrote: post #33
brycethornton wrote: post #12

Earlier quoted context omitted.

Can you add https://github.com/shayonj/pg-osc ? It's my favorite PG migration tool.

How many have you used? At ORIS I wrote a Laravel wrapper for PTOSC and really miss it now that I'm back on PostgreSQL. Now I mostly use updatable views in front of modified tables then drop-swap things later once any transitional backfilling is done.

Hey Paul! Try out pg-osc if you haven't already. I use it quite a bit for this type of thing. https://github.com/shayonj/pg-osc
Post reply on HN