Re: Postgresql Caching - Mailing list pgsql-hackers

From mark@mark.mielke.cc
Subject Re: Postgresql Caching
Date
Msg-id 20061016105715.GB6709@mark.mielke.cc
Whole thread Raw
In response to Re: Postgresql Caching  (Shane Ambler <pgsql@007Marketing.com>)
List pgsql-hackers
On Mon, Oct 16, 2006 at 05:59:05PM +0930, Shane Ambler wrote:
> > Registering each cache entry by the tables included in the query and 
> >invalidating the cache during on a committed update or insert 
> >transaction to any of the tables would, transparently, solve the 
> >consistency problem.
> That was part of my thinking when I made the suggestion of adding 
> something like memcached into postgres.

There is a valid suggestion in here, but I think it's the caching of
query plans, and caching of query plan results that the PostgreSQL
gain would be at.

The query to query plan cache could map SQL statements (with parameters
specified) to a query plan, and be invalidated upon changes to the
statistical composition of any of the involved tables. The query plan to
query results cache would keep the results and first and last transaction
ids that the results are valid for.

Although it sounds simple, I believe the above to be very complicated to
pursue. The real PostgreSQL hackers (not me) have talked at length about
it over the last while that I've read their mailing lists. They've come
up with good ideas, that have not all been shot down. Nobody is willing
to tackle it, because it seems like a lot of effort, for a problem that
can be mostly solved by application-side caching.

It's a subject that interests me - but it would take a lot of time, and
that's the thing that few of us have. Time sucks. :-)

Cheers,
mark

-- 
mark@mielke.cc / markm@ncf.ca / markm@nortel.com     __________________________
.  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada
 One ring to rule them all, one ring to find them, one ring to bring them all                      and in the darkness
bindthem...
 
                          http://mark.mielke.cc/



pgsql-hackers by date:

Previous
From: mark@mark.mielke.cc
Date:
Subject: Re: Postgresql Caching
Next
From: Sean Davis
Date:
Subject: Re: Problems building 8.2beta1 on macos G5 xserve