Re: Database Caching

Tom Lane wrote:
>
> "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 appreciate your position, and I can see your point, however, where caching is
a huge win is when you have a database which is largely static, something like
the back end of a web server.

The content is updated regularly, but not constantly. There is a window, say 4
hours, where the entire database is static, and all it is doing is running the
same 100 queries, over and over again.

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.