Re: query cache

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?

> 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.

There are a number of proven caching models which PostgreSQL currently does not yet implement. I'd think it would be more profitable to pursue one of those, such as:

* parse caching in the client (JDBC has this, but libpq does not).
* shared cached plans between sessions (snapshot issues here could be nasty)
* fully automated materialized views

If you want to do something radical and new, then come up with a way for a client to request and then reuse a complete query plan by passing it to the server. That would pave the way for client-side plan caching (and plan manipulation) code written in a variety of languages, and thus further innovation through creative algorithms and other ideas.

--Josh Berkus