4 ms·

PGBouncer doesn't do anything to magically prevent connections to postgres from being locked and idle for a pending query. It's just an external connection poo

by thdxr 8y ago

PGBouncer doesn't do anything to magically prevent connections to postgres from being locked and idle for a pending query. It's just an external connection pooler when your native language driver doesn't have a good one

doh 8y ago
pgb can timeout connections and close them if they are idle for too long, which is usually the way to deal with stale connections. Not sure what solution are you imagining would be the right one.
- thdxr 8y ago
  Examine any other distributed system that implements pipelining. Allows for multiple pending requests on a connection (simply by having IDs on every request) which makes for efficient use of connections.
  - doh 8y ago
    I do understand where are you coming from, but there is price to everything. Most distributed systems have eventual consistency. Very few can achieve strong consistency. Postgres sacrifices a lot for a strong consistency and predictable performance. I'm not saying that PG has the best model, but for the wast majority of users, out of connections is just not an issue. Most clients have now direct pooling and they are very easy to setup. There are a few who need more connections, but there are poolers for it. What you gain but separating connections is much easier monitoring and debugging process. You can examine each connection, its impact on resources, state, what locks they need to acquired, ... Additionally, because each connection opens a file descriptor, you gain a lot of operating security from the underlying OS and its kernel. PG contributors spent almost 3 decades building on this system. I'm pretty sure the gain would be so minuscule in comparison to the effort that would have to be put in to rebuild the connection model. I for one would appreciate server polling built in, better HA including simple discoverability, handoffs, ...

4 ms·

PGBouncer doesn't do anything to magically prevent connections to postgres from being locked and idle for a pending query. It's just an external connection poo

by thdxr 8y ago

doh 8y ago
pgb can timeout connections and close them if they are idle for too long, which is usually the way to deal with stale connections. Not sure what solution are you imagining would be the right one.
- thdxr 8y ago
  Examine any other distributed system that implements pipelining. Allows for multiple pending requests on a connection (simply by having IDs on every request) which makes for efficient use of connections.
  - doh 8y ago
    I do understand where are you coming from, but there is price to everything. Most distributed systems have eventual consistency. Very few can achieve strong consistency. Postgres sacrifices a lot for a strong consistency and predictable performance. I'm not saying that PG has the best model, but for the wast majority of users, out of connections is just not an issue. Most clients have now direct pooling and they are very easy to setup. There are a few who need more connections, but there are poolers for it. What you gain but separating connections is much easier monitoring and debugging process. You can examine each connection, its impact on resources, state, what locks they need to acquired, ... Additionally, because each connection opens a file descriptor, you gain a lot of operating security from the underlying OS and its kernel. PG contributors spent almost 3 decades building on this system. I'm pretty sure the gain would be so minuscule in comparison to the effort that would have to be put in to rebuild the connection model. I for one would appreciate server polling built in, better HA including simple discoverability, handoffs, ...