[directionless]

Postgres generally has a fairly low maximum connections. If you're running your own servers, you can adjust this, but in the cloud you may not be able to. For example, Google CloudSQL maxes at 1000, Heroku at 500.

At that point, people usually start looking at the connection pooling tools. Depending on how much work you need from the DB, connections pools can be a win. Anyone know how connection pooling works with listeners?

A

[mulander]

> Anyone know how connection pooling works with listeners?

They don't. LISTEN is per connection and pgbouncer multiplexes many sessions onto a single one. The poller holds the connection and has no way to propagate back the notification while still maintaining multiplexed sessions as isolated.

[directionless]

Well there we go.

So while postgres makes for a pretty awesome database, and a pretty good queueing system, some folks may be seriously impacted by the max number of connections

[takeda]

The reason for low connection limit is because every connection is a separate process. There are also various structures in postgres that are frequently traversed during various operations that large number of connections affect[1]. In my company we use aurora which supposedly was also modified connection handling to use threads to handle more connections. By default I think aurora is set to 500 connections.

In our case we had about ~200 connections to database. After we placed pgbouncer in front and reduced number of connections to 60. Our commit throughput doubled.

[1] https://brandur.org/postgres-connections

[1996]

Can't say about listeners. But connection poolers introduced more problems than I cared to fix. So now I routinely use 3000 connections, and have 9000 set up for peaks. It eats a little more ram but it is more stable.

[takeda]

connections aren't free[1] after reducing number of connections from 200 to 60 my commit throughput doubled.

[1] https://brandur.org/postgres-connections

[taffer]

> It eats a little more ram but it is more stable.

It also eats a lot more cpu cycles:

https://www.youtube.com/watch?v=xNDnVOCdvQ0

[directionless]

It's a good route, if you're running your own. I'm mostly in clouds, and they've got lower limits