Re: memcached and PostgreSQL

> The big concern I have about memcache is that because it controls
> storage external to the database there is no way to guarantee the cache
> is consistent with the database.

I've found that letting applications add data to memcache and then
letting the database replace or delete keys seems to be the best
approach to minimize exactly this issue. Having two clients update the
cache is risky. Using triggers or using NOTIFY + tailing logs makes
this much more bullet proof.

> This is similar to sending email in a trigger or on commit where you
> can't be certain you send email always
> and only on a commit.

While this is certainly a possibility, it's definitely closer to the
exception and not the normal instance.

> In the database, we mark everything we do with a transaction id and
> mark
> the transaction id as committed in on operation. I see no way to do
> that with memcache.

Correct. With an ON COMMIT trigger, it'll be easier to have a 100%
accurate cache. That said, memcache does exist out side of the
database so it's theoretically impossible to guarantee that the two are
100% in sync. pgmemcache goes a long way towards facilitating that the
cache is in sync with the database, but it certainly doesn't guarantee
it's in sync. That being said, I haven't had any instances of it not
being in sync since using pgmemcache (I'm quite proud of this, to be
honest *grin*). For critical operations such as financial
transactions, however, I advise going to the database unless you're
willing to swallow the financial cost of cache discrepancies.

-sc

--
Sean Chittenden