Re: query cache

Thanks.. I'll keep those issues in mind.

On Sat, Mar 24, 2012 at 6:18 PM, Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:

> >> Well, you'd have to start by demonstrating the benefit of it. The
> >> advantage of query caches in proxies and clients is well-known, because
> you
> >> can offload some of the work of the database onto other servers, this
> >> increasing capacity. Adding a query cache to the database server would
> >> require the "query identity recognition" of the cache to be far cheaper
> (as
> >> in 10X cheaper) than planning and running the query, which seems
> unlikely
> >> at best.
> >>
> >> I figured I'd create the md5 digest of the sourceText of a query, and
> then
> > look that up in a hash. I don't think that will be very expensive. I'll
> > have another hash to keep track of which queries are dependent on which
> > relations, so that when a relation is changed somehow (and committed),
> the
> > query is then invalidated and removed from the query hash.
>
> From the experience of implementing query cache in pgool-II there are
> some suggestions:
>
> - A query result cache should not be created if the transaction
> including the SELECT is not committed.
>
> - Since a transaction could have many SELECTs, you need to keep those
> query results somewhere in a temporary storage. You could either
> discard or register them to the query cache storage depending on the
> transaction's fate, either aborted or committed.
>
> - If a SELECT has non-immutable functions, then the query result
> should not be cached.
>
> - If a SELECT uses temporary tables, then the query result should not
> be cached.
>
> - If a SELECT uses unlogged tables, then the query result should not
> be cached because their data could vanish after crash recovery. Of
> course this is only applied if you plan to use cache storage which
> does not survive after crash.
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
>