At Tiqets we have a different repo with SQL migrations using Flyway... ITs as simply as branching that repo, creating your migration. Getting the pipeline green and merging it back. All migrations are automatically executed on production, pre-tested. Reviewed. No DB Administrator, No deployment together with the app. It works quite well.. However, personally I really like not having to deal with schema changes. For e…
This works fine for a lot of cases, but you can run into issues on larger databases. Things like default or calculated columns and indexes can seem like innocuous SQL but end up being incredibly expensive and even acquire locks that block other transactions on bigger databases.
We have some "medium sized" Postgres databases around 1-10TB and something like adding an index to a big table (100s millions of rows) can be quite expensive resource wise
(I'd say "big" is something like 20-50TB+ and small <1TB)