5 ms·

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 e

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 7y ago
> 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 7y ago
  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
1996 7y ago
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.
- taffer 7y ago
  > 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 https://www.youtube.com/watch?v=xNDnVOCdvQ0
- directionless 7y ago
  It's a good route, if you're running your own. I'm mostly in clouds, and they've got lower limits
- takeda 7y ago
  connections aren't free[1] after reducing number of connections from 200 to 60 my commit throughput doubled. [1] https://brandur.org/postgres-connections https://brandur.org/postgres-connections
takeda 7y ago
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 https://brandur.org/postgres-connections

mulander 7y ago

> 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 7y ago
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

1996 7y ago

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.

taffer 7y ago
> 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 https://www.youtube.com/watch?v=xNDnVOCdvQ0
directionless 7y ago
It's a good route, if you're running your own. I'm mostly in clouds, and they've got lower limits
takeda 7y ago
connections aren't free[1] after reducing number of connections from 200 to 60 my commit throughput doubled. [1] https://brandur.org/postgres-connections https://brandur.org/postgres-connections

takeda 7y ago

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 https://brandur.org/postgres-connections