On Fri, Feb 9, 2018 at 4:14 PM, Shay Rojansky <roji@roji.org> wrote:
Am a bit late to this thread, sorry if I'm slightly rehashing things. I'd like to go back to the basic on this.
Unless I'm mistaken, at least in the Java and .NET world, clients are almost always expected to have their own connection pooling, either implemented inside the driver (ADO.NET model) or as a separate modular component (JDBC). This approach has a few performance advantages:
1. "Opening" a new pooled connection is virtually free - no TCP connection needs to be opened, no I/O, no startup packet, nothing (only a tiny bit of synchronization).
2. Important client state can be associated to physical connections. For example, prepared statements can be tracked on the physical connection, and persisted when the connection is returned to the pool. The next time the physical connection is returned from the pool, if the user tries to server-prepare a statement, we can check on the connection if it has already been prepared in a "previous lifetime", and if so, no need to prepare again. This is vital for scenarios with short-lived (pooled) connections, such as web. Npgsql does this.
Regarding the problem of idle connections being kept open by clients, I'd argue it's a client-side problem. If the client is using a connection pool, the pool should be configurable to close idle connections after a certain time (I think this is relatively standard behavior). If the client isn't using a pool, it seems to be the application's responsibility to release connections when they're no longer needed.
The one drawback is that the pooling is application-specific, so it can't be shared by multiple applications/hosts. So in some scenarios it may make sense to use both client pooling and proxy/server pooling.
To sum it up, I would argue that connection pooling should first and foremost be considered as a client feature, rather than a proxy feature (pgpool) or server feature (the PostgreSQL pooling being discussed here). This isn't to say server-side pooling has no value though.
Recently, I did a large amount of parallel data processing where the results were stored in PG. I had about 1000 workers each with their own PG connection. As you pointed out, application pooling doesn't make sense in this scenario. I tried pgpool and pgbouncer, and both ended up as the bottleneck. Overall throughput was not great but it was highest without a pooler. That aligns with Konstantin's benchmarks too. As far as I know, server pooling is the only solution to increase throughput, without upgrading hardware, for this use case.