Re: query cache

On Sat, Mar 24, 2012 at 3:22 PM, Joshua Berkus <josh(at)agliodbs(dot)com> wrote:

> Billy,
>
> > I've done a brief search of the postgresql mail archives, and I've
> > noticed a few projects for adding query caches to postgresql, (for
> > example, Masanori Yamazaki's query cache proposal for GSOC 2011),
>
> ... which was completed, btw. Take a look at the current release of
> pgPool.
>
> Are you proposing this for GSOC2012, or is this just a general idea?
>

just a general idea, but if someone wants to work on it for GSOC2012, I
wouldn't mind giving a helping hand. I'm not a student, so GSOC probably
doesn't apply to me.

>
> > I'm wondering if anyone would be interested in a query cache as a
> > backend to postgresql? I've been playing around with the postgresql
> > code, and if I'm understanding the code, I believe this is possible.
>
> 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.

Billy