Re: Postgresql Caching

Alvaro Herrera wrote:
> 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. :-(
>>
>> 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.
>
> Hmm, have you tried having a NOTIFY when the Postgres record is updated,
> and having a listener daemon that updates the value when it gets the
> notification?
>
> One issue is that if the listener is not running you don't get to
> update nor invalidate the record, which would cause consistency problems.

The original poster seemed to be talking about seldom/never changed data.
But the interesting and often-discussed case is really rapidly changing
data such as shopping carts, web session objects etc.

On a somewhat unrelated note, I recently discussed a high performance
postgres installation with the CIO, who told me they were using a large
array of high end solid state disks (there is some serious money here) for
the backing store. That's probably something worth thinking about for high
traffic sites, which might help to relieve the problems for which caching
or in-memory tables are thought to be possible answers.

cheers

andrew