Best practices

[James Robinson]

Joshua,

	With respect to how to keep cache-deletes up-to-date with db-deletes  
(or updates for that matter), we use database triggers on tables  
whose data is known to be cached to log row ids into a 'cache-is- 
dirty' table. Then periodically an external database client  
("sweeper" / "invalidator") wakes up, drains all rows from the cache- 
is-dirty table, then fires off the appropriate memcache delete  
commands. We don't do so many bulk-deletes (or updates) on our cached  
data, so this has worked well for us so far. If the number of  
individual rows which expand from a 'where category_id = N' type  
delete clause is 'reasonable', I'd expect this pattern to also work  
in this case.

	This pattern saves you from having to maintain an index in the cache  
itself, whose downsides include costing cache memory, could get  
expunged from the cache in tight-memory times, and, most importantly  
forces you to fetch the index object plus your real data every page hit.

	The DB knows exactly what rows gets deleted / updated, be it through  
your applogic or through low-level SQL ops. Use triggers to preserve  
that information in such a way so that it can be made useful for  
purging from caches also.

	We lifted this design from slony-I (www.slony.info).  It is also  
similar in philosophy to the pgmemcache project, although that embeds  
the memcache client C API directly into the database backend as  
opposed to using an external process to do the talking to memcache.  
(http://pgfoundry.org/projects/pgmemcache/)

----
James Robinson
Socialserve.com