31–40 of 55 posts
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…
Here's a few other migration tooling options for Postgres: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...
Can you add https://github.com/shayonj/pg-osc ? It's my favorite PG migration tool.
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.
Earlier quoted context omitted.
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…
Earlier quoted context omitted.
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…
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.
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 untangle the mess that gets made otherwise, restore data from backups, go to meetings to explain outages to executives, etc etc so if you want to keep your DBA's happy and able to sign out of work at a reasonable hour, make sure they get to approve schema changes before they go out. Not to mention that very few engineers have a good understanding of schema design or how to write performant schemas, how to index data, etc. Getting a good migration the first time is just so much less painful and time consuming than trying to deal with bad ones.
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?
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.
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.
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 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
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…
pgroll is written in Go, so if you were to accept configuration written in CUE, you would get the best of all worlds:
* Besides support for comments, there's first-class support in Go for writing configuration in CUE and then importing it: https://cuelang.org/docs/concept/how-cue-works-with-go/#load...
* SQL can written in files with .sql extensions and embedded in CUE with @embed: https://cuelang.org/docs/howto/embed-files-in-cue-evaluation...