connection pool support

[canhduong28]

Hello,

I am developing a web application in which I use pyexasol to query for data from Exasol.

Is it possible to use a connection pool in pyexasol?

Having a connection pool would allow the web server to maintain long-running connections in memory for efficient re-use.

Thanks

[littleK0i]

@nautilus28 , it might be a bit difficult to implement proper connection pooling with Exasol due to lack of function to reset session state. At this moment all state changes (e.g. autocommit, query timeout, script languages) will be carried over to the next session.

Also, all result handles and prepared statements will remain opened, unless we manually track the current state of each object.

On top of that, Exasol instance has only 100 slots for parallel query execution. But it's a good idea to keep this number lower than 20-30 and use "Queue" pattern instead to limit parallelism. Instead of having potentially large spikes of workload, just queue it and execute only 5-10 queries at the same time. All jobs will be finished faster that way.

If you have a busy web-interface querying analytical database, it is advised to add a simple caching layer (MySQL, PostgreSQL, Redis, Mongo, SQLite, Tarantool, etc.) and send web requests to this layer instead. You may update data in this layer using ETL process.

[canhduong28]

@wildraid Great answer, thank you so much.