MVs vs application cache (Redis): decision matrix with quantitative criteria

Lesson overview

So far you've learned to implement materialized views. But in production not every decision is "MV yes or no" — many times it competes with an application cache (Redis, Memcached) or with dedicated analytics services (ClickHouse, BigQuery). Choosing the wrong tool leads to expensive refactors: an MV where Redis belonged means unacceptable staleness; a Redis where an MV belonged means invalidation complexity that never ends.

This lesson trains you to decide between the three options (MV, Redis, direct query) with quantitative criteria, not intuition. The matrix covers: target latency, key cardinality, compute complexity, retention, staleness tolerance, operational cost, and consistency requirements. Each criterion has numeric thresholds to avoid ambiguity.

By the end you'll be able to receive a feature brief and decide in under 2 minutes which of the three tools applies, justifying it to your lead with concrete criteria. You'll also know when the right answer is "none of the three — we need something else" (e.g. streaming materializations with Materialize/Flink for extreme cases).

Mental model: three tools for three different problems

The three options sound similar ("store precomputed results to speed up reads") but solve different problems.

┌──────────────────────────────────────────────────────────────────┐
│  MATERIALIZED VIEW (PostgreSQL)                                  │
│                                                                  │
│  Problem it solves: "I have an expensive query that repeats,     │
│  the data tolerates staleness, I want to stay in the DB."        │
│                                                                  │
│  Characteristics:                                               │
│  - Stored in PostgreSQL (same engine as your tables)            │
│  - Batch refresh (every N minutes/hours)                        │
│  - Data consistent with the refresh snapshot                    │
│  - No extra service                                             │
│  - Indexable, joinable with normal tables                       │
│                                                                  │
│  Cost: refresh compute + disk                                   │
│  Typical latency: 1-50 ms (lookup over an indexed table)        │
└──────────────────────────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────────────────┐
│  REDIS (application cache)                                       │
│                                                                  │
│  Problem it solves: "I need ultra-fast lookup by key,           │
│  with automatic TTL, volatile data, high cardinality."          │
│                                                                  │
│  Characteristics:                                               │
│  - Separate service (in-memory)                                 │
│  - Access by key (lookup, not complex queries)                  │
│  - Native TTL (data expires on its own)                         │
│  - High cardinality (millions of possible keys)                 │
│  - Manual invalidation or by TTL                                │
│                                                                  │
│  Cost: RAM + service operations + invalidation                  │
│         complexity                                              │
│  Typical latency: 0.5-5 ms (in-memory lookup)                   │
└──────────────────────────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────────────────┐
│  DIRECT QUERY (over base tables)                                 │
│                                                                  │
│  Problem it solves: "the data must be fresh, the query is        │
│  fast with appropriate indexes."                                │
│                                                                  │
│  Characteristics:                                               │
│  - Direct SELECT against tables with indexes                    │
│  - Data always from the exact moment                            │
│  - No extra compute, no staleness                               │
│  - Latency depends on the query and the indexes                 │
│                                                                  │
│  Cost: compute on every request                                 │
│  Typical latency: 1-100 ms (depends on complexity)              │
└──────────────────────────────────────────────────────────────────┘

Three ideas to internalize:

1. MV vs Redis isn't a direct competition — they're complementary tools for different problems. When someone poses "do we use MV or Redis?", the right question is usually "what problem are we solving?" The answer determines the tool.

2. Redis is for lookup by key, not for complex queries. If your question is "give me all the X where Y," Redis isn't the natural tool — that's DB territory. Redis shines with GET key, not with SELECT WHERE.

3. MV is for expensive compute + staleness OK + frequent reads. If your max staleness is minutes/hours, not seconds, MV is a candidate. If it's seconds or you require event-driven invalidation, Redis is a candidate.

The complete decision matrix

When MV wins: 5 explicit patterns

Pattern 1: dashboards with expensive queries + acceptable staleness

Example: "Top 10 most-viewed posts this week."

• Expensive underlying query (~4s).
• High frequency (200 loads/h).
• Staleness OK (1 hour).

