7 ms·

Odyssey – Scalable PostgreSQL connection pooler

203 points by pmwkaa 8y ago

LoSboccacc 8y ago
> Advanced transactional pooling > Odyssey tracks current transaction state and in case of unexpected client disconnection can emit automatic Cancel connection and do Rollback that's my biggest issue with pgbouncer, is there a docker image for it?
- pbarnes_1 8y ago
  But... if you never commit the transaction in pgbouncer you don't need to roll it back. It'll just auto-rollback on server connection drop.
- polthrowaway 8y ago
  is this a problem with pg bouncer? pg bouncer supports transactional pooling which is meant to only return connections back into the pool when they are not in a transaction. so seeing as it keeps tracking of the transaction status it seems pretty crazy that it would put a connection back into the pool that has an open transaction. i tested this on my local machine and if i drop a connection while it is inside a transaction it closes the server connection. client close: 2018-05-30 15:28:42.068 21702 LOG C-0x7f9728816a10: DB/USER@[::1]:64342 closing because: client close request (age=85) 2018-05-30 15:28:42.068 21702 LOG S-0x7f972980a190: DB/USER@127.0.0.1:5432 closing because: unclean server (age=85) client unexpected death: 2018-05-30 15:33:57.197 21702 LOG C-0x7f9728816a10: DB/USER@[::1]:64376 closing because: client unexpected eof (age=15) 2018-05-30 15:33:57.198 21702 LOG S-0x7f972980a190: DB/USER@127.0.0.1:5432 closing because: unclean server (age=10) i guess it sucks that it doesn't reuse the connection but presumably this shouldn't happen that often that it would actually be a problem.
  - cpburns2009 8y ago
    Do you know how pgbouncer handles an unexpected client disconnect when using session pooling? Using pgbouncer has been on my to-do list for a while and the comment by LoSboccacc gives me some concern though it's vague.
  - LoSboccacc 8y ago
    I’m unsure on the specifics and it might very well be an interaction between jdbc or something, what I know is that if I change the connection reuse settings from session to transaction I get leakage and eventual exhaustion, so currently I’ve a pgbouncer in session mode on every node and connect each to the backend. That uses a little more connection on the server but for now is not critical so I haven’t investigated in deep and if easier I’d just hop to an equivalent because we’re really short on hands right now.
    - foobarbazetc 8y ago
      You need to make sure you turn off prepared statements if you want to use pgbouncer in transaction mode with pgjdbc. FWIW we use it in exactly that way to serve many thousands of rps with no issues.
grillorafael 8y ago
Would be interesting to have a side by side comparison with PgBouncer
- misterbowfinger 8y ago
  agreed, what's the difference?
  - sudhirj 8y ago
    This is multicore and new hotness, think pgBouncer is single core and older than me. Glibness aside, this has a few more interesting options, like configuration at a user-db level.
kodablah 8y ago
I often wonder why connections aren't made more lightweight in Postgres, or if there was an option to steal a connection and have a "RESET" command that destroyed all state. In my Postgres library, I have to keep state information too just so I can "reset" a connection. Also maybe the protocol can add a (optionally client supported) PING to check for socket death to know if a connection is stealable.
- jakobegger 8y ago
  Is there any connection state that is not stored in pg_settings? You can check that table to see what settings were overridden in the session.
  - kodablah 8y ago
    I believe what state the protocol is at and things like whether its still has rows in the buffer to be flushed on a query is not present there. But I'm not exactly sure. I'd love to be wrong.
- koolba 8y ago
  That already exists via the "DISCARD" command: https://www.postgresql.org/docs/current/static/sql-discard.html https://www.postgresql.org/docs/current/static/sql-discard.h...
  - kodablah 8y ago
    I should have clarified, I meant at the protocol level. It's basically a state machine, and I want a "break" to reset the state machine. This would flush remaining query results, close named prepared statements, rollback any in-process transactions, and put the state back at ready-for-query. In the meantime, those with connections pools (this lib, my client-side lib, etc) have to keep this info (often except prepared statements which is the caller's responsibility to close).
    - michelpp 8y ago
