On Tue, Jun 30, 2020 at 8:54 AM Masahiko Sawada < masahiko.saw...@2ndquadrant.com> wrote:
> On Mon, 22 Jun 2020 at 14:56, Bharath Rupireddy > <bharath.rupireddyforpostg...@gmail.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
