Pros and cons with automated/manual cache

Question

Hi everyone,

I am thinking a lot about whether to head for a complete automated cache or manual cache.

Our automatic approach would be a solution that digs through the database, queries and format each potential and future data request and saves it to an appropriate cache storage(memcache or disk based). Data would then not be invalidated and just overwritten by the automated cache update action.

The manual approach is to let each request check if there's an valid and stored cache version that contains the requested data, and if not recreate it and save it to appropriate cache storage.

What's common sense to do? How does the big guys do this?

Thanks a lot!

Answer 1

The answer is of course: it depends! If you are getting a large # of queries to a sparse subset of your data, then on the fly caching could be better than caching everything. For instance, if you were querying a hotel rate database with a complex query, and only a few hotels got hits often, it wouldn't make sense to cache every search for every hotel. Just wait for the user to query something, then cache that result.

If on the other hand you have a widely accessed database set, you may want to look into something like a materialized view http://en.wikipedia.org/wiki/Materialized_view. This basically makes the database to all the caching for you and materializes the query for the full set into a "cached" table.