Re: Database Caching

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thursday, February 28, 2002 3:27 PM
To: Greg Sabino Mullane
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Database Caching

"Greg Sabino Mullane" <greg(at)turnstep(dot)com> writes:
> III. Relation caching

> The final cache is the relation itself, and simply involves putting
the entire
> relation into memory. This cache has a field for the name of the
relation,
> the table info itself, the type (indexes should ideally be cached more
than
> tables, for example), the access time, and the acccess number. Loading
could
> be done automatically, but most likely should be done according to a
flag
> on the table itself or as an explicit command by the user.

This would be a complete waste of time; the buffer cache (both Postgres'
own, and the kernel's disk cache) serves the purpose already.

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.

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.

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?

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