Re: Postgresql Caching - Mailing list pgsql-hackers

On Sun, Oct 15, 2006 at 08:31:36PM +0530, Merlin Moncure wrote:
> On 10/15/06, Anon Mous <soundami@yahoo.com> wrote:
> > Would it be possible to combine a special memcache implementation of
> > memcache with a Postgresql interface wrapper?
> have you seen
> http://people.freebsd.org/~seanc/pgmemcache/

Interesting. I note that they don't address the view consistency
problem any better than an application using memcached directly.
And that's the real problem with memcached, and why people are
tempted to 'indulge' by relying on PostgreSQL. Some people value
the consistency. Others don't. memcached, whether application-side,
or whether automatically invoked by triggers (pgmemcache) is a
decision to ignore the consistency.

Using memcache, I've had problems with consistency brought right to
the front. Both of these have failed me:
   1) When updating a PostgreSQL record, I invalidate the memcache record.      If another process comes along in
parallelbefore I commit, notices      that the memcache record is invalidated, it queries the data from      SQL, and
updatesthe memcache record back to the old value. :-(
 
   2) When updating a PostgreSQL record, I updated the memcache record      to the new value. If another process comes
alongin parallel before      I commit, that is still looking at an older view, cross-referencing      may not work as
expected.

I'm currently settled on 2), but setting a short timeout (5 seconds) on
the data. Still an imperfect compromise between speed and accuracy, but
it isn't causing me problems... yet.

I don't see memcache as a general solution to query plan or query
result caching. Along these lines, I would look more towards having
the query plans or query results stored in cache along with the
transaction numbers that would let us know whether either is valid.

Consistency is very valuable to me. If it wasn't for memcache being
hundreds or more times faster, I wouldn't use it in the cases I do.
It can be dangerous.

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/