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.
The locks I was referring to were more around advisory locks to prevent multiple migrations from running. For the locks you mention, all migrations create some kind of table lock typically, it's up to the dev to make them as small as necessary. I don't follow your strawman.
The locks I was referring to were a direct result of many (most?) ALTER statements. Multiple clients trying to migrate isn't even in this equation. Just one client migrating can ruin your day with a single ALTER statement given a large enough database.
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.
- 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/...