doh 8y ago
This looks pretty interesting. Will definitely spend some time testing it. Shameless plug. We have recently forked pgbouncer to add multicore support[0]. We are running in production for couple of weeks and the performance is great. Our design is very straightforward. Instead of touching the current code, we've extended it by a manager, that spins workers, which are essentially forks of pgbouncer itself (one per core, or whatever you specify in the settings), and then distributes the connections between the clients and the workers. So if you decide not to use the multiprocessing part, you can just turn it off and you will be running the same old pgbouncer you are used to. It also allows for the code to be merged to the original code base without any significant changes. [0] https://github.com/pexeso/pgbouncer-smp https://github.com/pexeso/pgbouncer-smp
- cpburns2009 8y ago
  I haven't used pgbouncer before but I plan on using either it or an alternative such as Odyssey in the future. What is the use case for multicore support? Is it many short lived connections, large result sets, or something else?
  - doh 8y ago
    At a certain scale the pooling becomes the bottleneck. PGB has to keep state of the connection and manage it’s life time. All this currently happens on a single core. So it doesn’t help you to tune postgres itself if PGB doesn’t keep up. Many solve this by running multiple instances of PGB (usually each on a dedicated processor) and use some kind of load balancing (haproxy, DNS, ...) to balance the connections. This fork removes the need for the load balancing as it does it out of the box. BTW this is only an issue if you’ve many connections to Postgres. We have thousands servers[0] connecting in and also run a citus[1] where the queries are distributed to many workers (with addition of citus MX that now allows each server to behave as a coordinator)[2]. At a small scale you are fine with the default postgres though. [0] https://cloud.google.com/customers/pex/ https://cloud.google.com/customers/pex/ [1] https://www.citusdata.com/customers/pex https://www.citusdata.com/customers/pex [2] https://www.citusdata.com/blog/2016/09/22/announcing-citus-mx/ https://www.citusdata.com/blog/2016/09/22/announcing-citus-m...
nh2 8y ago
Out of curiosity, is there a point in using a connection pooler if your application does not follow the PHP approach to things? That is, if you don't create a new DB connection for each HTTP request, but instead create one (or a few) connections at webserver startup time, which can serve all coming requests?
- stingraycharles 8y ago
  If you have a lot of read replicas, these poolers can be effective as a proxy / gateway in front of your read cluster.
- doh 8y ago
  Pooling on a client side is becoming more and more a standard in many new libraries across all languages. You should always use client pooling, at least in stable code/components (there is no need to go through it if you are hacking together a script). However client pooling can only optimize the single client that it runs on. Server side pooling allows it to optimize all the connections. If you are running a small deployment with couple of clients, then you truly don't need to use any server side pooling. Not that you won't benefit from it, but it may be a bit more hassle than needed.
- craigkerstiens 8y ago
  Yes, especially with Postgres. Within Postgres an idle connection still creates overhead both in terms of contention as well as resource consumption. Each connection you make even if it's not doing anything can consume around 10MB of memory from your database. A DB side pooler can help reduce that overhead by allowing only your active connections through. You can get a better idea of the details and how to monitor idle vs. active connections in this post - https://www.citusdata.com/blog/2017/05/10/scaling-connections-in-postgres/ https://www.citusdata.com/blog/2017/05/10/scaling-connection...
- spullara 8y ago
  There is another case where you have so many stateless servers that an individual database shard can't handle the number of connections. For example, if you have 1000 frontends that all need to talk to a sharded database and you only want to support 100 connections per shard you need to put concentrators inbetween the frontend tier and the database to reduce the number of connections.
arvidkahl 8y ago
I have been looking into this (and pgpool2 and pgbouncer) and what I found most suprising was both the lack of workable Docker images and any hint of a SaaS solution for this problem. Connection Pooling as a Service, why does this not exist? What factors could cause this to be a bad idea? Need for proximity? Network speeds? Security?
- zrail 8y ago
  Heroku sort of offers this, in so far as they will manage a pgbouncer instance in front of your Heroku Postgres database. It’s not generalized.
- graystevens 8y ago
  Latency would be the big issue here. You’d likely have to spin up instances in lots of key data centres to try and combat this... Each AWS location, each Google Cloud location.. and that’s not considering those that are colocating their own kit. Interesting idea though, but you’d end up having to bundle it with DBaaS, at which point you’ve got the pressure and stress of having to look after everyone else’s data.
  - foobarbazetc 8y ago
    It’s really not worth doing this because the back and forth latency of SQL is much worse than proxying a HTTP/RPC request to the app server sitting next to the DB and getting the final result back.
- deleted 8y ago
  [deleted]
- mmt 8y ago
  > What factors could cause this to be a bad idea? Need for proximity? Network speeds? Security? In short, yes to all. Specifically, fallacy [1] numbers 1, 2, 3, 4, and 7. Maybe number 5. None of those are necessarily insurmountable. However, given how relatively lightweight a connection pooler is to operate, especially compared with Postgres itself, it doesn't seem like an attractive target to "outsource". [1] https://en.wikipedia.org/wiki/Fallacies_of_distributed_computing https://en.wikipedia.org/wiki/Fallacies_of_distributed_compu...
