> But the bar should be high: only after pushing Postgres to its limits, documenting why it was insufficient, and accepting the operational cost of the alternative
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.
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?
Hot paths are basically never entirely comprised of your db query directly – they are part of some business logic contained in a backend service. So: you identify that "this API route is called a lot, but doesn't always need the exact data at time of call", and then you do this:
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.
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.
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.