Re: Database Caching

mlw <markw@mohawksoft.com> writes:
> My previous company, www.dmn.com, has a music database system. We logged all
> the backed info, most of the queries were duplicated many times. This can be
> explained by multiple users interested in the same thing or the same user
> hitting "next page"

> If you could cache the "next page" or similar hit results, you could really
> increase throughput and capaciy of a website.

Sure, but the most appropriate place to do that sort of thing is in the
application (in this case, probably a cgi/php-ish layer).  Only the
application can know what its requirements are.  In the case you
describe, it'd be perfectly okay for a "stale" cache result to be
delivered that's a few minutes out of date.  Maybe a few hours out of
date would be good enough too, or maybe not.  But if we do this at the
database level then we have to make sure it won't break *any*
applications, and that means the most conservative validity assumptions.
(Thus all the angst about how to invalidate cache entries on-the-fly.)

Likewise, the application has a much better handle than the database on
the issue of which query results are likely to be worth caching.

I think that reports of "we sped up this application X times by caching
query results on the client side" are interesting, but they are not good
guides to what would happen if we tried to put a query-result cache into
the database.
        regards, tom lane