News for hackers. Stuff that matters.
Re:Odyssey – Scalable PostgreSQL connection pooler by nh2 on Wednesday May 30, 2018 @11:43AM (#17188620)
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 by doh on Wednesday May 30, 2018 @11:55AM (#17188758)
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 by sudhirj on Wednesday May 30, 2018 @02:12PM (#17190079)
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 by needusername on Wednesday May 30, 2018 @02:49PM (#17190485)
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 by sudhirj on Wednesday May 30, 2018 @03:20PM (#17190807)
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 by needusername on Thursday May 31, 2018 @12:24AM (#17193937)
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 by sudhirj on Thursday May 31, 2018 @01:24AM (#17194159)
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.
Re:Odyssey – Scalable PostgreSQL connection pooler by needusername on Friday June 01, 2018 @01:14AM (#17202772)
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.
Re:Odyssey – Scalable PostgreSQL connection pooler by craigkerstiens on Wednesday May 30, 2018 @12:10PM (#17188910)
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...
Re:Odyssey – Scalable PostgreSQL connection pooler by stingraycharles on Wednesday May 30, 2018 @11:54AM (#17188752)
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 by spullara on Wednesday May 30, 2018 @12:39PM (#17189181)
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.
Re:Odyssey – Scalable PostgreSQL connection pooler by foobarbazetc on Wednesday May 30, 2018 @02:08PM (#17190040)
Yes, because limiting connections by putting them in a queue is how you maintain good response time.