Consider ways to reduce lock-contention on the internal_ids table

According to our Top-50 Queries by total_time report (https://gitlab.com/gitlab-com/gl-infra/infrastructure/snippets/1880192), the number one query, at 13% of the total time, by queries sampled on the postgres primary of GitLab.com, during the sample period was:

SELECT  "internal_ids".* FROM "internal_ids" WHERE "internal_ids"."id" = ? LIMIT ? FOR UPDATE

My understanding (which could be wrong) is this is lock contention on the internal_ids table, which could be leading to performance degradation.

Several other issues have been written regarding this:

• Reduce lock contention on table internal_ids: https://gitlab.com/gitlab-com/gl-infra/infrastructure/issues/7160
• Dozens of concurrent competing "SELECT ... FOR UPDATE" for the same row https://gitlab.com/gitlab-com/gl-infra/infrastructure/issues/5164
• Retry internal_id lock instead of blocking the query https://gitlab.com/gitlab-org/gitlab-ce/issues/57004

I'm creating this issue in this tracker, so that we can track this in the Infra/Dev board: https://gitlab.com/groups/gitlab-org/-/boards/1193197

cc @abrandl