What to expect from built-in query caching
Like my function-profiling question, this one has become less pressing because my app's critical query is now running fast enough that I'm not so concerned about cache misses, but I'd still like to get a better understanding of caching behavior and best practices.
Specifically, I'm wondering under what circumstances a cached query might be evicted from the cache without any changes to the query's code or the data it depends on. Is there any behind-the-scenes logic that evicts less frequently / recently used queries? If so, does that run per-instance, or might a less heavily used instance's cached queries be evicted to make room for another instance's entries? In a similar vein, are there things that might cause the cache to be cleared entirely?
In my app, data is read much more frequently than it is written, and the same queries are likely to be requested from different clients, so in a world where the cache weren't actively managed or periodically cleared, I'd expect a very high hit rate. When I was looking at a significant discrepancy between cached and uncached performance, though, I was nervous about relying on cache hits and considering whether I might need to implement some bespoke, app-level caching to ensure consistently good performance.
