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.
I think you could automate this with 2 transactions
- connection A, lock timeout=0, acquire unwanted lock
- connection B, lock timeout=0, run migration
- collection A, rollback
Then connection B will fail if it tries to acquire an undesirable lock since it will conflict with A. You'd be adding a very small window when you're actually holding the undesirable lock, though
orf · · focus · HN ↗
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. <a href="https://github.com/orf/locksmith" rel="nofollow">https://github.com/orf/locksmith
2. <a href="https://github.com/orf/locksmith/blob/f8798c6ee92bfae10d416c49aae33bc4afaaabf1/crates/locksmith/src/oracle.rs#L34" rel="nofollow">https://github.com/orf/locksmith/blob/f8798c6ee92bfae10d416c...
williamdclt · · focus · HN ↗
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.
nijave · · focus · HN ↗
- connection A, lock timeout=0, acquire unwanted lock
- connection B, lock timeout=0, run migration
- collection A, rollback
Then connection B will fail if it tries to acquire an undesirable lock since it will conflict with A. You'd be adding a very small window when you're actually holding the undesirable lock, though
orf · · focus · HN ↗