I think we should add something like this to the toolkit, as the increase in performance can be major and doing caching in this way (by simply adding "-cache" to a particular db_* call) is simpler than using util_memoize. And util_memoize doesn't work well with db_multirow, either.
Our current scheme at Greenpeace is oversimplified for generalized use - we just flush the cache every five minutes and take care to only cache content that we don't mind being slightly out-of-date. We're now serving 100,000 page views (many more hits) a day with negligible load on the servers, not bad for a site where every page is built from several queries on the content repository to build news and features side panels as well as the main content area.
I've been thinking a bit about a more generalized scheme, and think that a simple approach can get us a lot of performance benefit without making things overly complex.
For instance, in my existing implementation one can specify a database cache pool with the query. One possibility would be to define a cache pool specifically for permissions, which gets flushed whenever an object's permissions are modified or perms created for a new object. In most OpenACS 4 deployed sites both of these actions are relatively rare, meaning that for comparatively long stretches of time permission queries could come from cache. Permissions performance issues? What issues? Just cache!
Of course there are pitfalls in a simple approach of this sort. Killing the perms cache wouldn't kill content-building queries that interrogate permissions directly unless they used the same cache pool, sort of defeating the purpose of allowing for different pools. So some degree of sophistication is necessary.
Still ... given our experience with the greenpeace site and the ease of implementing my overly-simple hack (I spent a couple of hours rewriting the db-* API code to cache, with the most complex being multirow, not surprisingly) makes me confident that a reasonably simple approach can succeed.
And of course we needn't force the use of caching. "disable/enable db_* query caching" and "cache pool timeout" could be parameters settable by the system admin. After all, plenty of folks are running today with no caching whatsoever.
I'm interested in comments from folks, that's the point of my post.