HikariCP

A larger pool actually makes the number worse because it adds more contention. I tested it with 64 and all the results were worse. The last example which was 348 TPS drops to 164 TPS! Tangentially I also highly recommend this article on pool sizing. https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-...

> are they using a connection pooler We use Hikari [1] an in-process connection pooler. We didn't opt for pgbouncer at al, because we didn't want to add the extra infra yet. > since what they did in code can be natively done with PgBouncer, PgCat, et al. Can you point me to a reference I could look at, about doing a major version upgrade with PgBouncer et al? My understanding is that we would still need to write a script to switch masters, similar to what we wrote. > The big bottleneck is all the active connections The active connections we were referring too were websocket connections; we haven't had problems with PG connections. Right now the algorithm we use to find affected queries and notify websockets starts to falter when the number of active websocket connections on one machine get too high. We're working on improving it in the co

Looks like HikariCP is also awaiting fixes for this https://github.com/brettwooldridge/HikariCP/pull/2055

You reminded me of the HikariCP library and it’s documentation: Clear and simple with references to other libraries trying to accomplish the same thing. It is not in the clojure space though. https://github.com/brettwooldridge/HikariCP

Related, about sizing DB connection (counterintuitively): https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-...

If the API takes a cursor and a number of items to fetch, the idea of a "page" or how large it is exists entirely on the client. You can fetch 40 results in one query and say it corresponds to the next four "pages", if you're configured to show ten items per page It seems worth noting a high number of concurrent queries to the same database shard as part of the same overall page load can be very wasteful of CPU as database load increase, due to the cost of context switching. https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-... dives into that.

PostgreSQL also can also handle thousands of connections (if you have enough RAM) the problem is that it won't be as performant, due to how concurrency works. You get increased performance until a certain point (typically 2*number of true cores + more if database waits for disk), after that your performance starts degrading due to overhead. That's how time slicing works. Thread-per-conn can make the overhead smaller but it is still there. Aurora PostgreSQL in AWS has threaded connection pool, but you still get a better performance if you place pgbouncer in front of it and reduce number of connections. HikariCP[1] talks about it and is database independent. [1] https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-... Edit: looks like the video in the article is no longer there, but found one on youtube: https://www.youtube.com/watch

The reason for it is because PostgreSQL wants to concentrate on correctness and do one thing and do it well. They will add a feature if there is a good reason for it and they find an elegant solution. Having said that pooler is needed in all databases not just PostgreSQL. An evidence for it is just recently released MySQL proxy service in AWS. Here's[1] another database independent pool sizing guide with demo video of Oracle. You are aware of pgbouncer mainly because of its feature which is transaction pooling, where each transaction is treated independently, it allows you to have thousands connections open, at the cost of not being allowed to use any session specific features of postgresql. This mode is really good for web applications and can work well even if your pool on application side is misconfigured. [1] https://github.com/brettw

> This article would be better if it was called "connection pool sizing for MySQL" For the curious, HikariCP provides suggested JDBC config for MySQL[1] as well. [1] https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Confi...

Good pre-reading to this article is Brett Wooldridge's (of HikariCP) article on connection pool sizing: https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-... An important consideration is that increasing the pool size many times above the number of cores is very often counterproductive in the face of decreasing IO latency. Also, there may often not be justification for anything other than a constant-sized pool.

Comment from HikariCP: https://github.com/brettwooldridge/HikariCP/issues/1040#issu...