I haven't really used redis much so curious to hear your perspective - this seems the opposite to me? A materialised view is just taking the data I already have and rendering it in a different way to speed up my access patterns. It's easy for me to understand where its all coming from, and it's all directly mapped back to the source data so if things change I can easily understand why it might break etc.

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?

Those examples are all equally difficult to reason about. Cache invalidation is equivalent to refreshing a materialized view, and UNLOGGED tables bring about new and exciting ways lose data.

Because it's less moving pieces to only have one bit of state to think about.

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.