Why MV: the compute cost is concentrated in 1 refresh/h instead of 200 runs/h. No extra service. The data is still SQL-queryable, indexable, joinable. Covered in lesson 06.

Pattern 2: alternative to slow COUNT(*) over massive tables

Example: "Total events in the last 30 days" over a 50M-row table.

• A direct COUNT(*) takes 15s.
• Queried 100/h (in headers, sidebars).
• Staleness of 1h acceptable.

Why MV: it's the explicit pattern from guide #12 mentioned in module 5. Without an MV, that's 100 × 15s = 25 min of DB time/h just for the counter. With an MV, it's 1 refresh × 15s + 100 × 0.001s = 15s/h. 99.99% reduction.

Why not Redis: Redis could cache the counter (SET total_events_30d 8421421), but managing invalidation is complex (when does it invalidate? on inserting a new event? that impacts the hot path). An MV with hourly refresh is operationally simpler.

Pattern 3: precomputed joins of recurring use

Example: "post + author + category + comment count + view count" used across many endpoints.

• Join of 4 tables + 2 aggregations.
• Every dashboard endpoint uses it.
• Replicating the join in each endpoint = duplication + slowness.

Why MV: a single mv_post_summary MV with all the necessary columns, indexed by category, author, date. Each endpoint does SELECT FROM mv_post_summary WHERE .... Hourly refresh keeps the data up to date.

Why not Redis: the endpoints filter by different dimensions (by category, by author, by date). Redis would require caching each subset separately or reconstructing results from the base tables — it loses the advantage.

Pattern 4: batch / nightly reports

Example: "Monthly revenue report by country" generated on day 1 at 3 AM.

• Very expensive query (12s+).
• Very low frequency (once/month).
• Multiple recipients consume the same result.

