Unfortunately I cannot, firstly – as I wrote – clients are mostly Windows (no pgpool on Windows),
Secondly this customer, suppliers, third parties – I have nothing to say about their systems.
ØEmbedding this functionality directly into the driver seems like a lot of work for very little gain.
The functionality is in the pgpool/pqc – there is definitely work too factor it out into a library.
ØThat is, what is the probability of each client performing the exact same query?
For the test I did today (Enterprise Architect software) – over 95%. Out of around 3900 queries only 251 were unique.
Never underestimate innovative ways to write and integrate software. J
Ø And, as I assume there are multiple clients connecting to the database, why wouldn't it be more advantageous for multiple clients to share the same cache (e.g. pgpool being used as it is designed, to proxy and, as already implemented, cache the data?)
Because this is not the problem with the central DB – it is the problem with very complicated network configuration with dozens of
Firewalls, gateways etc. etc.
The cost is not in the db backend performance – cost is in the number of packets sent over the network.
Anyway it looks like consensus is that is not the best idea.
I’ll probably spend couple more hours on the crude Python cache I used for tests – make .exe out of it