4 ms·

Great article that matches my experiences: running with max_connections=100000 is not an issue when you have more than enough ram, the problem is the idle conne

by 1996 6y ago

Great article that matches my experiences: running with max_connections=100000 is not an issue when you have more than enough ram, the problem is the idle connections that you are tempted to tolerate (after all, you've got lots of RAM, so why bother?) linearly decrease the TPS even if nothing is done pgbouncer does help, but brings in unneeded complexity. postgres 14 should offer an "alternate" port doing essentially what pgbouncer does, next to the regular port. Typical usecase: a large fleet of IOT devices with a write-only access to a central database, feeding new data every second. No visibility of the current transactions is needed.

qeternity 6y ago
If you’re handling that many connections, does pgbouncer really add that much complexity? Surely at that scale, pgbouncer is less complex than alternative scaling options (replicas, caching, etc). We run it in production and hardly consider ourselves experts in pgbouncer.
- 1996 6y ago
  Before pgbouncer, I was running with 6000 active connections, each of them doing insertions. I fail to see how replicas can help if your input comes from that many clients doing writes, each already having an internal pool. I tried various other things, like an unlogged table on the input side, but nothing helped much. I have tested with and without pgbouncer, and the case isn't as clear cut as it seems. It helps a bit, but it adds complexity. This complexity is not readily apparent, but you have to separately manage the userlist, you must not get your auth types out of sync (md5/scram), and so on... The only reason pgbouncer stays is that I don't want to mess with it again, but it's death by a thousand paper cuts, enough to complexify deployment and updates to a point where I'm thinking of calling it quit and using a different database in front of postgres.
taffer 6y ago
> Typical usecase: a large fleet of IOT devices with a write-only access to a central database, feeding new data every second. No visibility of the current transactions is needed. Can't you queue and serialize those writes in a middle layer so that you only need a small amount of connections?
- anarazel 6y ago
  Yea, normally you'd want some type of broker / middle-layer to accept the incoming data from the devices and then send it onward into the database. That broker would also authenticate / validate the devices / data etc. And it can do stuff like batch the input, for efficiency. If you actually need that many incoming connections, for now you'd probably want to use pgbouncer (or oddysey or ...) in transaction or statement pooling mode.
  - 1996 6y ago
    Indeed, that's what I currently have, but for this database I'm looking at alternatives (ex: clickhouse) that could get everything and "send it onward" on a schedule. It's just a glorified bulk insert with extra moving parts, but if it works around the issue, why not? BTW Have you played with oddysey? It doesn't seem essentially different (or better) from pgbouncer.
    - anarazel 6y ago
      > BTW Have you played with oddysey? It doesn't seem essentially different (or better) from pgbouncer. pgbouncer is single threaded, oddysey isn't.
      - 1996 6y ago
        that is enough of a reason to try it, thanks!
- skrtskrt 6y ago
  Seems like a Kafka use case.

by 1996 6y ago

qeternity 6y ago

If you’re handling that many connections, does pgbouncer really add that much complexity? Surely at that scale, pgbouncer is less complex than alternative scaling options (replicas, caching, etc). We run it in production and hardly consider ourselves experts in pgbouncer.

1996 6y ago
Before pgbouncer, I was running with 6000 active connections, each of them doing insertions. I fail to see how replicas can help if your input comes from that many clients doing writes, each already having an internal pool. I tried various other things, like an unlogged table on the input side, but nothing helped much. I have tested with and without pgbouncer, and the case isn't as clear cut as it seems. It helps a bit, but it adds complexity. This complexity is not readily apparent, but you have to separately manage the userlist, you must not get your auth types out of sync (md5/scram), and so on... The only reason pgbouncer stays is that I don't want to mess with it again, but it's death by a thousand paper cuts, enough to complexify deployment and updates to a point where I'm thinking of calling it quit and using a different database in front of postgres.

taffer 6y ago

> Typical usecase: a large fleet of IOT devices with a write-only access to a central database, feeding new data every second. No visibility of the current transactions is needed. Can't you queue and serialize those writes in a middle layer so that you only need a small amount of connections?

anarazel 6y ago
Yea, normally you'd want some type of broker / middle-layer to accept the incoming data from the devices and then send it onward into the database. That broker would also authenticate / validate the devices / data etc. And it can do stuff like batch the input, for efficiency. If you actually need that many incoming connections, for now you'd probably want to use pgbouncer (or oddysey or ...) in transaction or statement pooling mode.
- 1996 6y ago
  Indeed, that's what I currently have, but for this database I'm looking at alternatives (ex: clickhouse) that could get everything and "send it onward" on a schedule. It's just a glorified bulk insert with extra moving parts, but if it works around the issue, why not? BTW Have you played with oddysey? It doesn't seem essentially different (or better) from pgbouncer.
  - anarazel 6y ago
    > BTW Have you played with oddysey? It doesn't seem essentially different (or better) from pgbouncer. pgbouncer is single threaded, oddysey isn't.
    - 1996 6y ago
      that is enough of a reason to try it, thanks!
skrtskrt 6y ago
Seems like a Kafka use case.