3 ms·

Are there any performance issues with connection handling? Not sure how/where a connection pooler fits in.

by nijave 4y ago

mwcampbell 4y ago
Mike has called this out as a weakness of PostgreSQL, and an advantage of Microsoft SQL Server and Oracle, before.
mike_hearn 4y ago
This is also discussed in the talk, which I hope will be on YouTube soon enough (it was live streamed). DB connections are heavier than web server connections, in particular in terms of memory (when using postgres, not sure about mysql). Multiplexers like pgbouncer can address this, but then you lose a bit of the deployment simplicity. Fortunately RAM is pretty cheap nowadays, you can configure the DB to disconnect idle connections, you can use multiplexers and you can make the client transparently reconnect when a connection is needed. So it turns into a RAM/user latency tradeoff. There are some benefits though. Native DB protocols will tend to stream results in binary, avoiding a JSON encoding that can expand the bandwidth requirements. Also you can get rid of the server side load balancing, because good db drivers know how to load balance amongst R/O replicas already. A lot of deployment complexity in web apps is driven by the fact that browsers handle errors by just throwing up an error page and stopping, so you need to avoid even tiny amounts of downtime. When you control the client you can do failover and recovery more gracefully, which in turn relaxes the availability constraints on the servers a bit.
- nijave 4y ago
  It seems like this uses native Postgres security/roles which I think would prevent a connection pooler from working (unless you held open 1 connection per user) Ultimately it'd be nice if Postgres could keep a hot worker pool for connections instead of forking processes but afaik there's been plenty of debate over the years and no consensus I think high connection thrashing also eats up CPU and there's the issue of increasing load eats more RAM which reduces Postgres memory cache. We had a connection leak problem and clearing ~2600 PG connections saved something like 80-100GB of RAM
  - mike_hearn 4y ago
    I think you can use https://github.com/pgaudit/set_user https://github.com/pgaudit/set_user combined with some pgbouncer configuration to solve that, but agreed that postgres doesn't have perfect support for this scenario. It'd be better to have a proxy that is designed specifically for this use case that knows how to drive something like the set_user extension (and better if it were to be merged upstream).

Are there any performance issues with connection handling? Not sure how/where a connection pooler fits in.

by nijave 4y ago

Are there any performance issues with connection handling? Not sure how/where a connection pooler fits in.

mwcampbell 4y ago

Mike has called this out as a weakness of PostgreSQL, and an advantage of Microsoft SQL Server and Oracle, before.

mike_hearn 4y ago

This is also discussed in the talk, which I hope will be on YouTube soon enough (it was live streamed). DB connections are heavier than web server connections, in particular in terms of memory (when using postgres, not sure about mysql). Multiplexers like pgbouncer can address this, but then you lose a bit of the deployment simplicity. Fortunately RAM is pretty cheap nowadays, you can configure the DB to disconnect idle connections, you can use multiplexers and you can make the client transparently reconnect when a connection is needed. So it turns into a RAM/user latency tradeoff. There are some benefits though. Native DB protocols will tend to stream results in binary, avoiding a JSON encoding that can expand the bandwidth requirements. Also you can get rid of the server side load balancing, because good db drivers know how to load balance amongst R/O replicas already. A lot of deployment complexity in web apps is driven by the fact that browsers handle errors by just throwing up an error page and stopping, so you need to avoid even tiny amounts of downtime. When you control the client you can do failover and recovery more gracefully, which in turn relaxes the availability constraints on the servers a bit.

nijave 4y ago
It seems like this uses native Postgres security/roles which I think would prevent a connection pooler from working (unless you held open 1 connection per user) Ultimately it'd be nice if Postgres could keep a hot worker pool for connections instead of forking processes but afaik there's been plenty of debate over the years and no consensus I think high connection thrashing also eats up CPU and there's the issue of increasing load eats more RAM which reduces Postgres memory cache. We had a connection leak problem and clearing ~2600 PG connections saved something like 80-100GB of RAM
- mike_hearn 4y ago
  I think you can use https://github.com/pgaudit/set_user https://github.com/pgaudit/set_user combined with some pgbouncer configuration to solve that, but agreed that postgres doesn't have perfect support for this scenario. It'd be better to have a proxy that is designed specifically for this use case that knows how to drive something like the set_user extension (and better if it were to be merged upstream).