> Oltp databases typically cannot process more than 5 x cpu cores without having to time-slice among cpus
I think this is entirely wrong in reality. In most read-mostly OLTP workloads, due to client<->server latency and application processing times, my experience as well as measurements show that at that connection count the machine will not be utilized sufficiently.
5x may be around the peak throughput when a benchmark client runs on the same system and connects via unix socket or localhost tcp. But even just benchmarking over a fast local (10GBe, <10m cables, one switch) connection moves peak throughput quite a bit higher than that.
Few real world scenarios have clients sending queries back-to-back without any gaps in between. They have to process the results e.g. go through some template engine, shuffle the data to the web server.
I think for OLTP - and many other workloads - any guidance based on CPU cores is going to be so off for 90% of workloads to be more misleading than useful. The request patterns and thus bottlenecks vary far too much.
For write heavy OLTP workloads on medium-high latency storage (cheaper SSDs, all network block stores, spinning disks) a larger number of connections also can actually be good from a cost perspective - due to group commit fewer iops will be needed than when the same workload were split over two instances.
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.
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)...
> 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.
I agree. I normally recommend multiplying the number of cores by a 2-5x factor, and then dividing all that by a subjective "% utilization of the connection", which is obviously a value lower than 1 and results in an increased value for max_connections.
This % of utilization accounts for factors like the session not sending back-to-back queries, the io_wait on the server, which is turn potentially derived by the speed of your I/O subsystem, etc.
However, I'd also advise to only look at the TPS peak as the optimal point. That is indeed typically achieved at high connection numbers. Postgres is reasonably good at managing large number of connections, in terms of throughput. However, latency may go to the roof.
So to find the peak more latency than tps needs to be considered IMO (or, obviously, both).
I think this is entirely wrong in reality. In most read-mostly OLTP workloads, due to client<->server latency and application processing times, my experience as well as measurements show that at that connection count the machine will not be utilized sufficiently.
5x may be around the peak throughput when a benchmark client runs on the same system and connects via unix socket or localhost tcp. But even just benchmarking over a fast local (10GBe, <10m cables, one switch) connection moves peak throughput quite a bit higher than that.
Few real world scenarios have clients sending queries back-to-back without any gaps in between. They have to process the results e.g. go through some template engine, shuffle the data to the web server.
I think for OLTP - and many other workloads - any guidance based on CPU cores is going to be so off for 90% of workloads to be more misleading than useful. The request patterns and thus bottlenecks vary far too much.
For write heavy OLTP workloads on medium-high latency storage (cheaper SSDs, all network block stores, spinning disks) a larger number of connections also can actually be good from a cost perspective - due to group commit fewer iops will be needed than when the same workload were split over two instances.