Idea: Statement Caching

[tpolecat]

Skunk closes prepared statements after use, which ensures that sessions don't build up an unbounded number of open statements on the server. This is good in principle, but it means that we have to do a Parse exchange for every prepared statement, even if an identical one was previously prepared on the same Session.

An alternative way to do this would be to have an LRU cache from Statement to StatementId on each Session.

• Parse checks the cache first and if it's there we have no need to do further work.
• If the statement is not in the cache, do the Parse exchange and insert into the cache on success.

On both access and insert there may be cache evictions, so each Session needs to track this set as well.

• On return to the pool all pending evictions are processed asynchronously (i.e., we do a bunch of Close exchanges). This means a Session returning to the pool may be unavailable for immediate use. We can pipeline these messages, which should be fast.

A consequence here is that .prepare can now return an F[PreparedCommand[...]] rather than a Resource[F, PreparedCommand[...]] (analogously for queries). This seems like a usability improvement to me but it will break everyone's code. As a first step we could just do Resource.eval to keep the API the same.

Note that the Describe cache is per pool so we don't want to roll it into the Parse cache, which is per session. If we did this we would end up running Describe exchanges for statements we already know are valid.

[tpolecat]

I should add that this really means we need to redo the session pool, which is overly complicated at the moment.

[fdietze]

How about ditching the session pool of skunk entirely and refer to dedicated solutions like https://www.pgbouncer.org/ instead?