We had an interesting architecture situation at work. Puppet Enterprise uses a single Postgres server. The company had moved from a recommendation of using a single PuppetDB API node (which fell over at high load) to running a PuppetDB API server on each compiler node.

That, however, came with its own set of problems. Of course you have to tune for concurrent connections as you scale wider, but there were much more serious contention issues than you'd expect, and the compilation times were terrible too. It turned out to be because those transactions locked the DB during their (synchronous) operations, and we had a globally distributed set of compilers in order to serve globally distributed traffic.

The solution ended up being to run a separate cluster of API servers in the same region as the DB. The expensive calls from the compilers to the API servers were largely async https so they didn't have to wait on the API nodes, and the API nodes could talk to the DB synchronously with low latency.

If you find yourself doing a lot of explicit transactions, it can be a sign that your schema isn't as normalized as it should be.

How does better normalization reduce the need for explicit transactions?

The need to change two separate tables atomically means that you probably have info duplicated. Also it can be non-ideal in a web backend cause it means keeping DB connections open longer.

Sorry ignore the second sentence, I meant keeps the connection checked out of the pool longer, but also it has nothing to do with normalization.

In this case that is not the problem.