Re: In theory question

>>> This may be a question for -hackers, but I don't like disturbing them
>>> unnecessarily.
>>> I've been having a look at memcached. I would like to ask, is there any
>>> reason that, theoretically, a similar caching system could be built
>>> right into the db serving daemon?
>>> I.e., the hash tables and libevent could sit on top of postmaster as an
>>> optional component caching data on a per-query basis and only hitting
>>> the actual db in the event of a cache miss?
>>
>> I think this is close to what MySQL's query cache does. The question is
>> if this should be the job of the DBMS and not another layer. At least
>> the pgmemcache author and I think that it's better done outside the
>> DBMS. See http://people.FreeBSD.org/~seanc/pgmemcache/pgmemcache.pdf
>> for the idea.

I have always found MySQL's query cache to be utterly useless.

Think about it this way :

It only works for tables that seldom change.
It does not work for big tables (like the posts table of a forum) because
the cache would have to be huge.

So, the most frequent usage of MySQL's query cache is for dumb
applications who use, for instance, PHP, store their configuration in
MySQL, and reload it on each and every page with a SELECT * FROM
configuration.

In this case, you save the query time, but you don't save : the roundtrip
between PHP and the database, extracting query results, building objects
from them, time spent in ORMs, etc.

A much better solution is to do your own caching, for instance using
shared memory in the application server, and then you cache native
language objects. You not only save the query time, but also all the time
spent building those objects on every page load.