From: | Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com> |
---|---|
To: | Masahiko Sawada <masahiko(dot)sawada(at)2ndquadrant(dot)com> |
Cc: | Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [PATCH] postgres_fdw connection caching - cause remote sessions linger till the local session exit |
Date: | 2020-07-01 09:03:22 |
Message-ID: | CAGPqQf1dXF8oKiBYv6VmvrwFYZdQH198NdJ+WTdaSpSpuqoUVw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Tue, Jun 30, 2020 at 8:54 AM Masahiko Sawada <
masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:
> On Mon, 22 Jun 2020 at 14:56, Bharath Rupireddy
> <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> >
> > Hi,
> >
> > When a query on foreign table is executed from a local session using
> > postgres_fdw, as expected the local postgres backend opens a
> > connection which causes a remote session/backend to be opened on the
> > remote postgres server for query execution.
> >
> > One observation is that, even after the query is finished, the remote
> > session/backend still persists on the remote postgres server. Upon
> > researching, I found that there is a concept of Connection Caching for
> > the remote connections made using postgres_fdw. Local backend/session
> > can cache up to 8 different connections per backend. This caching is
> > useful as it avoids the cost of reestablishing new connections per
> > foreign query.
> >
> > However, at times, there may be situations where the long lasting
> > local sessions may execute very few foreign queries and remaining all
> > are local queries, in this scenario, the remote sessions opened by the
> > local sessions/backends may not be useful as they remain idle and eat
> > up the remote server connections capacity. This problem gets even
> > worse(though this use case is a bit imaginary) if all of
> > max_connections(default 100 and each backend caching 8 remote
> > connections) local sessions open remote sessions and they are cached
> > in the local backend.
> >
> > I propose to have a new session level GUC called
> > "enable_connectioncache"(name can be changed if it doesn't correctly
> > mean the purpose) with the default value being true which means that
> > all the remote connections are cached. If set to false, the
> > connections are not cached and so are remote sessions closed by the
> local backend/session at
> > the end of each remote transaction.
>
> I've not looked at your patch deeply but if this problem is talking
> only about postgres_fdw I think we should improve postgres_fdw, not
> adding a GUC to the core. It’s not that all FDW plugins use connection
> cache and postgres_fdw’s connection cache is implemented within
> postgres_fdw, I think we should focus on improving postgres_fdw. I
> also think it’s not a good design that the core manages connections to
> remote servers connected via FDW. I wonder if we can add a
> postgres_fdw option for this purpose, say keep_connection [on|off].
> That way, we can set it per server so that remote connections to the
> particular server don’t remain idle.
>
>
+1
I have not looked at the implementation, but I agree that here problem
is with postgres_fdw so we should try to solve that by keeping it limited
to postgres_fdw. I liked the idea of passing it as an option to the FDW
connection.
Regards,
>
> --
> Masahiko Sawada http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
>
--
Rushabh Lathia
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Gustafsson | 2020-07-01 09:04:21 | Re: [PATCH v1] Allow COPY "text" to output a header and add header matching mode to COPY FROM |
Previous Message | Daniel Gustafsson | 2020-07-01 08:57:28 | Re: PATCH: Add uri percent-encoding for binary data |