Thing that bit me most wasn't the DDL itself, it was lock queuing. An ADD COLUMN is instant but if it waits behind a long read, every query behind it piles up too. Lock_timeout plus retry saved us more than any clever migration tool.
I like the concept and I'm trying to work out if it'll be useful for me, but I just cannot get past the cookie-cutter LLM style of the landing page. A Go library doesn't need a marketing page with a seemingly unrelated artwork and call-outs like "Climb from easy to nightmare →". Put a runnable example front and centre.
Author here: Adding some context. I led the Postgres platform team (2019-23) at Cloudflare and we were supporting 170+ growing product teams. One of the constant asks is schema migration review. We published a lot of best practices, added CI checks however, it was still hard to catch. Also, I tried to explain the internals of how the locking (rewrite) works, but I realized most of the devs just want the answer - Is it safe or not safe to run?
Not sure if it rings a bell, the name is a reference to the Silicon Valley Jian Yang's hot dog or not hot dog app.
Also, I understand the decision of safe vs not-safe depends heavily on data/histogram and edge cases, but still quite a lot of low-hanging issues can be easily caught with a deterministic rule engine. So I ported pg_savior[1] and used sql parser from libpg-query-node[2] which compiles as WASM, so it entirely runs on the browser. No telemetry, no login. Source attached [3]
It's not immediately clear from the README, but is it easy to run with multiple profiles like "backwards-compatible", "revertable" (both data and schema) and "destructive" for that final clean-up in multi-staged no-downtime migrations? Basically common subsets of "safe-ness" of the schema migration queries.
I imagine it can be tuned, but I'd love this for all my projects.
And since I am currently on a project doing MS SQL (gasp), that'd be cool too ;)
I am familiar with an "is it a hot dog" app from back in the day, bit would have never made the connection :)
If you continue working on this a good direction to go in would be to package it as a command line tool, so it can be integrated into testing and release processes.
These kinds of rule-based migration safety checks are simple, but hardly complete.
The problem is that some migration safety depends on the state of the database, which isn’t represented in the DDL statement alone. For example, altering a column type is either a no-op or an exclusive locked table rewrite depending on the original type of the column.
There are other footguns that can happen if the column you’re altering is a foreign key, where multiple tables can be locked.
I went down a rabbit hole a few years ago and built a system[1] to introspect a given migration against a live schema, and actually let Postgres tell you what it’s doing[2].
It would be great to have better built-in support for this (EXPLAIN for DDL statements?), but this direction feels safer and more accurate than static rulesets.
Safety also depends on the size/activity of a table being altered (i.e rewriting an empty table is fine). Having an accurate representation of the locks and actions performed by the database lets you integrate with production metrics to actually determine real-world safety across a fleet of databases, rather than guessing.
The way I wished Postgres DDLs worked (at least optionally) is that you have to explicitly acquire the correct lock before a DDL statement, or it just immediately fails. Something like:
ACQUIRE ACCESS SHARE TABLE LOCK ON my_table
ALTER TABLE my_table ALTER COLUMN my_column TYPE bigint
This way I _know_ that if the operation needs a stronger lock than I thought or than I'm willing to give it, it will just fail rather than locking up my database and causing unexpected downtime.
Thing that bit me most wasn't the DDL itself, it was lock queuing. An ADD COLUMN is instant but if it waits behind a long read, every query behind it piles up too. Lock_timeout plus retry saved us more than any clever migration tool.
Agree. `lock_timeout` will go a long way in terms of damage control.
Very cool! I wrote a go library for this https://onwardpg.solberg.is
Nice. Integrating it on CI and catching there is still the best way/place. Having said, it doesn't work like that in practice.
I like the concept and I'm trying to work out if it'll be useful for me, but I just cannot get past the cookie-cutter LLM style of the landing page. A Go library doesn't need a marketing page with a seemingly unrelated artwork and call-outs like "Climb from easy to nightmare →". Put a runnable example front and centre.
Author here: Adding some context. I led the Postgres platform team (2019-23) at Cloudflare and we were supporting 170+ growing product teams. One of the constant asks is schema migration review. We published a lot of best practices, added CI checks however, it was still hard to catch. Also, I tried to explain the internals of how the locking (rewrite) works, but I realized most of the devs just want the answer - Is it safe or not safe to run?
Not sure if it rings a bell, the name is a reference to the Silicon Valley Jian Yang's hot dog or not hot dog app.
Also, I understand the decision of safe vs not-safe depends heavily on data/histogram and edge cases, but still quite a lot of low-hanging issues can be easily caught with a deterministic rule engine. So I ported pg_savior[1] and used sql parser from libpg-query-node[2] which compiles as WASM, so it entirely runs on the browser. No telemetry, no login. Source attached [3]
[1] https://github.com/viggy28/pg_savior [2] https://github.com/constructive-io/libpg-query-node [3] https://github.com/viggy28/safe-not-safe
Wow, great idea!
It's not immediately clear from the README, but is it easy to run with multiple profiles like "backwards-compatible", "revertable" (both data and schema) and "destructive" for that final clean-up in multi-staged no-downtime migrations? Basically common subsets of "safe-ness" of the schema migration queries.
I imagine it can be tuned, but I'd love this for all my projects.
And since I am currently on a project doing MS SQL (gasp), that'd be cool too ;)
I am familiar with an "is it a hot dog" app from back in the day, bit would have never made the connection :)
Thanks you.
Certainly, there is a lot of room to improve the README. Overall the project is very much alpha.
You're right. Currently, it's very binary. The answer is more nuanced and it should classify it based on the profiles like you mentioned.
Also, I noticed parsers for other databases that compiles to WASM. So, all running on client side.
This looks extremely useful.
If you continue working on this a good direction to go in would be to package it as a command line tool, so it can be integrated into testing and release processes.
really like the browser-only approach here - catching the obvious migration risks locally before they ever reach CI feels super useful
These kinds of rule-based migration safety checks are simple, but hardly complete.
The problem is that some migration safety depends on the state of the database, which isn’t represented in the DDL statement alone. For example, altering a column type is either a no-op or an exclusive locked table rewrite depending on the original type of the column.
There are other footguns that can happen if the column you’re altering is a foreign key, where multiple tables can be locked.
I went down a rabbit hole a few years ago and built a system[1] to introspect a given migration against a live schema, and actually let Postgres tell you what it’s doing[2].
It would be great to have better built-in support for this (EXPLAIN for DDL statements?), but this direction feels safer and more accurate than static rulesets.
Safety also depends on the size/activity of a table being altered (i.e rewriting an empty table is fine). Having an accurate representation of the locks and actions performed by the database lets you integrate with production metrics to actually determine real-world safety across a fleet of databases, rather than guessing.
1. https://github.com/orf/locksmith
2. https://github.com/orf/locksmith/blob/f8798c6ee92bfae10d416c...
The way I wished Postgres DDLs worked (at least optionally) is that you have to explicitly acquire the correct lock before a DDL statement, or it just immediately fails. Something like:
ACQUIRE ACCESS SHARE TABLE LOCK ON my_table ALTER TABLE my_table ALTER COLUMN my_column TYPE bigint
This way I _know_ that if the operation needs a stronger lock than I thought or than I'm willing to give it, it will just fail rather than locking up my database and causing unexpected downtime.