
Odyssey – Scalable PostgreSQL connection pooler - pmwkaa
https://github.com/yandex/odyssey
======
doh
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
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
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-m...](https://www.citusdata.com/blog/2016/09/22/announcing-citus-mx/)

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

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

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

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

~~~
needusername
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.).

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

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

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

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

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

~~~
koolba
That already exists via the "DISCARD" command:
[https://www.postgresql.org/docs/current/static/sql-
discard.h...](https://www.postgresql.org/docs/current/static/sql-discard.html)

~~~
kodablah
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
That is what DISCARD ALL does, it discards all session state.

~~~
kodablah
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....](https://github.com/yandex/odyssey/blob/master/sources/reset.c)
(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...](https://www.postgresql.org/docs/current/static/protocol-message-
formats.html)

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

~~~
misterbowfinger
agreed, what's the difference?

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

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

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

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

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

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

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

