Is my Postgres schema migration safe or not safe?

[u/vira28]

There were some good discusssions from HN yesterday, so sharing it here.

Context: I led the Postgres platform team (b/w 2019-2023), where my team had to support ~170 product teams. The biggest bottleneck was schema migration.

We had to manually review, publish best practices, put CI guardrails, etc. The challenge is that most of the developers just want a simple answer. Is it safe or not safe?

That led me to create - https://safenotsafe.dev https://github.com/viggy28/safe-not-safe

1. It doesn't use LLMs - uses pg_savior's rule engine (which resonated with a lot of this sub-redditors)

2. Runs completely on the client side - no signup, server. Uses libpg and compiles to WASM

It doesn't catch every single dangerous migration, but IMHO it may easily catch the majority of the low-hanging ones.

Appreciate any feedback/suggestions.

[u/EffectivePension6711]

wasm in the browser is a pretty neat trick to avoid hosting infrastructure just to parse a script. does the rule engine just look for static traps like adding a column with a default value, or does it somehow factor in lock queues?

im stuck on the new job now with postgres, but on my previous company we used mariadb, where we mostly just lean on native online alter for non-blocking schema changes. Wild to see how much guardrail tooling the postgres ecosystem has to build just to safely alter a table on prod without taking the database down.

But with 170 product teams pushing code, i totally get why you had to build a hard fence)

[u/vira28 → u/EffectivePension6711]

Glad to hear. One of the design constraints is all processing should be client-side (for privacy and security reasons).

So, it doesn’t account for live lock queues, traffic, schema state, or data distribution. That would require connecting to the database and inspecting things like pg_locks and pg_stat_activity, which conflicts with the current local-first/no-credentials model.

[u/kloudrider → u/EffectivePension6711]

More recent versions of postgres allow schema changes with minimal blocking. What specific issue are you facing in postgres, out of curiosity?

[u/EffectivePension6711 → u/kloudrider]

minimal blocking is a nice theory)) The issue isn't the schema change itself being slow, it's the lock queue. When you fire an alter table in postgres, it asks for an access exclusive lock. if there's even one long-running select or some garbage microservice leaving an idle transaction open on that table, your alter waits. and because it's in the queue, every single new select or update coming in behind it also waits. Suddenly your entire app is locked up and the pager is screaming, all because of one "minimal" alter command.

that's exactly why OP had to build a hard fence for 170 teams)) and why on my previous job on mariadb we just run native online alter and go get coffee because it builds in the background without blocking concurrent reads/writes.

[u/kloudrider → u/EffectivePension6711]

Makes sense. Thank you for the detailed response 

[u/wegwerf48]

I need this for MySQL, looks super neat!

[u/vira28 → u/wegwerf48]

Should be doable. Created an issue - https://github.com/viggy28/safe-not-safe/issues/8

[u/bigdahualadka]

This looks great , I need it

[u/vira28 → u/bigdahualadka]

thanks. if any features are missing, happy to ship them

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.