Re: Having query cache in core

On 07.05.2018 11:58, Tatsuo Ishii wrote:
>> On 07.05.2018 05:32, Tatsuo Ishii wrote:
>>> Does anybody think having in-memory query result cache in core is a
>>> good idea? From the experience of implementing the feature in
>>> Pgpool-II, I would think this is not terribly hard job. But first of
>>> all I'm wondering if there's a demand for the feature.
>> Do you want to implement this cache locally in backend?
> No, as a global cache, if I employ the design of Pgpool-II. Pgpool-II
> offsers two types of query result cache: in the shared memory or using
> external cache server (i.e. memcached).
>
>> Global result cache (like one used in mySQL) may be more efficient.
>> But I think it is better to start first with
>> 1. Global prepared statements cache
>> 2. Global catalog cache
>> 3. Global relation cache
> Are they totally different story from query result cache, no?
Well, catalog/relation/plan caches are really not related with query
result cache.
But from my point of view first three are much more important and critical.
Just because catalog is used by all backends. It is possible to
discuss/investigate how frequently DBMS applications are issuing the
queries returning the same result
(for example, search engines have classical 90/10 relation: most of
users are doing the same queries, so caching is vital for Google&Co).
But I am not sure
that it is true for database applications... But there are no doubts, at
least for OLAP, that applications used to run queries with the same
query execution plan (but with different parameters).
So having global prepared statement cache seems to be much more useful
than caching results.

Also, implementations of all shared caches have to solve the similar
problems: access synchronization, invalidation,...
In this sense query result cache implementation will be similar with
other shared caches implementation. If we have infrastructure for
building efficient shared caches (lockless algorithms, smart
invalidation, ...) then it will be not so difficult to implement result
cache on top of it.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company