properties in models, the problem persists (of course the relation name in the error message changes to reflect the rename), so it's not like it's just a conflict with some old relations already existing in the database.

The system uses no more than NAMEDATALEN-1 bytes of an identifier;
longer names can be written in commands, but they will be truncated.
By default, NAMEDATALEN is 64 so the maximum identifier length is 63
bytes.

Odoo doesn't take this into account. It happily generates much longer identifiers, which are then truncated by PostgreSQL. If both identifiers share the same first 63 characters (which for longer identifiers is quite likely) they will be treated by PostgreSQL as the same. That means the first index will be created, but creating the second one will result in an error, since it shares an identifier that was already used (at least according to PostgreSQL).

So what is the maximum length the _name property can have while avoiding the problem? It depends on how many characters are shared between the names of two models in m2m relation, but to fully avoid identifier truncation you should never use names longer than 16 characters.

Why 16? PostgreSQL identifiers can not be longer than 63 characters. In index identifiers generated by odoo there are 15 fixed characters. That leaves us with 48 characters, which have to accommodate three repetitions of model names. This in turn leaves us with 16 characters per single model name.

Another way to work around the issue would be to manually set a short relation name via relation attribute on the Many2many field.