Understand the mechanism of subtransactions contention affecting CI endpoints the most
Description
In #338346 (closed) we believe that we've managed to describe the root cause of database contention coming from the subtransactions locking.
Questions
There are questions that we need to answer:
-
✅ What is the exact mechanism causing the contention to affect the CI/CD endpoints the most? ANSWERED in #338865 (comment 655000965) -
✅ At what rate of subtransactions creation CI/CD queries will not experience locking / contention? Do we need to go down to zero? ANSWERED in #338865 (comment 655317080)
Answering these questions might help us to direct resources into work that will have the biggest impact to improve the situation.
Conclusions
We need to either path PostgreSQL or eliminate subtransaction completely.
Edited by Grzegorz Bizon