igammarays 8y ago
Noob question: are we supposed to run a connection pooler on each backend webserver instance, or have every server connect to this (i.e. this is another microservice)? And no usage examples?

203 points by pmwkaa 8y ago

> Advanced transactional pooling > Odyssey tracks current transaction state and in case of unexpected client disconnection can emit automatic Cancel connection and do Rollback that's my biggest issue with pgbouncer, is there a docker image for it?

pbarnes_1 8y ago
But... if you never commit the transaction in pgbouncer you don't need to roll it back. It'll just auto-rollback on server connection drop.
polthrowaway 8y ago
is this a problem with pg bouncer? pg bouncer supports transactional pooling which is meant to only return connections back into the pool when they are not in a transaction. so seeing as it keeps tracking of the transaction status it seems pretty crazy that it would put a connection back into the pool that has an open transaction. i tested this on my local machine and if i drop a connection while it is inside a transaction it closes the server connection. client close: 2018-05-30 15:28:42.068 21702 LOG C-0x7f9728816a10: DB/USER@[::1]:64342 closing because: client close request (age=85) 2018-05-30 15:28:42.068 21702 LOG S-0x7f972980a190: DB/USER@127.0.0.1:5432 closing because: unclean server (age=85) client unexpected death: 2018-05-30 15:33:57.197 21702 LOG C-0x7f9728816a10: DB/USER@[::1]:64376 closing because: client unexpected eof (age=15) 2018-05-30 15:33:57.198 21702 LOG S-0x7f972980a190: DB/USER@127.0.0.1:5432 closing because: unclean server (age=10) i guess it sucks that it doesn't reuse the connection but presumably this shouldn't happen that often that it would actually be a problem.
- cpburns2009 8y ago
  Do you know how pgbouncer handles an unexpected client disconnect when using session pooling? Using pgbouncer has been on my to-do list for a while and the comment by LoSboccacc gives me some concern though it's vague.
- LoSboccacc 8y ago
  I’m unsure on the specifics and it might very well be an interaction between jdbc or something, what I know is that if I change the connection reuse settings from session to transaction I get leakage and eventual exhaustion, so currently I’ve a pgbouncer in session mode on every node and connect each to the backend. That uses a little more connection on the server but for now is not critical so I haven’t investigated in deep and if easier I’d just hop to an equivalent because we’re really short on hands right now.
  - foobarbazetc 8y ago
    You need to make sure you turn off prepared statements if you want to use pgbouncer in transaction mode with pgjdbc. FWIW we use it in exactly that way to serve many thousands of rps with no issues.

grillorafael 8y ago

Would be interesting to have a side by side comparison with PgBouncer

misterbowfinger 8y ago
agreed, what's the difference?
- sudhirj 8y ago
  This is multicore and new hotness, think pgBouncer is single core and older than me. Glibness aside, this has a few more interesting options, like configuration at a user-db level.

kodablah 8y ago

I often wonder why connections aren't made more lightweight in Postgres, or if there was an option to steal a connection and have a "RESET" command that destroyed all state. In my Postgres library, I have to keep state information too just so I can "reset" a connection. Also maybe the protocol can add a (optionally client supported) PING to check for socket death to know if a connection is stealable.

jakobegger 8y ago
Is there any connection state that is not stored in pg_settings? You can check that table to see what settings were overridden in the session.
- kodablah 8y ago
  I believe what state the protocol is at and things like whether its still has rows in the buffer to be flushed on a query is not present there. But I'm not exactly sure. I'd love to be wrong.
koolba 8y ago
That already exists via the "DISCARD" command: https://www.postgresql.org/docs/current/static/sql-discard.html https://www.postgresql.org/docs/current/static/sql-discard.h...
- kodablah 8y ago
  I should have clarified, I meant at the protocol level. It's basically a state machine, and I want a "break" to reset the state machine. This would flush remaining query results, close named prepared statements, rollback any in-process transactions, and put the state back at ready-for-query. In the meantime, those with connections pools (this lib, my client-side lib, etc) have to keep this info (often except prepared statements which is the caller's responsibility to close).
  - michelpp 8y ago

doh 8y ago

This looks pretty interesting. Will definitely spend some time testing it. Shameless plug. We have recently forked pgbouncer to add multicore support[0]. We are running in production for couple of weeks and the performance is great. Our design is very straightforward. Instead of touching the current code, we've extended it by a manager, that spins workers, which are essentially forks of pgbouncer itself (one per core, or whatever you specify in the settings), and then distributes the connections between the clients and the workers. So if you decide not to use the multiprocessing part, you can just turn it off and you will be running the same old pgbouncer you are used to. It also allows for the code to be merged to the original code base without any significant changes. [0] https://github.com/pexeso/pgbouncer-smp https://github.com/pexeso/pgbouncer-smp

