How do you guys handle db schema migrations? I know there's linters to prevent backwards incompatible changes and you can adopt the Expand and contract patterns. Two concerns that come to mind: 1. Do you enforce it and have the CI fail if there's a backwards incompatible change, also any tools you recommend for this? 2. How do you handle new indices? On small tables new indices aren't a problem but on big tables it locks it so we manually do a concurrent index creation. Does this just mean we always have to look at migrations for new indices to be prepared to manually make concurrent indexes?

It’s been a while since I have realized a project that is not “build an IDP” but back in the day we used flyway.io - they have solutions in their enterprise edition that would help you to solve those things in any way. I have also heard very positive things about https://schemahero.io/ but never used it myself at project scale - so please take this with a pinch of salt. Generally speaking, I have always tried to make the topic “schema migration” a project-level problem where the software itself executes the migration at startup time. Breaking changes as part of a release on a substantial DB usually means that there is a “back only through recovery and downtime” event, which no CI can really judge impact-wise.