I think where it usually goes downhill is when it gets too complex to ensure whether the cache is still valid.
There are only two hard things in Computer Science: cache invalidation and naming things. [Phil Karlton]
If data changes too often or in too complex ways, you need too much logic and possibly database hits to make it worthwhile.
Beyond that, as soon as it doesn't fit in RAM, performance will plummet.