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

31–40 of 42 posts

Re: Odyssey – Scalable PostgreSQL connection pooler

#31

Post by sudhirj »

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

Re: Odyssey – Scalable PostgreSQL connection pooler

#32

Post by foobarbazetc »

graystevens wrote: post #26
arvidkahl wrote: post #22

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?

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.

Re: Odyssey – Scalable PostgreSQL connection pooler

#33

Post by sudhirj »

misterbowfinger wrote: post #5
grillorafael wrote: post #3

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.

Re: Odyssey – Scalable PostgreSQL connection pooler

#34

Post by [deleted] »

arvidkahl wrote: post #22

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?

[deleted]

Re: Odyssey – Scalable PostgreSQL connection pooler

#35

Post by needusername »

sudhirj wrote: post #31
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 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 thous…

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

Re: Odyssey – Scalable PostgreSQL connection pooler

#36

Post by sudhirj »

needusername wrote: post #35
sudhirj wrote: post #31

Earlier quoted context omitted.

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

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.

Re: Odyssey – Scalable PostgreSQL connection pooler

#38

Post by mmt »

arvidkahl wrote: post #22

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?

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

Re: Odyssey – Scalable PostgreSQL connection pooler

#39

Post by needusername »

sudhirj wrote: post #36
needusername wrote: post #35

Earlier quoted context omitted.

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.

Re: Odyssey – Scalable PostgreSQL connection pooler

#40

Post by sudhirj »

needusername wrote: post #39
sudhirj wrote: post #36

Earlier quoted context omitted.

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.
Post reply on HN