Duplicate indexes present on PostgreSQL

[matrixbot]

This issue was originally created by @Omar007 at https://github.com/matrix-org/dendrite/issues/3389.

Background information

• Dendrite version or git SHA: v0.13.7
• SQLite3 or Postgres?: Postgres, postgres:15-alpine
• Running in Docker?: Yes, ghcr.io/matrix-org/dendrite-monolith:v0.13.7

Description

• What is the problem: duplicate indexes that are not needed exist in the dendrite database when deployed on PostgreSQL.
• Who is affected: as far as I can tell it affects everyone using PostgreSQL. I don't know if a similar problem exists for SQLite.
• How is this bug manifesting: it affects database performance negatively.
• When did this first appear: probably during a migration/update where the new/extra indexes where added. I don't know which/when.

Steps to reproduce

• Have dendrite deployed on PostgreSQL
• Analyze the database (an easy way for this specific case would be to use something like pgHero)
• Find that certain indexes encompass others

Indexes in question:	Table	Affected Columns	Index 1	Index 2
federationsender_queue_edus	json_nid	federationsender_queue_edus_nid_idx	federationsender_queue_edus_json_nid_idx
federationsender_queue_pdus	json_nid	federationsender_queue_pdus_json_nid_idx	federationsender_queue_pdus_pdus_json_nid_idx
federationsender_relay_servers	server_name	federationsender_relay_servers_server_name_idx	federationsender_relay_server_server_name_relay_server_name_key
keyserver_one_time_keys	user_id, device_id	keyserver_one_time_keys_idx	keyserver_one_time_keys_unique
syncapi_output_room_events	room_id	syncapi_output_room_events_room_id_idx	syncapi_output_room_events_recent_events_idx
syncapi_peeks	room_id	syncapi_peeks_room_id_idx	syncapi_peeks_room_id_user_id_device_id_key
syncapi_presence	user_id	syncapi_presence_user_id	presence_presences_unique
syncapi_receipts	room_id	syncapi_receipts_room_id	syncapi_receipts_unique
userapi_pushers	app_id, pushkey	userapi_pusher_app_id_pushkey_idx	userapi_pusher_app_id_pushkey_localpart_idx

[matrixbot]

This comment was originally posted by @Omar007 at https://github.com/matrix-org/dendrite/issues/3389#issuecomment-2178553762.

Here is the same result/information from a pgHero deployment: