Is your Postgres migration safe or not safe?

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...

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;

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?

I built pglockanalyze for the purpose of a actually seeing in action what locks your migrations will acquire: https://github.com/agis/pglockanalyze

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.

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

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

[flagged]

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.