It's difficult to maintain schema upgrade compatibility across large version gaps because of background updates

I'm sure I've mentioned this before, but not finding a ticket about it. Anyway..

The way we do background updates and foreground updates (of the database schema) makes it hard for a developer to get a good understanding of what state the database could be in at a given point.

We also don't have a way of permutation testing the different orders that schema upgrades could be installed in and checking that all of them produce a sensible result. (Though this seems like it is hard without having some representative 'fake data' anyway.)

It also takes several schema versions to achieve multi-step database refactorings, which causes them to get forgotten about and not happen.

Finally, the `SCHEMA_VERSION` / `SCHEMA_COMPAT_VERSION` is both simple and a headmess at the same time. I'm not sure anyone is particularly confident in making a multi-step migration plan without thoroughly checking it over.

I don't have a fully specific idea on how to make this better, but one thought that occurs to me is that we could treat background and foreground updates less differently.

The difference between a foreground update and a background update is that the latter is not required for Synapse to start up. (And additionally, it can run out of order with foreground updates, though this property is not entirely desirable)

It would be nice to be able to convert a background update to a foreground one with minimal effort (perhaps along the lines of removing the 'I can run in the background' flag on it)

---

A separate thought: we should at least have CI that upgrades an old database and check that it's at least possible. This is currently tricky to do because we delete old full schemas, but it's something that could be achieved by checking them out in CI, I guess.

No files, tests, or concrete entry points are named. Start by mapping Synapse's background and foreground schema update paths and the handling of SCHEMA_VERSION and SCHEMA_COMPAT_VERSION; define the desired migration model before implementation. Done would require an agreed approach for multi-step upgrades and, if pursued, CI coverage that upgrades an old database.

Written by the indexing model from the issue text.