Database hardening on common pitfall

We are about to release a new LTS version, and our users with the biggest and oldest database will run the migrations. The few corner-cases that we saw on community might be amplified & more users are going soon to be impacted. We provided some workaround that might be acceptable for a small dataset, but not for huge ones.

The goal of this task is to have a look at the LTS-to-LTS migrations, and see if we can't fix proactively some of the common pitfalls we already know can affect us.

Since the last LTS, we had several kinds of issues with database migrations:

• Index deletion
  • Our framework stops the migration when we try to delete an index that is missing. We could improve this, by searching on table metadata (if the index name is not the one expected), and allow the migration to continue if everything is fine (=database is already in the state where the index is not here)
• Duplicate cleanup before adding a new index with a unique constraint
  • Right now we rely on "being careful" when designed migrations. Experience shows that tables can contain unexpected duplicates, and we don't always check for them before adding a new index. Remediation: Make the action of removing duplicates before adding a new index part of our framework, so that you can't forget it
• Orphan cleanup
  • We often forget about potential orphans rows when designing migrations. Let's have a look and see if unexpected orphans on tables could break the upgrade path