Re: Again, sorry, caching. - Mailing list pgsql-hackers

On Sat, 2002-03-16 at 09:01, mlw wrote:
> On a web site, a few specific queries get executed, unchanged, repeatedly.
> Think about an ecommerce site, most of the time it is just a handful of basic
> queries. These basic queries are usually against pretty large product tables. A
> caching mechanism would make these queries pretty light weight.
> 
> The arguments against caching:
> 
> "It is an application issue"
> This is completely wrong. Caching can not be done against a database without
> knowledge of the database, i.e. when the data changes.

But can't this be achieved by using a LISTEN/NOTIFY model, with
user-created rules to NOTIFY the appropriate listener when a table
changes? With a good notification scheme like this, you don't need to
continually poll the DB for changes. You don't need to teach your cache
a lot of things about the database, since most of that knowledge is
encapsulated inside the rules, and supporting tables.

My impression (I could be wrong) is that LISTEN/NOTIFY doesn't get the
press that it deserves. If this model isn't widely used because of some 
deficiencies in the LISTEN/NOTIFY implementation, IMHO our time would be
better spent fixing those problems than implementing the proposed
caching scheme.

If we're looking to provide a "quick and easy" caching scheme for users
attracted to MySQL's query cache, why not provide this functionality
through another application? I'm thinking about a generic "caching
layer" that would sit in between Postgres and the database client. It
could speak the FE/BE protocol as necessary; it would use LISTEN/NOTIFY
to allow it to efficiently be aware of database changes; it would create
the necessary rules for the user, providing a simple interface to
enabling query caching for a table or a set of tables?

What does everyone think?

> OK, let me have it, tell me how terrible an idea this is. tell me how wrong I
> am.

I think your goals are laudable (and I also appreciate the effort that
you and everyone else puts into Postgres); I just think we could get
most of the benefits without needing to implement potentially complex
changes to Postgres internals.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC