Avoid SET ROLE/SET SCHEMA in Shared Pool

Description

Purpose/Overview:
In https://folio-org.atlassian.net/browse/MODINVSTOR-1124 the performance degradation is observed, when the shared connection pool is used. It is expected for the case when the overall number of connections for shared pool is less, but it is less also when the overall number of connections is same. The root cause is that:

1. If connections are separate for each tenant, then you do not need to constantly switch to the desired tenant after receiving a connection. But in such case , there will be too many of them (number of modules * number of tenants).

2. If a shared connection pool is created, then it cannot be as productive as a non-shared one, since in this case the connections have to constantly switch between tenants (each time before the query execution). And hence we see the performance degradation in the test results.

Approach:

If there are free connection in the pool, which was previously used for the desired tenant (for which the connection is being requested), this connection should be taken instead of random one and in such case the "SET ROLE '" + schemaName + "'; SET SCHEMA '" + schemaName + "'" should not be executed for it.
This changes should be implemented in PostgresClient.

It is not simple to do because the PgConnection is vertex class and it is hard to set some "marker" (e.g. modify the PgConnectOptions.properties) for it or identify it somehow (to have mapping between connection and last used schema in PostgreClient).
The best approach is to identify it by the Java Object (e.g. put the PgConnection as the key of the HashMap).
The Vert.x pool closes an idle connection after some time and deletes it from the pool, we don't want to keep references in the HashMap to closed and deleted connection, otherwise we may get out of memory.

Environment

None

Potential Workaround

None

Checklist

hide

TestRail: Results

Activity

Show:
Done

Details

Assignee

Reporter

Priority

Development Team

Core: Platform

Fix versions

Release

Ramsons (R2 2024)

TestRail: Cases

Open TestRail: Cases

TestRail: Runs

Open TestRail: Runs
Created November 21, 2023 at 1:54 PM
Updated October 13, 2024 at 6:14 PM
Resolved May 15, 2024 at 3:48 PM
TestRail: Cases
TestRail: Runs

Flag notifications