If you do that on a table or two with millions of rows, you've effectively locked your database from all other access, even SELECTs.
ALTER statements typically grab exclusive locks, blocking both writes and reads. Chain a couple together and you're looking at high CPU, queued queries, and an unresponsive database for hours.
Once you realize what's happening and kill the migration in a panic, it may take tens of minutes or more for the DB to revert back to its initial state, much to everyone's impatient horror.
In simple programs and databases, everything is possible and quick. Complex programs and large databases disabuse you of your naïveté with a quickness.
I love Postgres's transactional DDL, but as the article's mention of renaming columns highlights, one transaction simply isn't enough for the job.
how large are the DB migrations you've done? your experience is seemingly completely different to mine.
What's the biggest table you've ever had to modify?
Adding a column with a default is easy. What about deleting a column from a table with 10s of millions of rows? Renaming a column? Changing a column's type? Adding a CHECK constraint? Adding a NOT NULL? Adding a foreign key constraint that someone accidentally removed in the previous migration while data keeps flowing?
Those concerns aren't about your explicit advisory locks. Not by a long shot.
https://www.postgresql.org/docs/current/explicit-locking.htm...
- Start migration, create transaction and locks
- Do migration things
- Do some SELECTs, INSERTS, or w/e in your code to validate the schema matches your model.
- End transaction or rollback if failure
None of this is free, but it's by no means hard IMO. I do something similar in Go: https://github.com/candiddev/shared/blob/main/go/postgresql/...