Re: Built-in connection pooling

On Mon, Jan 22, 2018 at 06:51:08PM +0100, Tomas Vondra wrote:
> > Yes, external connection pooling is more flexible. It allows to
> > perform pooling either at client side either at server side (or even
> > combine two approaches).>
> > Also external connection pooling for PostgreSQL is not limited by
> > pgbouncer/pgpool.>
> > There are many frameworks maintaining their own connection pool, for
> > example J2EE, jboss, hibernate,...>
> > I have a filling than about 70% of enterprise systems working with
> > databases are written in Java and doing connection pooling in their
> > own way.>
>
> True, but that does not really mean we don't need "our" connection
> pooling (built-in or not). The connection pools are usually built into
> the application servers, so each application server has their own
> independent pool. With larger deployments (a couple of application
> servers) that quickly causes problems with max_connections.

I found this thread and the pthread thread very interesting. Konstantin,
thank you for writing prototypes and giving us very useful benchmarks
for ideas I thought I might never see.

As much as I would like to move forward with coding, I would like to
back up and understand where we need to go with these ideas.

First, it looks like pthreads and a builtin pooler help mostly with
1000+ connections. It seems like you found that pthreads wasn't
sufficient and the builtin pooler was better. Is that correct?

Is there anything we can do differently about allowing long-idle
connections to reduce their resource usage, e.g. free their caches?
Remove from PGPROC? Could we do it conditionally, e.g. only sessions
that don't have open transactions or cursors?

It feels like user and db mismatches are always going to cause pooling
problems. Could we actually exit and restart connections that have
default session state?

Right now, if you hit max_connections, we start rejecting new
connections. Would it make sense to allow an option to exit idle
connections when this happens so new users can connect?

I know we have relied on external connection poolers to solve all the
high connection problems but it seems there might be simple things we
can do to improve matters. FYI, I did write a blog entry comparing
external and internal connection poolers:

https://momjian.us/main/blogs/pgblog/2017.html#April_21_2017

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +