Just curious to see if anyone knows how it compares to using Apache Drill as an external scalable process to Postgres Jdbc store?
Odyssey: Scalable PostgreSQL Connection Pooler
21–30 of 54 posts
Re: Odyssey: Scalable PostgreSQL Connection Pooler
#22Curious 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.
There 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…
Can 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.
Re: Odyssey: Scalable PostgreSQL Connection Pooler
#23If we are able to control/modify the protocol at both ends, not just in the middle, would this still be the optimal solution?
I've been thinking for a while that connection-oriented DBMS protocols seem like a good candidate for being carried as flows over a single-socket multiplexed protocol, like HTTP/2-over-TCP is today. (I say "over TCP" because connections between app servers and databases are long-lived and stable, so there isn't really the same advantage in replacing TCP with something like QUIC there.)
Also, such a protocol, by removing the direct "connection = session" association, would remove the ability of DBMSes to key their session state by socket ID, and force them to refactor to more explicit session IDs. This would mean that there'd be an opportunity to introduce another intermediate abstraction layer during the refactoring, keeping "session" separate from "flow", such that the DBMS could have multiple concurrent MVCC transactions/worldstates as individual flows, all referencing the same ancestor MVCC worldstate (with the same config vars set, temporary objects created + populated, etc.) as an explicit stateful session that the flow would be "opened against." Which would be pretty awesome, and would also benefit non-socket-multiplexed flows (e.g. pgbouncer's per-transaction routing mode would no longer have non-SQL-conformant semantics.)
Re: Odyssey: Scalable PostgreSQL Connection Pooler
#24Curious 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.
The "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.
Re: Odyssey: Scalable PostgreSQL Connection Pooler
#25Scalable in which sense?
Assuming that this is something like pgbouncer or pgpool that sit between the client and the database, and that you have a limit of connections with the database as well as the number of client connections you can keep, what value does this adds compared with those other (more mature, battle tested, included in major distros) projects?
Re: Odyssey: Scalable PostgreSQL Connection Pooler
#26Curious 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.
There 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…
Without 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.
Re: Odyssey: Scalable PostgreSQL Connection Pooler
#27Looks cool. Maybe I'm missing something though, but it's not really clear how you'd set this up? Is there a quick start or some examples of configs for different situations?
Yeah there’s detailed info on the contents of the config file, but not where to put said file, or how to tell the binary where to find and reference it. This looks like an exciting project that needs more externally-facing docs. A lot of knowledge is assumed at the moment.
Re: Odyssey: Scalable PostgreSQL Connection Pooler
#28If we are able to control/modify the protocol at both ends, not just in the middle, would this still be the optimal solution? I've been thinking for a while that connection-oriented DBMS protocols seem like a good candidate for being carried as flows over a single-socket multiplexed protocol, like HTTP/2-over-TCP is today. (I say "over TCP" because connections between app servers and databases are long-lived and stab…
HTTP2 could be great, but how would you implement this with Postgres, which uses one process per connection? You'd have to do all the multiplexing in a single server process, but you'd still have to have one process per underlying session, I think, and so you get the same problem with per-connection overhead that a pool tries to solve.
Re: Odyssey: Scalable PostgreSQL Connection Pooler
#29Curious 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.
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 o…
> 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.
Re: Odyssey: Scalable PostgreSQL Connection Pooler
#30If we are able to control/modify the protocol at both ends, not just in the middle, would this still be the optimal solution? I've been thinking for a while that connection-oriented DBMS protocols seem like a good candidate for being carried as flows over a single-socket multiplexed protocol, like HTTP/2-over-TCP is today. (I say "over TCP" because connections between app servers and databases are long-lived and stab…
HTTP2 could be great, but how would you implement this with Postgres, which uses one process per connection? You'd have to do all the multiplexing in a single server process, but you'd still have to have one process per underlying session, I think, and so you get the same problem with per-connection overhead that a pool tries to solve.
One of the advantages of a multiplexed protocol, is that it would reduce the need for each client to keep around a pool of N open+idle connections, just in case the client needs to abruptly make a low-latency query on a new session in parallel to its existing ones.
Part of the reason abrupt scheduling of low-latency parallel queries requires clients to keep a pool of open+idle connections, is that the PG backend takes a moment to fork(2) out a process to serve your connection, and nothing about multiplexing eliminates that cost; but another part of the reason, is that it takes the TCP socket a moment to establish, and takes the TCP window even longer than a moment to scale. (And, intriguingly, open+idle separate-TCP-socket flows scale their TCP windows down while idle, so current connection-pooling strategies aren't even getting 100% of the possible benefit.)
Decreasing the costs of flow establishment would decrease the number of open+idle flows clients keep around, which means the backend host would be free to do other things with its freed-up resources.
In combination with the fact that sessions would be "grouped" to a connection, such that long-term metrics could be captured for the behavior of a client (= one connection) as a whole, you could design an alternative "elastic scheduler" for session backends, without needing to change the forking model. E.g. keep a number of prefork idle backend processes around, associated with each connection, where the size of the prefork pool varies dynamically per connection with the observed velocity of the client's opening of new sessions on that connection. (So, for a client that does everything under one session, you'd have zero observed session-open events per unit time, and thus have zero prefork processes waiting around for that client.)