4 ms·

It's common to put a connection pooler such as pgbouncer in front of postgres, usually in the same datacenter/cloud region, sometimes on the same machine. In t

It's common to put a connection pooler such as pgbouncer in front of postgres, usually in the same datacenter/cloud region, sometimes on the same machine. In that case, the behavior you describe will happen between the clients and the pooler, then the pooler will mux the clients' queries onto a smaller number of connections to the database. In many configs, once all the pgbouncer -> postgres connections are used, any more incoming queries will be queued. Each time pgbouncer gets a result from postgres, it pulls another query off the queue, so if queuing is sustained you'll see back-to-back requests on the pgbouncer -> postgres connections without gaps in between. I agree there's no general CPU core guidance here. Gold standard is to representatively load test your app, safely beyond your target load, or to destruction if nobody can tell you what the target load is.

anarazel 6y ago
Pooling has a huge effect in this regard when using statement level pooling. Which isn't suitable for all that many workloads. With transaction level pooling it's still significant, but latency / processing effects become significant. And even tx pooling has a lot of issues - lack of proper prepared statements support being the biggest. The last issue really could be solved, but to my knowledge none of the common poolers do. Imo the medium-long term solution here is so separate out connection from process / thread. Neither deals well with many realistic and important workloads (with the overhead being smaller but significant with threads). It's a huge project with lots of prerequisites though (aio, threading, non recursive query execution)... Edit: s/poolling/pooling/
- andrewf 6y ago
  You're right, most of my experience here is using statement level pooling.
- eternalban 6y ago
  > Imo the medium-long term solution here is so separate out connection from process / thread. I thought process forking was a fundamental aspect of Posgres' architecture. Is it correct to read above as effectively rearchitecting Postgres?
  - anarazel 6y ago
    > I thought process forking was a fundamental aspect of Posgres' architecture. Is it correct to read above as effectively rearchitecting Postgres? Is it a small change? Certainly not. But it'd also not touch the majority of the code. There have been prototypes for just going to threading by liberally sprinkling thread-local markers around - it works, but is fairly ugly. But more incrementally working towards that is quite doable.

anarazel 6y ago

Pooling has a huge effect in this regard when using statement level pooling. Which isn't suitable for all that many workloads. With transaction level pooling it's still significant, but latency / processing effects become significant. And even tx pooling has a lot of issues - lack of proper prepared statements support being the biggest. The last issue really could be solved, but to my knowledge none of the common poolers do. Imo the medium-long term solution here is so separate out connection from process / thread. Neither deals well with many realistic and important workloads (with the overhead being smaller but significant with threads). It's a huge project with lots of prerequisites though (aio, threading, non recursive query execution)... Edit: s/poolling/pooling/

andrewf 6y ago
You're right, most of my experience here is using statement level pooling.
eternalban 6y ago
> Imo the medium-long term solution here is so separate out connection from process / thread. I thought process forking was a fundamental aspect of Posgres' architecture. Is it correct to read above as effectively rearchitecting Postgres?
- anarazel 6y ago
  > I thought process forking was a fundamental aspect of Posgres' architecture. Is it correct to read above as effectively rearchitecting Postgres? Is it a small change? Certainly not. But it'd also not touch the majority of the code. There have been prototypes for just going to threading by liberally sprinkling thread-local markers around - it works, but is fairly ugly. But more incrementally working towards that is quite doable.