3 ms·

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 f

by takeda 7y ago

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/brettwooldridge/HikariCP/wiki/About-Pool-Sizing https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-...

evanelias 7y ago
> 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.

by takeda 7y ago

evanelias 7y ago

> 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.