3 ms·

> 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. That's a bit

> 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. That's a bit apples and oranges though. The main use-case for that AWS MySQL proxy is high-concurrency Lambda/serverless applications, which conceptually have no other possible means of persistent connections or application-side connection pooling. MySQL's thread-per-conn model tends to scale well up to several thousand concurrent connections, which means a proxy is only literally essential for more niche situations -- for example, large-scale monolithic environments that need an insane connection count; large-scale sharded environments where it's impractical for each app server to maintain a conn pool to each shard; cross-region SSL connections where a reconnect-each-request model introduces too much latency. That said, a really solid multi-purpose proxy such as ProxySQL provides other powerful benefits, such as query routing, query rewriting, stale replica read avoidance (read-after-write consistency), etc.

takeda 7y ago
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-Sizing 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?v=xNDnVOCdvQ0 https://www.youtube.com/watch?v=xNDnVOCdvQ0
- evanelias 7y ago
  > if you have enough RAM That's the key difference here, combined with the observation that for user-facing web applications, a decent portion of direct DB connections (i.e. without a multiplexing proxy or conn pool) tend to be idle at any given time. Assuming good configuration of session-level buffer sizes, MySQL can happily have several thousand idle connections at any given time, with no detrimental performance penalty and without requiring a massive amount of RAM. It's been a while since I've examined Postgres under similar conditions, but previously this certainly was not the case there.

takeda 7y ago

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-Sizing 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?v=xNDnVOCdvQ0 https://www.youtube.com/watch?v=xNDnVOCdvQ0

evanelias 7y ago
> if you have enough RAM That's the key difference here, combined with the observation that for user-facing web applications, a decent portion of direct DB connections (i.e. without a multiplexing proxy or conn pool) tend to be idle at any given time. Assuming good configuration of session-level buffer sizes, MySQL can happily have several thousand idle connections at any given time, with no detrimental performance penalty and without requiring a massive amount of RAM. It's been a while since I've examined Postgres under similar conditions, but previously this certainly was not the case there.