I was looking at my design for primary keys which is based on my own incrementing BIGINT key generator. It is designed to generate batches of 50 numbers for use on that process, but mostly it ends up generating surrogate keys in monotonic increasing order.
Therefore I am worried with inserts from lots of different users to the same table for that primary surrogate key being used, since the inserts will all be using numbers near each other at a given point in time, might there be enough gap lock contention to slow down the insert?
I have two workarounds in mind. One is to use an md5 hash of the BIGINT number as the actual primary key. This way, all concurrent inserts are likely to lock different gaps. The other solution is that even though for programming simplicity, it would be best to use the the new BIGINT given by my sequence generator as the surrogate id, instead use a primary key tuple (user_id, surrogate_int_id).
Also, if there secondary indices on the table do inserts take gap locks on all of these as well?