Unique constraints seem to be disappearing from the database

[babolivier]

#7399 and #7416 both look like a different unique constraint suddenly disappeared from their database (respectively account_data_uniqueness and device_uniqueness).

This doesn't seem to be related to sqlite -> postgres migrations, and both these constraints are added during table creation, outside of a background update, so it's not clear to me how this could happen.

[richvdh]

I've seen this reported before (eg #7282, #7309). On investigation it usually turns out to have happened when people back up and restore their database. If you have duplicate rows due to incorrect collation settings (#6696 etc), then it will not be possible to create the index on the restored database, hence the "missing" index.

[babolivier]

Right, so it's more likely an operational/PostgreSQL issue than a Synapse one.