Skip to content

authz: remove upsert for updating user pending permissions

Administratorrequested to merge
jc/perms-reduce-upserts into main

Created by: unknwon

We used to rely on ON CONFLICT ON CONSTRAINT to do upsert when we do batch update on user_pending_permissions table. However, there are reasons why the approach in this PR is better (use update not upsert):

  1. The primary key (id) of user_pending_permissions table is increased rapidly every time hits ON CONFLICT ON CONSTRAINT for no obvious benefit. (Have seen a customer with few thousand rows but MAX(id) >80,000,000).
  2. At the moment of "upsert", we already have ids on hand, could have just used it to directly update rows with these ids as they are primary keys.

This PR does not solve 1. entirely but at least slows down id increment pace by half. Another half is caused by:

https://github.com/sourcegraph/sourcegraph/blob/e0145db0159fad19a1f48735a27986e502cbf3c9/enterprise/internal/db/perms_store.go#L598-L599

Merge request reports