Database read scaling: when caching isn't enough

even with extensive redis caching, our postgresql read replicas are still frequently hitting 80% cpu or more, causing elevated query latencies. the issue isn't hot keys or easily cacheable data, but rather the 'long tail' of uncacheable, complex queries: think highly filtered search results, paginated and sorted lists, personalized dashboards. these queries often hit multiple tables and can't be efficiently served from a simple key-value cache. we're exploring options like elasticsearch for search, materialized views for some reporting, and even a cqrs-lite pattern to separate read and write models. when do you decide that simply adding more read replicas or tuning postgres isn't enough, and you need to introduce fundamentally different data stores or architectural patterns for read scaling? what were your biggest wins in these situations?