alt Hacker NewsThese 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...
Replies
Altering a column that already has data in production should be an absolute last resort, I don't think I've ever even done it, it's never 100% necessary
Locksmith is awesome, how am I just now discovering this?
Your comments re: database state are spot on. DDL can fail in subtle ways. It's not even enough to take a snapshot of the current state and validate; things can change under your feet.
Take adding a unique index on a column: a simple CREATE UNIQUE INDEX statement, right? But you realize it will fail if the values aren't unique already, so you run a SELECT query to confirm. Yep, all unique. Deploy the app which runs the migration on startup - fail. A non-unique key arrived in the time between your queries.
Even more fun if you CREATE UNIQUE INDEX CONCURRENTLY and a non-unique key arrives in the middle of the DDL execution.
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.
What about the classic pg_dump running in the background?
I would go further than this and argue that most bugs during database migrations happen because of mismatched application behavior with the action of the migration, not because the DDL was wrong. E.g. removing something that was still being relied on by the application, or starting to backfill data to a new column before the application is fully writing it. The most insidious version of this is where one application server doesn't have it's code updated (or comes back from the dead, etc) and causes the problem.
At a previous job what I did to prevent that was to have a special DB table that would signal what capabilities the database has, and the code would read that table and compare to its own requirements. If a capability required by the database was not present in the code (e.g. code not updated for a new feature) the code would refuse to make any writes to the DB and error all incoming requests. Likewise if a capability required by the code was missing from the database (e.g. code deployed too soon and database migration not run yet) it again would refuse requests. Before setting a feature to required in the DB and preforming the migration with feature flags, we could check all known application servers were reporting compatibility with the new feature (if any were down or not reporting at the time, they will be blocked in the next step - prioritizing safety over liveness)