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.
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)
I came here to say this too. Most bugs I run into are when databases schema versions interact with multiple software versions. If you are a low availability service, you can just take down the service and update the schema atomically, but 90% of the time you actually need to write backwards compatible migrations and forwards compatible code and coordinate the rollout accordingly.
My rule of thumb is no more than two distinct software versions can share a database at the same time. This effectively rules out database sharing between services. That way you push the problem to an API layer, which is better equipped to handle maintaining compatibility between many client versions.
There is really nothing specific to API layer (I am assuming you mean REST API layer) in building backwards- and forwards-compatibility compared to databases. If anything, it is less powerful.
It is pretty easy to do with databases as well, you just need to adopt the right mindset.
For instance, if you think having an "api/vX" of an endpoint is acceptable, then it must also be to create a duplicate table/relation — you'll have exactly the same challenges in maintaining consistency between the two, though RDBMS offer quite a bit of tooling built-in.
I believe there are patterns that always work, but might not be optimal for all circumstances.
Eg. you could have a mirror table that you keep in sync with triggers without any constraints or foreign keys, do the migration on it, and then switch them around when ready.
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.
Wouldn't that indicate an issue in your business logic attempting to do this in the first place?
Or if you are relying on DB to fail and your business side to detect and react, you'd still have that built into the business logic so you can just keep retrying the schema migration until it succeeds (if it's rare this happens).
So while I can see how this can happen, it basically is a bug and it means you are doing the migration yet the invariants are not going to be satisfied. Basically, even if it succeeds, you will have future inserts fail with unique constraint being broken.
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
This largely depends on the kind of software system you are working on. These sorts of DDL migrations are common on the kinds of Rails apps I’ve worked on over my career. Size of the table, traffic patterns, who uses associated features, tolerance for small downtime windows, or orchestrating a multi-phase zero-downtime migration are all ways to justify these migrations, and is preferable to alternatives that would be comparably over-engineered for that app’s business and technical context.
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
Yes: on your tiny new table
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.
Right I'm agreeing with you but if there even is a column that's already in production you should just assume it has data in it so I would favor just a blanket ban on altering columns at all
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.
The biggest problem with that right now is that postgres doesn't allow explicit lock acquisitions (via the LOCK stmt) for all the object types. I've been thinking we should change that for a while, albeit partially just because it is useful for writing tests. With that added, a mode that refuses new lock acquisitions wouldn't be that hard...
I invite you to start a discussion on the lists about that feature, I've wished for it before.
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
That’s an interesting idea but not all locks are held for the duration of the statement. A lot of them take a less intrusive lock for the whole statement and take an exclusive lock for a very short time when they finish up.
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...)
I've been executing migrations for almost two decades and I only feel safe when I can run them against a replica first, no matter what the automated checks tell. There are state-dependent gotchas every where.
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]
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 :)
Great concept and nice demo site! It's kind of interesting to me what the Jev model is ambiently aware of and what it isn't. For instance, this query comes back as safe:
-- fails if any rows exist in the table since the new column
-- is not null and also has no default value.
ALTER TABLE users ADD COLUMN status text NOT NULL;
That's like, one of the most common mistakes ever when it comes to database migrations. Gemini/ChatGPT/Claude all flag this immediately and can offer improvements.
I bring this up not because it's a gotcha but because I wonder how you think about the utility of rules-based classifiers like this. At a certain point, if you need to extend your rules engines with tons of edge cases like this, why not just ask a true LLM? Or, have you considered extending your default ruleset with generated rules — maybe point an LLM at use-the-index, other db docs, or just ask it to expand the ruleset based on its own knowledge?
Although useful, I think migration linters like this one don't give enough peace of mind. I have maintained a zero-downtime schema migration tool for several years now that tries to cover all the different ways one can shoot oneself in the foot: https://github.com/fabianlindfors/reshape
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.
We're kinda blessed to not have to worry too much about it, because the tool we use for schema management [1] removes the need for migrations for most additive schema changes.
It refuses to auto-generate potentially destructive migrations so you have to write those by hand, and this tool would be useful in that case. But we review those more carefully since they're the exception and not the rule.
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.
stop complaining and just look at the godoc? not everything needs to be for You (experts). there are millions of upcoming net new coders who are most fluid at navigating when presented this way. and it’s easy for other models too. humans are no longer the motive force of information transmission.
Models don't necessarily produce documentation that's good for other models. It's still good to spend some time paring down AI to something simpler, clearer, and more concise.
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.
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...
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)
I came here to say this too. Most bugs I run into are when databases schema versions interact with multiple software versions. If you are a low availability service, you can just take down the service and update the schema atomically, but 90% of the time you actually need to write backwards compatible migrations and forwards compatible code and coordinate the rollout accordingly.
My rule of thumb is no more than two distinct software versions can share a database at the same time. This effectively rules out database sharing between services. That way you push the problem to an API layer, which is better equipped to handle maintaining compatibility between many client versions.
There is really nothing specific to API layer (I am assuming you mean REST API layer) in building backwards- and forwards-compatibility compared to databases. If anything, it is less powerful.
It is pretty easy to do with databases as well, you just need to adopt the right mindset.
For instance, if you think having an "api/vX" of an endpoint is acceptable, then it must also be to create a duplicate table/relation — you'll have exactly the same challenges in maintaining consistency between the two, though RDBMS offer quite a bit of tooling built-in.
[delayed]
I believe there are patterns that always work, but might not be optimal for all circumstances.
Eg. you could have a mirror table that you keep in sync with triggers without any constraints or foreign keys, do the migration on it, and then switch them around when ready.
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.
Wouldn't that indicate an issue in your business logic attempting to do this in the first place?
Or if you are relying on DB to fail and your business side to detect and react, you'd still have that built into the business logic so you can just keep retrying the schema migration until it succeeds (if it's rare this happens).
So while I can see how this can happen, it basically is a bug and it means you are doing the migration yet the invariants are not going to be satisfied. Basically, even if it succeeds, you will have future inserts fail with unique constraint being broken.
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
This largely depends on the kind of software system you are working on. These sorts of DDL migrations are common on the kinds of Rails apps I’ve worked on over my career. Size of the table, traffic patterns, who uses associated features, tolerance for small downtime windows, or orchestrating a multi-phase zero-downtime migration are all ways to justify these migrations, and is preferable to alternatives that would be comparably over-engineered for that app’s business and technical context.
> that already has data in production
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:
Yes: on your tiny new tableNo: 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.
Right I'm agreeing with you but if there even is a column that's already in production you should just assume it has data in it so I would favor just a blanket ban on altering columns at all
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.
The biggest problem with that right now is that postgres doesn't allow explicit lock acquisitions (via the LOCK stmt) for all the object types. I've been thinking we should change that for a while, albeit partially just because it is useful for writing tests. With that added, a mode that refuses new lock acquisitions wouldn't be that hard...
I invite you to start a discussion on the lists about that feature, I've wished for it before.
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
This is essentially how my tool discovers locks for a given arbitrary DDL statement.
That’s an interesting idea but not all locks are held for the duration of the statement. A lot of them take a less intrusive lock for the whole statement and take an exclusive lock for a very short time when they finish up.
Edit: Looking this up, I’m not sure this is correct.
> whole statement
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...)
What about the classic pg_dump running in the background?
I've been executing migrations for almost two decades and I only feel safe when I can run them against a replica first, no matter what the automated checks tell. There are state-dependent gotchas every where.
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
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.
Appreciate it. Will definitely add a CLI option for it.
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.
Great concept and nice demo site! It's kind of interesting to me what the Jev model is ambiently aware of and what it isn't. For instance, this query comes back as safe:
That's like, one of the most common mistakes ever when it comes to database migrations. Gemini/ChatGPT/Claude all flag this immediately and can offer improvements.I bring this up not because it's a gotcha but because I wonder how you think about the utility of rules-based classifiers like this. At a certain point, if you need to extend your rules engines with tons of edge cases like this, why not just ask a true LLM? Or, have you considered extending your default ruleset with generated rules — maybe point an LLM at use-the-index, other db docs, or just ask it to expand the ruleset based on its own knowledge?
That query is completely safe!
The migration will just fail with no harm done.
Although useful, I think migration linters like this one don't give enough peace of mind. I have maintained a zero-downtime schema migration tool for several years now that tries to cover all the different ways one can shoot oneself in the foot: https://github.com/fabianlindfors/reshape
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.
I built pglockanalyze for the purpose of a actually seeing in action what locks your migrations will acquire: https://github.com/agis/pglockanalyze
We're kinda blessed to not have to worry too much about it, because the tool we use for schema management [1] removes the need for migrations for most additive schema changes.
It refuses to auto-generate potentially destructive migrations so you have to write those by hand, and this tool would be useful in that case. But we review those more carefully since they're the exception and not the rule.
[1] https://gitlab.com/deltaex/schematic
There's also https://github.com/ankane/strong_migrations for Ruby
The checks/explanations are fairly simple and straightforward so it also makes a good reference regardless of whether you're using the library
really like the browser-only approach here - catching the obvious migration risks locally before they ever reach CI feels super useful
Very cool! I wrote a go library for this https://onwardpg.solberg.is
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.
stop complaining and just look at the godoc? not everything needs to be for You (experts). there are millions of upcoming net new coders who are most fluid at navigating when presented this way. and it’s easy for other models too. humans are no longer the motive force of information transmission.
Models don't necessarily produce documentation that's good for other models. It's still good to spend some time paring down AI to something simpler, clearer, and more concise.
Good feedback - I condensed the webpage
Hahaha, "One command holds the feature together". That page is a gallery or horrors.
Nice. Integrating it on CI and catching there is still the best way/place. Having said, it doesn't work like that in practice.
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.