Re: Database Caching - Mailing list pgsql-hackers

On Fri, 2002-03-01 at 04:44, Dann Corbit wrote:
> As I've commented before, I have deep misgivings about the idea of a
> query-result cache, too.
> >>
> I certainly agree with Tom on both counts.
> 
> Think of the extra machinery that would be needed to retain full
> relational integrity with a result cache...
> 
> Then think of how easy it is to write your own application that caches
> results if that is what you are after and you know (for some reason)
> that it won't matter if the database gets updated.

That would be trivial indeed.

The tricky case is when you dont know when and how the database will be
updated. That would need an insert/update/delete trigger on each and
every table that contributes to the query, either explicitly ot through
rule expansion. Doing that from client side would a) be difficult and b)
probably too slow to be of any use. To do it in a general fashion wopuld
also need a way to get the expanded query tree for a query to see which
tables the query depends on.

> I don't see how result caching can be a win, since it can be done when
> needed anyway, without adding complexity to the database engine.  Just
> have the application cache the result set.  Certainly a web server could
> do this, if needed.

More advanced application server can do it all right. But you still need
sound cache invalidation mechanisms.

> If there were a way to mark a database as read only (and this could not
> be changed unless the entire database is shut down and restarted in
> read/write mode) then there might be some utility to result set cache.
> Otherwise, I think it will be wasted effort.  It might be worthwhile to
> do the same for individual tables (with the same sort of restrictions).
> But think of all the effort that would be needed to do this properly,
> and what sort of payback would be received from it?

The payback will be blazingly fast slashdot type applications with very
little effort from end application programmer.

> Again, the same goals can easily be accomplished without having to
> perform major surgery on the database system.  I suspect that there is
> some logical reason that Oracle/Sybase/IBM/Microsoft have not bothered
> with it.

I think they were designed/developed when WWW was nonexistent, and
client-server meant a system where client and server where separated by
a (slow) network connection that would negate most of the benefit from
server side cacheing. On todays application server scenario the client
(AS) and server (DB) are usually very close, if not on the same computer
and thus effectively managed cache can be kept on DB to avoid all the
cache invalidation logic going through DB-AS link.

> I am ready to be convinced otherwise if I see a logical reason for it.
> But with the current evidence, I don't see any compelling reason to put
> effort in that direction.

In what direction are _you_ planning to put your effort ?

------------
Hannu