Re: Database Caching

> > > 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.
 There are a couple of catches with that idea.  First, we have thirty+
applications that we've written, and trying to go back and patch the caching
into all of them would be much more work than just doing it in the DB.
Secondly, changes to the data can be made from psql, other apps, or from
triggers and hence, there is no reliable way to deal with cache expiration.
Not only that, but if you have a pool of web servers, applications on each
one can be inserting/updating data, and none of the other machines have any
clue about that.
 Really, doing it in PG makes a lot of sense.  Doing it outside of PG has a
lot of problems.  At one point, I was resolved that I was going to do it in
middleware.  I sat down and planned out the entire thing, including a really
nifty hash structure that would make cache expiration and invalidtion
lightning-fast... but I had focused entirely on the implementation, and when
I realized all of the drawbacks to doing it outside of the backend, I
scrapped it.  That's the first time I've ever really wished that I was a C
programmer....
  In the worst-case scenario (never repeating a query), result caching
would have a very small overhead.  In any semi-realistic scenario, the
benefits are likely to be significant to extraordinary.

steve