Why do I need to push Postgres to its limits before using a different solution? Throwing a hosted Redis in front of some hot-path API calls is very straightforward and easier to reason about than materialized views or UNLOGGED tables.
You already have a connection string to your database with a password or authn/z with your cloud provider. If this is a "serious" application, you have backups, monitoring, user roles, pgbouncer, partitioning, and other Postgres-specific things to think about. With just a little bit of care, you can make whatever queries you are running fast enough to not need redis.
But ok, you think adding redis is going to solve your performance problem because you can just cache API responses in redis instead of hitting the DB. Maybe, but now you have to think about cache invalidation, eviction behavior, sizing the redis instance, another set of authn/z roles to think about, and of course more cost.
I realize we're speaking past each other, but IME Postgres will work well into the terabyte range and if you can't tune your database setup for performance then reaching for cache is a form of premature optimization.
For redis, it seems there's no "out of the box" way to take some data from my DB and cache it. It seems it needs to be hand rolled per query you're optimising, you lose any structural link to the source data (redis doesn't know about my table structure), and now I have another service I need to worry about. Or is it much easier nowadays than I am thinking?
cached_val = redis.get(MY_CACHED_KEY);
if (cached_val) return cached_val;
db_val = pg.select(...);
redis.set(MY_CACHED_KEY, db_val, ttl=60s);
return db_val;
Imo this is much easier to grok/reason about than materialized views, and has the added benefits of: it is much faster than your db query if cache is set, there is some amount of robustness if your db is temporarily unavailable, you don't need to worry about "is my cache being backed up and costing me money because its part of my persistent db", etc
Basically, the point is: if you are using some hyperscaler to host your stuff (most people), then you already have trivial access to other services beyond postgres, and shoving everything into postgres might not actually be easier than using things that were purpose-built for your problems-that-are-not-actually-the-shape-of-a-persistent-relational-db.
Fwiw I love Postgres.