Why MV: refresh on day 1 at 2 AM (before it's sent). The emails consume the MV → everyone sees the same consistent snapshot. No need to re-run the query 50 times for 50 recipients.

Why not Redis: it could be used, but it's overkill. An MV with REFRESH FULL (no traffic at 2 AM) covers the case without adding a service.

Pattern 5: stable rolling windows

Example: "Posts published per month over the last 12 months" for a trend chart.

• Data changes little hour to hour.
• Low cardinality (12 rows).
• Target latency: <100ms.

Why MV: refresh every 6h. Instant lookup (12 indexed rows). Minimal disk.

Why not Redis: it could be valid too, but MV is more natural — the data comes from SQL, is SQL-queryable, adds no service. If you already have Redis for other things, both options are reasonable.

When Redis wins: 5 explicit patterns

Pattern 1: ultra-fast lookup by user_id (sessions, tokens)

Example: JWT/session token validation on every request.

• Lookup by key (GET session:<token>).
• Target latency: <2ms.
• Frequency: every request (millions/day).
• TTL: 24h.

Why Redis: in-memory lookup is <1ms. Native TTL removes expired sessions without a cleanup job. High cardinality (millions of active tokens) managed efficiently.

Why not MV: an MV of "all active sessions" has high cardinality and is very volatile (each login/logout changes it). The refresh would be almost continuous. It fails all the MV criteria.

Pattern 2: rate limiting

Example: "maximum 100 requests per user per hour."

• Operation: INCR counter:<user_id>:<hour>.
• Target latency: <1ms (in the critical path of every request).
• TTL: 1h (the count expires with the window).

Why Redis: atomic INCR in memory, minimal latency. Native TTL matches the rate limit window. No lock contention in the DB.

Why not MV: an MV doesn't support direct INSERT/UPDATE. Maintaining a rate limit counter in a table with UPSERT is possible but creates contention and is slower than Redis.

Pattern 3: HTTP response cache by URL

Example: cache the JSON response of GET /api/posts/123 for 5 minutes.

• Key: hash of the URL + query params.
• Cardinality: high (any combinable URL).
• Staleness: minutes OK.
• TTL: configurable.

Why Redis: pure key/value. Automatic TTL. Invalidation by DEL when the post is updated. No need to model it as a table.

Why not MV: modeling "cached HTTP responses" as an MV makes no sense — there's no SELECT that generates them, they're application blobs.

Pattern 4: pub/sub or coordination between services

Example: notify multiple instances when a post is published so they clear their local cache.

• Operation: PUBLISH posts:published <post_id>.
• Target latency: ms.
• Multiple consumers.

Why Redis: native pub/sub, multi-consumer, low-latency. A Redis-specific use case (it's not a cache, it's communication).

Why not MV: completely out of scope — an MV isn't a tool for inter-process communication.

Pattern 5: top-N with aggressive write-through

Example: "player leaderboard by points in real time."

• Every time a player earns points, the leaderboard is updated.
• Target latency: <5ms to read the top 100.
• Cardinality: 1M players.
• Staleness: zero (real-time changes).

Why Redis: sorted sets (ZADD, ZREVRANGE) are perfect for leaderboards. Ultra-fast in-memory operations. No batch refresh, the data is always fresh.

Why not MV: an MV with a refresh every 5 minutes doesn't meet "real time." If you refresh every 5s, the cost is prohibitive.

Decision tree: MV, Redis, or direct query?

                  ┌─────────────────────────────────────┐
                  │ What's the latency SLA?             │
                  └──────────────┬──────────────────────┘
                                 │
              ┌──────────────────┼──────────────────┐
              │                  │                  │
          <2ms│           2-50ms │           >50ms │
              ▼                  ▼                  ▼
      ┌───────────┐    ┌─────────────────┐  ┌──────────────────┐
      │ REDIS     │    │ Tolerates       │  │ Probable: direct │
      │ (in-memory│    │ staleness?      │  │ query with       │
      │  or table │    └────────┬────────┘  │ indexes (guide   │
      │  with     │             │           │ #12)             │
      │  precom-   │     ┌──────┴──────┐    └──────────────────┘
      │  puted     │     │             │
      │  UPSERT)   │   No │          Yes │
      └───────────┘      ▼             ▼
                ┌──────────────┐  ┌─────────────────────────┐
                │ Direct query │  │ Expensive compute       │
                │ with indexes │  │ (joins, agg, group by)? │
                └──────────────┘  └────────────┬────────────┘
                                               │
                                  ┌────────────┴────────────┐
                                  │                         │
                                No│                      Yes│
                                  ▼                         ▼
                          ┌──────────────┐         ┌────────────────┐
                          │ Direct query │         │ High           │
                          │ (it's cheap) │         │ cardinality +  │
                          └──────────────┘         │ lookup by key? │
                                                   └───────┬────────┘
                                                           │
                                            ┌──────────────┴──────────────┐
                                            │                             │
                                          No│                          Yes │
                                            ▼                             ▼
                                  ┌──────────────────┐            ┌─────────────┐
                                  │ MATERIALIZED VIEW│            │ REDIS       │
                                  │                  │            │             │
                                  └──────────────────┘            └─────────────┘

Cases where the right answer is "none of the three":

• Latency <1ms + absolutely fresh data + high cardinality → you need a streaming materialization service (Materialize, Flink, Druid). Out of scope.
• Complex analytics over billions of rows → ClickHouse, BigQuery, Snowflake. Out of scope.
• Semantic search with embeddings → vector DB (pgvector covered briefly in module 7, or Pinecone/Weaviate). Out of scope.

Comparative analysis: 4 contrasted cases

Case A: "Top 10 most-viewed posts this week"

Decision: MATERIALIZED VIEW. Covered in lesson 06.

Case B: JWT validation on every request

Decision: REDIS. MV doesn't apply (cardinality + zero staleness + <2ms latency).

Case C: "Total comments on post X" shown below each post

Decision: it depends.

• If you have ~5K posts/h viewed and 1 query <5ms each = 25s/h DB time. Acceptable. Direct query with an index.
• If you have ~500K posts/h viewed = 2500s/h DB time. Too much. Cache in Redis with 60s TTL or MV with a refresh every minute.
• If the counters can acceptably be a few minutes out of date → MV or Redis with TTL.

Subtle point: this is the case that confuses most. The answer depends on the real volume, not on "it's a counter, it goes in Redis." Start with a direct query + index. Only change if the metrics justify it.

Case D: global gamification leaderboard, top 100 players by points

Decision: REDIS with sorted sets. An MV doesn't meet the case because "real time" doesn't accept a refresh every N minutes.

Hybrid alternative: Redis as the source of truth for the leaderboard, PostgreSQL for history/audit. A common pattern.

Comparative operational cost

Deciding isn't just "which tool is technically fit" — it's "what does it cost to operate."

MV: low operational cost

• Service: you already have PostgreSQL. Zero new service.
• Monitoring: uses the same Postgres monitoring (p_stat_user_tables, slow query log).
• Backups: included in the Postgres backup.
• HA: part of Postgres replication.
• Team skills: they already know Postgres.
• Typical failure: the refresh takes longer than expected. Detectable with logs.

Redis: medium-high operational cost

• Service: Redis separately (install, configure, monitor).
• Monitoring: Redis-specific (memory usage, hit ratio, connection pool).
• Backups: RDB/AOF separately.
• HA: Redis Sentinel or Cluster (non-trivial configuration).
• Team skills: requires someone who knows Redis ops.
• Typical failures: OOM if data grows without TTL, TTL expiration causing massive misses, exhausted connections.

Direct query: zero operational cost

• Service: you already have PostgreSQL.
• Nothing additional.
• Typical failure: slow queries. Solvable with indexes (guide #12).

Implication: if the difference in latency/functionality between options is marginal, choose the one with the lowest operational cost. Adding Redis "just because" when MV or a direct query works is an expensive decision that you pay for over years.

Why does this matter on the job?

1. The wrong choice costs expensive refactors. Migrating from Redis to MV (or vice versa) means rewriting endpoints, changing invalidation logic, rethinking monitoring. If the initial choice was by intuition and not by matrix, the cost is discovered late.

2. "Let's add Redis" is the most common and worst-justified architectural proposal. Teams add Redis to the stack for any "we need cache" without analyzing whether an MV is enough. The matrix gives you the argument to say "this case is MV, we don't need a new service" — saving operations over years.

3. The conversation with the lead becomes concrete. "Why not Redis?" is answered with "target latency is 50ms, not 2ms; acceptable staleness is 1h; cardinality is low. Three criteria point to MV. Redis adds operations with no benefit." It's defensible and reproducible.

4. Distinguishing "cache" from "precomputed lookup" is senior vocabulary. Many confuse "caching" with "precomputing." Cache = speed up lookups that already exist (Redis, HTTP cache). Precomputed = materialize compute results (MV). Knowing the difference positions you.

5. Identifying the "none of the three" case avoids losing weeks. If your requirement is "<1ms latency + real-time data + high cardinality + complex compute," none of the three is enough. It's a signal that you need another architecture (streaming materialization, specialized in-memory DB). Recognizing it early avoids months of failed prototyping.

Pitfalls and common mistakes

Mistake 1 (conceptual): "Redis is always faster than PostgreSQL"

Symptom: someone argues "let's use Redis because it's in-memory and always faster."

Why it happens: a widespread confusion. Redis is faster for lookup by key. PostgreSQL with an appropriate index can be just as fast or faster for complex SQL queries.

How to tell: measure empirically. For SELECT * FROM mv_x WHERE pk = $1 with an index, PostgreSQL is ~1ms. Redis is ~0.5ms. The difference is marginal and doesn't justify adding a service.

How to fix: latency depends on the query and the storage. For a direct lookup by PK with an index, both are sub-ms. The choice shouldn't be by raw speed but by the other criteria.

Mistake 2 (practical): caching queries in Redis without an invalidation strategy

Symptom: the team adds a Redis cache for "post detail" with a 24h TTL. When the post is updated, users keep seeing the old version for 24h.

Why it happens: TTL is passive invalidation. Without explicit invalidation on events (when the post is updated, DEL cache:post:<id>), the cache stays old.

How to tell: observe the lag between changes in the DB and what users see.

How to fix:

• For data that changes, add DEL on every UPDATE/DELETE.
• Or reduce the TTL to something acceptable as max staleness.
• Or use an MV with hourly refresh and accept that a post-update takes up to 1h to show.

Mistake 3 (conceptual): MV for volatile data + high cardinality

Symptom: the team creates an MV of "each user's shopping cart." Every change in the cart implies a refresh. The MV is always stale.

Why it happens: confusion about what's "expensive to compute." The per-user cart isn't expensive — it's just SELECT * FROM cart_items WHERE user_id = $1. It's not an MV candidate.

How to tell: if the "compute" is just a lookup by key, it's not MV territory.

How to fix: direct query with an index, or Redis cache with event-based invalidation.

Mistake 4 (operational): choosing Redis without a team to operate it

Symptom: a team of 3 backend devs with no DevOps decides to add Redis Cluster for HA. 6 months later, the cluster fails, nobody knows how to diagnose it, and the on-call suffers.

Why it happens: deciding by features without considering operational cost. Redis HA isn't trivial.

How to tell: is there anyone on the team who knows how to restore Redis from RDB in production? Do they know what to do if the cluster suffers split-brain? If not, Redis HA in production is a risk.

How to fix: prefer managed Redis (AWS ElastiCache, Redis Cloud) if you decide to use it. Or prefer MV (no additional operations) if the features meet the need.

Mistake 5 (conceptual): thinking "MV vs Redis" is a global binary decision for the app

Symptom: "Let's decide: do we use MVs or Redis for everything?"

Why it happens: a false dichotomy. The tools coexist. A real app can have:

• MVs for dashboards (lesson 06).
• Redis for sessions, rate limiting, pub/sub.
• Direct query for general CRUD.

How to tell: the right question isn't "which tool for the app?" but "which tool for this specific endpoint?"

How to fix: decide case by case applying the matrix. The final architecture has multiple tools, each in its niche.

Exercises

Exercise 1: apply the matrix to 5 cases

For each case, decide MV/Redis/direct query and justify with 2-3 criteria from the matrix.

1. Show the logged-in user's username in the navbar of every page.
2. Top 10 most popular posts of the last month on the homepage (50K visits/h).
3. Counter of "X users viewing this post right now" (in real time).
4. List the 20 categories ordered by number of posts (changes little, admin dashboard).
5. Free keyword search results in real time.

Exercise 2: rebut a poorly founded proposal

Your colleague proposes:

"Let's put the whole product catalog into Redis to speed up the e-commerce's paginated list. We have 50K products and the catalog page takes 200ms to load."

Apply the matrix to evaluate whether it makes sense. Propose an alternative if appropriate.

CREATE INDEX idx_products_category_price ON products (category_id, price);
CREATE INDEX idx_products_published ON products (published_at DESC) WHERE active = true;

CREATE MATERIALIZED VIEW mv_active_products AS
SELECT id, title, slug, category_id, price, image_url, stock, ...
FROM products WHERE active = true AND stock > 0;

CREATE UNIQUE INDEX ... ON mv_active_products (id);
CREATE INDEX ... ON mv_active_products (category_id, price);

-- Refresh every 5 min (active products change little)

Exercise 3: identify the "none of the three" case

For each case, decide whether MV/Redis/direct query fits, or whether it's a "we need another architecture" case.

1. "Real-time activity" dashboard for the network operations center: latency <100ms, cardinality 100 servers, metrics every second.
2. Search with semantic embeddings: "find articles similar to this text."
3. Monthly reports over 2 billion historical events.
4. Top 10 most popular searches in the last hour (refresh every 5 minutes OK).

Exercise 4: document the architectural decision

For your current app (or a hypothetical one), choose 3 endpoints/dashboards. For each one, document: query, matrix criteria, decision, alternatives considered, justification. Result: a short document that serves as an ADR (Architecture Decision Record).

# ADR-0042: Cache/MV decisions for the blog dashboard

## Context

We're implementing the internal metrics dashboard. 4 panels that query
analytical data. We decide which tool for each one.

## Decision 1: Weekly top posts

**Underlying query:** join posts + views + aggregation last 7 days.

**Criteria:**
- Target latency: <100ms
- Acceptable staleness: 1h
- Cardinality: 10 rows (LIMIT 10)
- Compute: expensive (~4s without precomputation)
- Frequency: 200 loads/h

**Decision:** MATERIALIZED VIEW `mv_top_posts_weekly`, hourly refresh.

**Alternatives considered:**
- Redis: discarded. There's no lookup by key, it's an aggregation.
- Direct query: discarded. 200/h × 4s = 13min DB time/h, unacceptable.

## Decision 2: Session token validation (endpoint /me)

**Underlying query:** lookup of an active session token.

**Criteria:**
- Target latency: <2ms
- Acceptable staleness: zero (logout must invalidate)
- Cardinality: high (~500K active tokens)
- Frequency: every request (~100K/h)

**Decision:** REDIS with TTL = session duration.

**Alternatives:**
- Direct query: discarded. 100K/h × 5ms = 8min DB time/h, high load.
- MV: discarded. High cardinality + zero staleness + high write frequency.

## Decision 3: Paginated post listing (public)

**Underlying query:** SELECT * FROM posts WHERE category_id = X
ORDER BY published_at DESC LIMIT 20.

**Criteria:**
- Target latency: <100ms
- Staleness: 0 (a just-published post must appear)
- Cardinality: any category/page
- Frequency: high

**Decision:** Direct query with a composite index (category_id, published_at DESC).

**Alternatives:**
- MV: discarded. High cardinality, zero staleness required.
- HTTP cache by category: viable, but not needed yet (latency already <30ms with an index).

## Summary

3 different decisions for 3 different problems. Each tool in its niche.
Total extra services added: 1 (Redis). MVs add no service (part of Postgres).

Summary and next step

In this lesson you built the decision framework between the three tools:

• MATERIALIZED VIEW wins when: expensive query + acceptable staleness + low-medium cardinality + SQL access type + zero extra service desired. Typical cases: dashboards, reports, alternative to slow COUNT(*).

• REDIS wins when: latency <2ms + lookup by key + high cardinality + native TTL + volatile data. Typical cases: sessions/tokens, rate limiting, real-time leaderboards, HTTP response cache, pub/sub.

• DIRECT QUERY wins when: data must be fresh + the query is reasonably fast with appropriate indexes. Typical cases: lookups by PK, paginated lists with an index, searches with FTS.

• The wrong choice costs expensive refactors. Migrating between tools means rewriting endpoints, changing invalidation, rethinking monitoring. Applying the matrix from day 1 avoids this.

• Operational cost matters. If the technical difference between options is marginal, choose the one with the lowest operational cost. Adding Redis "just because" is an expensive decision you pay for over years.

• The "none of the three" cases exist: streaming materializations, vector DBs, warehouses. Recognizing them early avoids forcing inadequate solutions.

Before moving on, you should be able to:

• Receive a feature brief and apply the matrix in <2 minutes.
• Justify the decision with 2-3 concrete criteria to the lead.
• Rebut poorly founded proposals with technical vocabulary.
• Identify cases that require tools outside the trio.

Next lesson — the module project: blog dashboard with MVs. Now that you have the decision framework and the technical implementation, lesson 08 is the capstone project. You're going to deliver: 4 MVs created with migrations, a scheduled refresh cron, FastAPI endpoints with last_updated, a frontend banner (mock), a documented before/after benchmark, and an ARCHITECTURE.md justifying the decisions (why MV and not Redis for each case). It's the piece that's reused directly in the final project of module 8.

Resources

1. Redis — Use Cases — official reference on patterns where Redis shines.
2. Crunchy Data — When to Use Redis vs PostgreSQL — deep comparison with cases.
3. Lukas Fittl (pganalyze) — Materialized Views vs Caching — analysis of when each one wins.
4. Materialize — Streaming Materializations — for "none of the three" cases where you need real time with SQL compute.
5. Hashrocket — Caching Patterns — taxonomy of caching patterns.
6. Distributed Systems Reading List — Caching — classic references on caching and consistency.
7. Martin Kleppmann — Designing Data-Intensive Applications — chapters on derived data and consistency. Required reading for senior engineers.

Module 5 — Advanced PostgreSQL for Backend Guide

Next lesson: The module project — blog dashboard with MVs end-to-end.