Moritz-Langer
commented
1 month ago On the weekend I started with an initial implementation along the line of the following:
class DatabaseConnection:
"""Database connection class to be used as 'with' statement.
The class returns a connection object.
"""
pool = None # Class-level connection pool
@classmethod
def initialize_pool(cls, conn_dict):
"""Initialize the connection pool if not already initialized."""
LOGGER.debug(f"conn_dict contains {conn_dict}")
if DatabaseConnection.pool is None:
DatabaseConnection.pool = oracledb.create_pool(
user=conn_dict["user"],
password=conn_dict["password"],
dsn=dsn,
min=2,
max=10,
increment=1,
)
This looks promising and I will create a pull-request after some more polishing
tomkralidis
8luewater
Is your feature request related to a problem? Please describe.
Performance improvements might be gained when using a connection pool for database providers. I have seen this commit to the postgres provider: https://github.com/geopython/pygeoapi/commit/644281359a5c622a7808b67ddf7773723ac60b4d
Describe the solution you'd like Upon application start check for config whether there are features/collections using the Oracle provider. If the Oracle provider is in use, the application should initialize a connection pool. Subsequent connections required by the Oracle provider should then be acquired from this pool rather than creating new connections per request.
Open Question: Initialize Pool globally for example within itemtypes.py?
Describe alternatives you've considered I thought about handling within the oracle provider alone, but this might lead to initializing pools for every request or am I mistaking here?
Additional context Connection pooling can drastically reduce the overhead associated with establishing connections to the database, particularly under load. By managing a pool of reusable connections, the system can gain improvements in response time and reduce the load on the database server.
Discussion Points