Re: Postgresql Caching

mark(at)mark(dot)mielke(dot)cc wrote:
> 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 parallel before I commit, notices
> that the memcache record is invalidated, it queries the data from
> SQL, and updates the memcache record back to the old value. :-(

How can this fail? The PostgreSQL MVCC will hold the second transaction
back until the effect on the tuple is known (i.e. after the first
transaction is over). Have you not been using SERIALIZABLE transactions?

With a bit of careful planning (and a few SELECT FOR UPDATE queries to
prevent deadlock), having perfect consistency and correct caching is
possible.

> 2) When updating a PostgreSQL record, I updated the memcache record
> to the new value. If another process comes along in parallel before
> I commit, that is still looking at an older view, cross-referencing
> may not work as expected.

This breaks integrity, and all bets are off.

> 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.

What exactly does your application do about the possibility of incorrect
data?

> 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.

Consistency and caching are not mutually exclusive, and there are many
frameworks that handle the burden of maintaining both for you.

Regards,
LL