cpburns2009 8y ago
I haven't used pgbouncer before but I plan on using either it or an alternative such as Odyssey in the future. What is the use case for multicore support? Is it many short lived connections, large result sets, or something else?
- doh 8y ago
  At a certain scale the pooling becomes the bottleneck. PGB has to keep state of the connection and manage it’s life time. All this currently happens on a single core. So it doesn’t help you to tune postgres itself if PGB doesn’t keep up. Many solve this by running multiple instances of PGB (usually each on a dedicated processor) and use some kind of load balancing (haproxy, DNS, ...) to balance the connections. This fork removes the need for the load balancing as it does it out of the box. BTW this is only an issue if you’ve many connections to Postgres. We have thousands servers[0] connecting in and also run a citus[1] where the queries are distributed to many workers (with addition of citus MX that now allows each server to behave as a coordinator)[2]. At a small scale you are fine with the default postgres though. [0] https://cloud.google.com/customers/pex/ https://cloud.google.com/customers/pex/ [1] https://www.citusdata.com/customers/pex https://www.citusdata.com/customers/pex [2] https://www.citusdata.com/blog/2016/09/22/announcing-citus-mx/ https://www.citusdata.com/blog/2016/09/22/announcing-citus-m...

nh2 8y ago

Out of curiosity, is there a point in using a connection pooler if your application does not follow the PHP approach to things? That is, if you don't create a new DB connection for each HTTP request, but instead create one (or a few) connections at webserver startup time, which can serve all coming requests?

stingraycharles 8y ago
If you have a lot of read replicas, these poolers can be effective as a proxy / gateway in front of your read cluster.
doh 8y ago
Pooling on a client side is becoming more and more a standard in many new libraries across all languages. You should always use client pooling, at least in stable code/components (there is no need to go through it if you are hacking together a script). However client pooling can only optimize the single client that it runs on. Server side pooling allows it to optimize all the connections. If you are running a small deployment with couple of clients, then you truly don't need to use any server side pooling. Not that you won't benefit from it, but it may be a bit more hassle than needed.
craigkerstiens 8y ago
Yes, especially with Postgres. Within Postgres an idle connection still creates overhead both in terms of contention as well as resource consumption. Each connection you make even if it's not doing anything can consume around 10MB of memory from your database. A DB side pooler can help reduce that overhead by allowing only your active connections through. You can get a better idea of the details and how to monitor idle vs. active connections in this post - https://www.citusdata.com/blog/2017/05/10/scaling-connections-in-postgres/ https://www.citusdata.com/blog/2017/05/10/scaling-connection...
spullara 8y ago
There is another case where you have so many stateless servers that an individual database shard can't handle the number of connections. For example, if you have 1000 frontends that all need to talk to a sharded database and you only want to support 100 connections per shard you need to put concentrators inbetween the frontend tier and the database to reduce the number of connections.

arvidkahl 8y ago

I have been looking into this (and pgpool2 and pgbouncer) and what I found most suprising was both the lack of workable Docker images and any hint of a SaaS solution for this problem. Connection Pooling as a Service, why does this not exist? What factors could cause this to be a bad idea? Need for proximity? Network speeds? Security?

zrail 8y ago
Heroku sort of offers this, in so far as they will manage a pgbouncer instance in front of your Heroku Postgres database. It’s not generalized.
graystevens 8y ago
Latency would be the big issue here. You’d likely have to spin up instances in lots of key data centres to try and combat this... Each AWS location, each Google Cloud location.. and that’s not considering those that are colocating their own kit. Interesting idea though, but you’d end up having to bundle it with DBaaS, at which point you’ve got the pressure and stress of having to look after everyone else’s data.
- foobarbazetc 8y ago
  It’s really not worth doing this because the back and forth latency of SQL is much worse than proxying a HTTP/RPC request to the app server sitting next to the DB and getting the final result back.
deleted 8y ago
[deleted]
mmt 8y ago
> What factors could cause this to be a bad idea? Need for proximity? Network speeds? Security? In short, yes to all. Specifically, fallacy [1] numbers 1, 2, 3, 4, and 7. Maybe number 5. None of those are necessarily insurmountable. However, given how relatively lightweight a connection pooler is to operate, especially compared with Postgres itself, it doesn't seem like an attractive target to "outsource". [1] https://en.wikipedia.org/wiki/Fallacies_of_distributed_computing https://en.wikipedia.org/wiki/Fallacies_of_distributed_compu...

igammarays 8y ago

Noob question: are we supposed to run a connection pooler on each backend webserver instance, or have every server connect to this (i.e. this is another microservice)? And no usage examples?