[HN Gopher] Pgroll - Zero-downtime, reversible, schema changes f...
___________________________________________________________________
Pgroll - Zero-downtime, reversible, schema changes for PostgreSQL
(new website)
Author : todsacerdoti
Score : 127 points
Date : 2024-12-11 15:51 UTC (7 hours ago)
(HTM) web link (pgroll.com)
(TXT) w3m dump (pgroll.com)
| colemannerd wrote:
| Are migrations still specified in json?
| tudorg wrote:
| 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 data.
|
| The sql2pgroll converter is creating the higher level migration
| files, but leaves placeholder for the "up" / "down" data
| migrations.
|
| The issue where sql2pgroll is tracked is this one:
| https://github.com/xataio/pgroll/issues/504
| mdaniel wrote:
| 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 :-(
| tudorg wrote:
| 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.
| jherskovic wrote:
| The website generally looks good, but the font in the headings is
| weird. What is up with the "d" in it?!
| tudorg wrote:
| This is the font, we wanted something a bit different :)
| https://unbounded.polkadot.network/
| rtuin wrote:
| This seems like a great tool! I like how it "simply" works by
| putting a view on top of the actual table. This concept is widely
| used in the data engineering world (in dbt).
|
| There must be an easier way to write migrations for pgroll
| though. I mean, JSON, really?
| cpursley wrote:
| Here's a few other migration tooling options for Postgres:
| https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...
| brycethornton wrote:
| Can you add https://github.com/shayonj/pg-osc? It's my favorite
| PG migration tool.
| shayonj wrote:
| Thank you Bryce <3
| konradb wrote:
| Cool new site! I got 'invalid invite' on the discord link from
| the front page, I only mention as I'd like to join it.
| tudorg wrote:
| Oops, fixing it now. This link should work:
| https://discord.com/invite/kvAcQKh7vm
| Dinux wrote:
| Is this related to supabase? There seems to be a link?
| tudorg wrote:
| Actually no, this is a project by Xata. What link do you mean?
| ltbarcly3 wrote:
| 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 have tons of 'secret' columns holding old data. Every
| operation that made sense before (ALTER TABLE foo ADD COLUMN bar
| INTEGER NOT NULL) turns into some ugly json mess (look at the
| docs, it's horrible) that then drops into the magic of pgroll to
| get turned into who knows what operations in the db, with the
| _PROMISE_ that it can be reversed safely. Since all software has
| bugs, pgroll has bugs. When pgroll leaves you in a broken state,
| which it will sooner or later, you are just FUCKED. You will have
| to reverse engineer all it 's complicated magic and try to get it
| back on the rails live.
|
| You're trading something you understand and is simple, but maybe
| not super convenient, for something that is magically convenient
| and which will eventually put a bullet in your head. It's a
| horrific product, don't use it.
| erulabs wrote:
| 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 windows) costs real money or
| creates real problems other than optics, something like this or
| Vitess (MySQL) is definitely worth it.
|
| Engineering should not be a "one-size-fits-all" type of job,
| and while I do love postgres, my main gripe with the community
| is that the "keep it simple stupid" mentality persists well
| beyond its sell-by date in many cases.
| ltbarcly3 wrote:
| 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 not hard to do manually, just a little bit of extra work
| (not that much extra). With a little bit of feedback
| engineers will learn the basics of how to write migrations
| that are safe and then the use case for this product is
| dramatically reduced anyway.
|
| You keep things simple because you need to be able to
| understand what is going on to work with it later. pgroll is
| inherently complex and poorly designed, but even if it wasn't
| it is still bad to use something that you can't reasonably
| correct the problems it causes when it breaks.
| erulabs wrote:
| 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.
| emmelaich wrote:
| 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.
| tudorg wrote:
| 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 deployments in between those steps, and you often need to
| have some backwards compatibility code.
|
| This is all fine but: 1. it slows you down and 2. it's manual
| and error prone. With pgroll, the process is always the same
| (start pgroll migration, deploy code, complete/rollback
| migration) so the team can exercise it often.
|
| Second, while any software has bugs, it's worth noting that the
| main reason roll-ing back is quick and safe with pgroll is that
| it only has to drop views and any hidden columns. While the
| physical schema is changed, it is in a backwards compatible way
| until with complete the migration, so you can always skip the
| views if you have to bypass whatever pgroll is doing.
|
| [1]:
| https://docs.gitlab.com/ee/development/migration_style_guide...
| hinkley wrote:
| 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.
| rixed wrote:
| 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.
| lucideer wrote:
| > _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)
| hendiatris wrote:
| 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 wrote:
| 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
| Arctic_fly wrote:
| 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!
| notpushkin wrote:
| Same but with Alembic! I'm wondering how hard would it be to
| open pgroll up for third party migration systems.
| tudorg wrote:
| 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.
| muratsu wrote:
| Unrelated to the OP product but I'm curious how people are
| solving this issue on smaller scale with nextjs + prisma/drizzle.
| Do you just run the builtin migrate script with npm run?
___________________________________________________________________
(page generated 2024-12-11 23:00 UTC)