Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Odyssey – Scalable PostgreSQL connection pooler

github.com

Post reply on HN
Print view View original

11–20 of 42 posts

Re: Odyssey – Scalable PostgreSQL connection pooler

#11

Post by kodablah »

jakobegger wrote: post #7
kodablah wrote: post #4

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.

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.

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.

Re: Odyssey – Scalable PostgreSQL connection pooler

#12

Post by cpburns2009 »

polthrowaway wrote: post #6
LoSboccacc wrote: post #2

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

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 trans…

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.

Re: Odyssey – Scalable PostgreSQL connection pooler

#13

Post by cpburns2009 »

doh wrote: post #9

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…

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?

Re: Odyssey – Scalable PostgreSQL connection pooler

#14

Post by doh »

cpburns2009 wrote: post #13
doh wrote: post #9

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…

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?

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/

[1] https://www.citusdata.com/customers/pex

[2] https://www.citusdata.com/blog/2016/09/22/announcing-citus-m...

Re: Odyssey – Scalable PostgreSQL connection pooler

#15

Post by nh2 »

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?

Re: Odyssey – Scalable PostgreSQL connection pooler

#16

Post by pbarnes_1 »

LoSboccacc wrote: post #2

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

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.

Re: Odyssey – Scalable PostgreSQL connection pooler

#17

Post by stingraycharles »

nh2 wrote: post #15

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?

If you have a lot of read replicas, these poolers can be effective as a proxy / gateway in front of your read cluster.

Re: Odyssey – Scalable PostgreSQL connection pooler

#18

Post by doh »

nh2 wrote: post #15

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?

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.

Re: Odyssey – Scalable PostgreSQL connection pooler

#19

Post by vageli »

doh wrote: post #14
cpburns2009 wrote: post #13

Earlier quoted context omitted.

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?

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…

Interesting to see how you guys do things. My team has taken the opposite approach, deploying pgbouncer as an ECS service with multiple containers per host, fronted by an lb.

Re: Odyssey – Scalable PostgreSQL connection pooler

#20

Post by michelpp »

kodablah wrote: post #10
koolba wrote: post #8

Earlier quoted context omitted.

That already exists via the "DISCARD" command: https://www.postgresql.org/docs/current/static/sql-discard.h...

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…

That is what DISCARD ALL does, it discards all session state.
Post reply on HN