Hacker News Reader

Comment by viraptor | Hacker News Reader

> Decoupling concurrency from connections seems possible via pipelining commands over a single connection. Is it just too late for a project as mature as Postgres?

This is commonly done using http://www.pgbouncer.org/

While it's not built-in, it's a really common solution and can achieve high number of connections. It comes with some issues though (no server-side prepared statements). And yes, it's not exactly the same as pipelining on the server itself since it's still limited by the number of bouncer connections, but there's usually enough idle time to sacrifice some latency for throughput.

There's also https://www.pgpool.net/mediawiki/index.php/Main_Page but I'm not familiar with the details.

View on HN

I always see people recommending external pools like this here on HN. I'm wondering a bit about that as many frameworks/libraries that use Postgres already implement an internal connection pool. Is this recommendation generally assuming that multiple applications will access the server? Or are there other reasons to prefer an external pooler over internal pooling in your application?

Its for IMO, crap languages that don't have proper threading support. Every language with native decent threading implementation has its own connection pool in the client or as an add-on library

> Is this recommendation generally assuming that multiple applications will access the server

That's very often a reason to use external pools, yes. Or just think of a single web application that runs on multiple nodes to distribute the load on the application servers. You either carefully configure each connection pool or you use an external pooler to which each node connects to (rather than directly to the database)

Even if you configure it carefully, you have the limit of: your db connection capacity < app servers X max internal pool size.

Every time you add another instance, you're not adding a "happy path number" of connections. You're adding up to the internal max, because at some point you will get spammed with real or bot traffic.

It's easier to aggregate the connections than carefully balance that setup every time you make infra changes or scale up. And you need some headroom for "oh, this point release of the library spawns a thread now and gets its own connection pool... oops".

They serve somewhat different purposes. For example, application-internal connection pools won't shield the database server from a rapid scale-out of application instances: n * the instance count will still result in n * connections.

I've always considered the #1 purpose of internal pools to be minimising latency, by moving the database connection handshakes outwith the critical path.

>>> I'm wondering a bit about that as many frameworks/libraries that use Postgres already implement an internal connection pool.

That's precisely the problem.

The usual misconfigured python/ruby application will throw 16 connections at the database.

The model for these languages is to deploy one process per core, so there will be 8 concurrent processes running. 8 cores * 16 pool = 256 connections total.

Multiply by N parallel servers and that's even more connections.

There are issues with large amount of connections and postgresql, hence people decide to put pgbouncer in front of the database to mitigate.

> I always see people recommending external pools like this here on HN. I'm wondering a bit about that as many frameworks/libraries that use Postgres already implement an internal connection pool. Is this recommendation generally assuming that multiple applications will access the server? Or are there other reasons to prefer an external pooler over internal pooling in your application?

It's pretty common to want both, particularly for larger installations. If you have more than one or two application servers the number of connections held by the "internal" pools can become pretty large. With naive configuration you often end up with each of the internal pools being substantially over-sized, due to handling some momentary imbalance between the application servers.

In particular if your workload allows to use transaction (or even statement) level pooling, you can get very large boosts by having those two levels of pooling. The application pools ensure the connection overhead is not incurred, the external pooler ensures the total number of connections is manageable.

It is a common solution, but losing track of where connections are from can be a pretty big price to pay.

I am painfully aware of this due to a situation where a rogue machine with an out of date version of software kept making unwanted updates. It literally took months of off and on searching to locate the machine. And the most irritating thing was that Postgres internally contains all of the tools that we needed to trace where connections came from..which is how we knew that it was connecting through pgbouncer.

Dumb q: pgbouncer didn't have logs? There was no way to modify the connection or request to add identifying info? Genuinely curious...

It had logs, but they were not useful.

However reading docs, it looks like my complaint is dated. In version 1.6 (released in 2015) they added the application_name_add_host parameter which sticks the client ip and port to application_name. This allows bad queries to be tracked back to the source machine.

I wish I had had that feature available to me when I needed it...

This actually doesn't solve the underlying issue that I'm referencing. While this lets you share a pool between multiple processes it does not change the high memory requirements on Postgres to support a highly concurrent workload. One connection (10mb) still can only be doing one thing at a time. Most of the lifetime of a request the connection is idle but locked

One connection is not actually 10 MB. There is great article explaining the difference, in Linux, beetween reported memory usage and real physical actual usage. I'll edit if manage to find it.

Edit: anarazel actually posted it in its reply.

That might of been a serious problem in 1995, but certainly throwing 64-128gb of ram at a postgres box isn't that uncommon?

I'm also curious how you got 10mb per connection (if I'm reading your parenthetical correctly). I've seen a PG box with a few hundred active connections sitting happily at just a gig of ram. Obviously WHAT you do with those connections may well change that but that isn't pg-wire overhead.

It's uncommon to throw 128 GB at a postgres. Remember that the world has moved to the cloud, where you pay a hefty fee per GB.

It's entirely possible to throw gigabytes of RAM as long as the company is willing to foot the bill. You're going into thousands of dollars per month depending how big and how many replicas we're talking about.

Reply on news.ycombinator.com