6 ms·
Curious why this isn't a priority for the Postgres team. Not my area of expertise, and even I know about pgbouncer. Now another tool, so it seems like there's
by tyingq 7y ago
Curious why this isn't a priority for the Postgres team. Not my area of expertise, and even I know about pgbouncer. Now another tool, so it seems like there's demand for it.
- manigandham 7y agoMostly because it's a lot of work due to Postgres process-per-connection architecture and since these projects already exist, so dev time is better spent elsewhere for now. PG generally depends on 3rd-party tools to complete and polish the experience for those that need it.
- thatha7777 7y agoThere’s Postgres Pro Enterprise which is a commercial fork that has a connection poller feature. Back in May it seemed to outperform Odyssey, according to a benchmark by one of the Postgres Pro folks: https://github.com/postgrespro/postgresql.builtin_pool/wiki/Benchmarking-connection-poolers https://github.com/postgrespro/postgresql.builtin_pool/wiki/...
- takeda 7y agoAurora PostgreSQL also has a connection pooler, but it will still lose performance when you open too many connections. No matter what is used you still need to limit number of open connections to the database, so a pooler like pgbouncer in transaction mode which can drastically reduce them might still be needed.
- takeda 7y agoThe 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 agoPostgreSQL 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.
- craigkerstiens 7y agoThere is definitely a demand for it, there are really three problems that exist with connections in Postgres. 1. The initial time to establish a connection is higher than would be ideal 2. Each connection, even an idle one, has an overhead of roughly 10 MB. For idle connections this is pretty much wasted space and most application frameworks grab a pool of connections accumulating quite a few idle connections. 3. The max number of connections you can push the system to is somewhere in hundreds to low thousand. Yes, it is possible to push PG further but it is not trivial. But, the sad reality is it isn't a trivial quick fix. There are a lot of foundational issues with connection that have to be fixed. There are some folks and efforts in the community working towards this, and in my opinion it is the single biggest improvement that could happen in Postgres over the next 2-3 years, so hopefully it will improve directly in core over time.
- AaronFriel 7y agoCan you remove the leading spaces so that this text is readable without scrolling? Even on my 4k monitor I must scroll to read each line.
- majewsky 7y agoWithout broken formatting: > 1. The initial time to establish a connection is higher than would be ideal > 2. Each connection, even an idle one, has an overhead of roughly 10 MB. For idle connections this is pretty much wasted space and most application frameworks grab a pool of connections accumulating quite a few idle connections. > 3. The max number of connections you can push the system to is somewhere in hundreds to low thousand. Yes, it is possible to push PG further but it is not trivial.
- fdr 7y agoThe "straightforward" way to deal with this is a multi-year project. So, the reason is likely expense. The guts of the matter is that Postgres, and its extensions, relies on a number of global variables. These global variables are more akin to a thread-local variable in multi-threaded programs, since there is one copy per forked backend. More nominally global state is carefully stored in shared memory. The ramifications of changing this, e.g. to allow an execution state that can be cleanly suspended and resumed when scheduling, are rather large. Also, consider extensions, which to date could rely on their own global variables and connection termination exiting the entire process. As-is, all the poolers have to live with obscure but important abnormalities where they cannot reset the process state quite properly, and that can be quite confusing. To eliminate this behavior is very expensive.