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:

1. ✅ What is the exact mechanism causing the contention to affect the CI/CD endpoints the most? ANSWERED in #338865 (comment 655000965)
2. ✅ 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.

/cc @stanhu @NikolayS @cat