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...
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)
exactly: already has data. It’s not the statement that’s unsafe, it’s the size of the table. That’s what all pattern matching migration checkers get wrong.
You might be releasing a new feature gradually and you realised your schema is slightly wrong and want to alter a column type. You’ve got some tiny volume of data in one production cluster. Is it safe?
A pseudo rule determining the safety for any arbitrary migration that causes a rewrite could be:
smt.is_rewrite and tbl.size < 10MB
No: on your 10TB orders table
To accurately model migration safety you don’t really care about the statement: you care about the effects (locks, rewrites, additions, etc). That’s what is safe or unsafe.
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.
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.
- 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
Edit: Looking this up, I’m not sure this is correct.
simplified you can think of a statement outside of a transaction as starting an implicit transaction just for itself
and (normal) locks are in general hold until the end of the transaction (while also allowing re-entrance from subsequent queries on the same transaction)
practically
- there are edge cases (e.g. Advisory Locks, but in general you don't want to use them)
- you normally(^1) would want to run your pg migration as a single transaction (but there are edge cases). And in turn the OPs idea of pre-acquiring locks would be for the whole transaction anyway. Plus it was just a general idea, so the end result could be more like an "expect lock" statement maybe with some scan ahead ability then an "acquire lock".
(^1): Exceptions include certain operations which need to be in different transactions, and some painful situations where too much data is touched/changed/computed and you need a lot of very careful handling you common small-ish PG DB use-case isn't exposed to (and in turn a lot of "naive but often good enough" migration setups can't handle either...)
It ensures migrations don't lock the database but maybe more importantly, it allows zero-downtime rollouts for your application as well by supporting both the old and new schema during the deployment, and automatically data between them. It also handles backfills and more that usually require multiple, separate deployments when using standard SQL commands.
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
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.
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 :)
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.
The checks/explanations are fairly simple and straightforward so it also makes a good reference regardless of whether you're using the library