Odyssey – Scalable PostgreSQL connection pooler | Hacker News Reader

Odyssey – Scalable PostgreSQL connection pooler

github.com

203 pointspmwkaa40 comments

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

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

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.

We also did it this way before. However we paid for it in latency, and because we operate in cloud, network micro-outages. Once we moved it directly to the Postgres servers and deployed this fork, we saw pretty significant overall improvements.

We've solved this problem by running 4 instances of pgbouncer and placing them behind a load balancer.

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.

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.

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

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.

Yes, because limiting connections by putting them in a queue is how you maintain good response time.

Pooling is more a problem with larger frameworks like Rails(Ruby) where a connection is automatically checked out of the client pool for you on every request and held till the end of the request. If you're doing other work on the request, like network calls or non-DB related slow stuff, you'll quickly run out of connections.

A separate pooler makes sense here because it'll let Rails imagine it has how many ever thousands of connections it needs open, but map them to real connections only when usage actually happens.

This is easier than taking over connection management manually in the code, which the only other option.

How does this work? I always assumed that you can have only one transaction open per session/connection (yes there are subtransaction but I don't see how these could be used for multiplexing independent transaction over the same session.).

Can't multiplex transactions, but if you're doing long running work inside transactions you have bigger problems.

The Rails will checkout a connection for you, but it won't necessarily start a transaction if you don't ask it to. Think there's some magic involved there, but generally transactions don't start unless necessary or they're initiated by code.

Uh oh. I naturally assumed Rails runs every request in a transaction because that's obviously the default you want.

Not really. Though this should be trivial to do - just start and commit in the base controller life cycle hooks - I would never want or recommend it as he default. Too many things can go wrong with out of hand work, sequencing, assuming that hooks will happen when you do call save methods etc.

Why would you not have that as a default? What things can go wrong? Not having it means you have to think about transactions in every method or you accept database inconsistency.

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.

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 statements which is the caller's responsibility to close).

That is what DISCARD ALL does, it discards all session state.

I am talking about the protocol, not a query. What if the state of the connection is not even at ready-for-query state, how do I issue a query? There is no DISCARD ALL in the protocol [0]. You might have only read 3 rows and still have a thousand to flush, or you might have sent a Parse and are awaiting a ParseComplete. This is the type of thing that a Postgres connection pooler has to keep track of. Here's some of their code: https://github.com/yandex/odyssey/blob/master/sources/reset.... (note how it has to get back to ready-for-query state before rolling back).

0 - https://www.postgresql.org/docs/current/static/protocol-mess...

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

agreed, what's the difference?

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.

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

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.

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.

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.

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.

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?

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.

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.

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.

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

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?

Reply on news.ycombinator.com