Views vs materialized views: when each one

Lesson overview

This is the module's critical framing. Before learning to create materialized views, refresh them concurrently, or index them, you need to know when to choose them. The most expensive operational mistake isn't "I implemented an MV wrong" — it's "I used an MV when I should have used a VIEW" or "I used a VIEW when I should have used an MV." The consequences of each mistake are different: an MV where it doesn't apply wastes disk and produces stale data; a VIEW where it doesn't apply collapses the database under load.

This lesson trains you to read a use case (a dashboard, an endpoint, a report) and decide which of the two structures fits. The technical difference is simple — a VIEW is a named SELECT, it doesn't store data; a MATERIALIZED VIEW stores the result physically and refreshes on demand. The operational difference is enormous: a VIEW runs on every query (recurring latency and cost); a MATERIALIZED VIEW runs only when you refresh it (low read latency, cost concentrated in the refresh).

By the end you'll be able to receive a description of a new endpoint and decide, with quantitative criteria, whether to expose it with a VIEW, a MATERIALIZED VIEW, or a direct SELECT in the service code.

Mental model: the difference between a recipe and a prepared dish

Think of VIEW and MATERIALIZED VIEW as two ways of serving food.

A VIEW is a recipe pinned up in the kitchen. When someone orders "Caesar salad," the cook reads the recipe and prepares the salad from scratch: washes lettuce, grates cheese, mixes dressing. Every order means the full work. If 100 people come in an hour, that's 100 salads prepared from scratch. The upside: each salad is made with the freshest ingredients of the moment. The downside: if the dish is complex, the kitchen collapses when the restaurant fills up.

A MATERIALIZED VIEW is a precooked, refrigerated dish. The chef prepared 50 salads at 6 AM. When someone orders "Caesar salad," the waiter takes one from the fridge and serves it. Speed: instant. The downside: the salads are from 6 AM, not from the moment of the order. If the customer wants "a salad with the lettuce that arrived 5 minutes ago," the MV doesn't serve them. And someone has to prepare new salads periodically (refresh) — otherwise, by 4 PM there are none left or they're old.

┌──────────────────────────────────────────────────────────────────┐
│                      VIEW                                        │
│                                                                  │
│  CREATE VIEW v_top_posts AS                                      │
│      SELECT post_id, count(*) AS views                           │
│      FROM views WHERE created_at > NOW() - INTERVAL '7 days'     │
│      GROUP BY post_id ORDER BY count(*) DESC LIMIT 10;           │
│                                                                  │
│  Request 1 → runs the SELECT (4.2 seconds)                       │
│  Request 2 → runs the SELECT (4.2 seconds)                       │
│  Request 3 → runs the SELECT (4.2 seconds)                       │
│  ...                                                             │
│  Request 200 → runs the SELECT (4.2 seconds)                     │
│                                                                  │
│  ✅ Data always fresh                                            │
│  ❌ Recurring compute cost: 200 × 4.2s = 14 minutes             │
└──────────────────────────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────────────────┐
│                  MATERIALIZED VIEW                               │
│                                                                  │
│  CREATE MATERIALIZED VIEW mv_top_posts AS                        │
│      SELECT post_id, count(*) AS views                           │
│      FROM views WHERE created_at > NOW() - INTERVAL '7 days'     │
│      GROUP BY post_id ORDER BY count(*) DESC LIMIT 10;           │
│                                                                  │
│  REFRESH MATERIALIZED VIEW mv_top_posts;  (runs every hour)      │
│                                                                  │
│  Request 1 → SELECT * FROM mv_top_posts (8 milliseconds)         │
│  Request 2 → SELECT * FROM mv_top_posts (8 milliseconds)         │
│  ...                                                             │
│  Request 200 → SELECT * FROM mv_top_posts (8 milliseconds)       │
│                                                                  │
│  ✅ Constant latency: 200 × 8ms = 1.6 seconds                    │
│  ❌ Data up to 1 hour of staleness                              │
│  ❌ Cost concentrated in the refresh: 1 × 4.2s per hour = 4.2s/h │
└──────────────────────────────────────────────────────────────────┘

Three ideas to internalize:

1. VIEW doesn't store data. MATERIALIZED VIEW does. The difference is on disk, not in syntax. A VIEW is pure metadata (a named SELECT that the planner expands every time). A MATERIALIZED VIEW is a table with real data that takes up the space of the SELECT result.

2. The trade-off is always the same: freshness vs latency. There's no way to have both without paying somewhere. If you need data at the exact instant, pay the compute cost every time (VIEW). If you tolerate staleness, pay the cost only once per refresh and get cheap reads (MATERIALIZED VIEW). A third option — "fresh data fast" — demands an external service (Redis cache, incremental compute like Materialize), the topic of lesson 07.

3. MATERIALIZED VIEW needs an explicit refresh strategy. Unlike a VIEW, which "is always up to date by construction," an MV keeps the data from the last refresh until you update it. Without a cron, a trigger, or a call from the app, the MV ages without limit.

Side-by-side comparison

When to choose VIEW

A classic VIEW wins in these cases:

1. Schema abstraction with no compute cost

The query is cheap (sub-millisecond or a few ms) and you need to expose a "logical view" different from the physical tables. Example: you have users with columns first_name, last_name and you want to expose full_name:

CREATE VIEW v_users AS
SELECT
    id,
    email,
    first_name || ' ' || last_name AS full_name,
    created_at
FROM users;

The SELECT is trivial (string concatenation). No aggregation, no massive joins. A VIEW is perfect: zero overhead, always-fresh data, simpler client code.

2. Data must be absolutely fresh

The user expects to see "what happened 30 seconds ago." Examples:

• Bank account balance. The user makes a transfer and refreshes the page. If they see a "stale" balance from 5 minutes ago not reflecting the transfer, that's a serious bug.
• Shopping cart. Adding/removing items must be seen instantly.
• Read/unread notifications. If the user marks one as read, the counter must drop immediately.
• Real-time operations dashboard (NOC, monitoring). Any delay >5s can translate into a broken SLA.

In these cases, neither MV nor Redis cache applies (unless they're caches with very strict write-through/invalidation — out of scope for this module). A live query over the base tables is the correct choice.

3. High cardinality + scattered queries

When the nature of the problem implies a universe of distinct queries that can't be precomputed. Example: free product search by arbitrary keyword. Each user searches for different things. It makes no sense to materialize "search results for all possible queries." Here the solution is indexing (B-tree, GIN for FTS) on the base tables, not MVs.

4. Security/isolation rules (RLS-friendly)

VIEWs respect the Row-Level Security policies of the base tables by default. MVs don't — the refresh runs them as a superuser and "breaks" per-tenant isolation unless you design for it explicitly. For multi-tenancy with RLS (covered in guide #13), VIEWs are safer out-of-the-box.

When to choose MATERIALIZED VIEW

A MATERIALIZED VIEW wins in these cases:

1. Dashboards and reports with expensive queries

The original query takes seconds (joins, aggregations, ORDER BY count(*), scanning millions of rows) and is queried many times per hour. Examples:

• "Top 10 most-viewed posts this week" — 4-second query, dashboard loaded 200 times/hour.
• "Total comments per month over the last 12 months" — 3-second query, chart used across 50 different endpoints.
• "Categories with the most activity" — query with triple joins, repeated in the sidebar of every page.

The user tolerates staleness (15 minutes to 1 hour typical). MV is the idiomatic answer.

2. Slow COUNT(*) over massive tables (explicit alternative)

In guide #12 you learned that SELECT count(*) FROM events over 50M rows takes seconds because PostgreSQL doesn't keep a counter in memory. The explicit pattern:

-- Before (15 seconds):
SELECT count(*) FROM events;

-- After (8 ms):
CREATE MATERIALIZED VIEW mv_total_events AS
SELECT count(*) AS total FROM events;

-- Refresh every hour via cron:
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_total_events;
-- (requires a unique index — lesson 04)

-- Query:
SELECT total FROM mv_total_events;

The cost is concentrated in the refresh (once per hour) instead of every request (200/hour).

3. Precomputed joins of recurring use

You have a complex join (3-4 tables) that repeats across many endpoints and always returns the same denormalized structure. Example: a blog dashboard needs "post + author + category + comment count" on every panel. Instead of repeating the join:

CREATE MATERIALIZED VIEW mv_post_summary AS
SELECT
    p.id, p.title, p.slug,
    u.username AS author,
    c.name AS category,
    COALESCE(cmt.count, 0) AS comment_count,
    p.published_at
FROM posts p
JOIN users u ON p.author_id = u.id
JOIN categories c ON p.category_id = c.id
LEFT JOIN (
    SELECT post_id, count(*) AS count
    FROM comments
    GROUP BY post_id
) cmt ON cmt.post_id = p.id
WHERE p.published_at IS NOT NULL;

Each dashboard endpoint does SELECT FROM mv_post_summary WHERE ... with appropriate indexes. A single place to maintain, cheap reads.

4. Analytical data with rolling windows

"Top X of Y in the last N units of time" is the case par excellence. The window is moving, the compute is expensive, users tolerate refresh every so often. An MV refreshed every hour covers 95% of trend dashboards.

Decision tree: VIEW, MATERIALIZED VIEW, or direct SELECT?

                    ┌──────────────────────────────┐
                    │ Is the query expensive       │
                    │ (>500ms typical)?            │
                    └──────────┬───────────────────┘
                               │
                ┌──────────────┴──────────────┐
                │                             │
              No│                         Yes │
                ▼                             ▼
      ┌──────────────────┐         ┌────────────────────────────┐
      │ Do you need      │         │ Does the data tolerate     │
      │ schema           │         │ staleness (>5 minutes)?    │
      │ abstraction?     │         └──────────┬─────────────────┘
      └────────┬─────────┘                    │
               │                  ┌───────────┴───────────┐
        ┌──────┴──────┐           │                       │
        │             │         No│                    Yes │
        │No       Yes │           ▼                       ▼
        ▼             ▼  ┌──────────────────┐   ┌──────────────────┐
   Direct SELECT   VIEW   │ App cache        │   │ MATERIALIZED     │
   (in code)              │ (Redis, lesson   │   │ VIEW             │
                          │ 07) or table     │   │ + refresh every  │
                          │ with event-      │   │ N minutes/hours  │
                          │ driven UPSERT    │   │                  │
                          └──────────────────┘   └──────────────────┘

Notes on the tree:

• "Expensive" is relative to your SLA. If your p95 target is 50ms and a query takes 200ms, it's expensive for you.
• "Tolerates staleness" demands a conversation with product. Don't decide it alone.
• The "doesn't tolerate staleness + expensive query" branch is the trickiest — it usually ends in a Redis cache with explicit invalidation or in a streaming analytics service (Materialize, Flink). Covered in lesson 07.

Contrasted cases: 4 examples from the blog

To anchor the mental model, let's classify 4 typical blog endpoints.

Case A: "Posts published per month over the last year" (trend chart)

• Underlying query: SELECT date_trunc('month', published_at) AS month, count(*) FROM posts WHERE published_at > NOW() - INTERVAL '12 months' GROUP BY 1 ORDER BY 1;
• Query cost: ~800ms (250K posts, aggregation with date_trunc).
• Query frequency: 50 times/hour (sidebar and dashboard).
• Staleness tolerance: high — the monthly trend chart doesn't change from one minute to the next.
• Decision: ✅ MATERIALIZED VIEW refreshed every 6 hours.
• Why: expensive query + high frequency + tolerated staleness.

Case B: "Current user's email in the header"

• Underlying query: SELECT email FROM users WHERE id = $1;
• Query cost: <1ms (lookup by PK).
• Query frequency: every request (all pages).
• Staleness tolerance: zero — if the user changes their email, they must see it immediately.
• Decision: ✅ Direct SELECT in the code (or cache with write-through, out of scope).
• Why: trivial query + data must be fresh.

Case C: "List of posts by author X" (profile view)

• Underlying query: SELECT id, title, slug, published_at FROM posts WHERE author_id = $1 AND published_at IS NOT NULL ORDER BY published_at DESC LIMIT 20;
• Query cost: ~5-15ms (with an index on (author_id, published_at)).
• Query frequency: moderate (profile view, ~20/hour for a popular author).
• Staleness tolerance: low — if the author publishes a new post, it must appear on their profile immediately.
• Decision: ✅ VIEW (or direct SELECT) with an appropriate index.
• Why: cheap query + data must be fresh.

Case D: "Top 10 most active categories this week"

• Underlying query: join between posts, comments, views, categories with aggregation and ordering.
• Query cost: ~3-6 seconds (scans the week's views, multiple aggregations).
• Query frequency: 100 times/hour (homepage sidebar).
• Staleness tolerance: high — "this week" covers 7 days, the rankings don't change dramatically every 30 minutes.
• Decision: ✅ MATERIALIZED VIEW refreshed every 30 minutes to 1 hour.
• Why: very expensive query + high frequency + tolerated staleness.

Emerging pattern: the decision depends on three axes — query cost, read frequency, staleness tolerance. VIEWs win on cheap queries. MVs win when the cost is high and the tolerance exists. If the query is expensive but the tolerance is zero, you have to think about another architecture (lesson 07).

Why does this matter on the job?

1. The wrong choice costs money or costs UX. An MV where a VIEW belongs wastes disk and produces old data for no reason. A VIEW where an MV belongs saturates the database under load and users suffer latency. Knowing how to choose before implementing saves you expensive refactors.

2. It's the foundation of architectural conversations with seniority. When someone proposes "let's add Redis for this," the right question is "is this an MV case or a cache case?" Without the framing of this lesson, the answer is opinion. With the framing, it's a defensible technical decision.

3. PMs don't think in terms of VIEW/MV — they think in SLAs. "I need the dashboard to load in under 1 second and the data to be no more than 1 hour behind" is a typical product brief. Your job is to translate that into "MV refreshed every hour with an index on the filter columns." The framing of this lesson gives you the vocabulary for that translation.

4. Code review on dashboards becomes concrete. "This endpoint runs a 4-second query every time it's called, and it's called 200 times per hour — it's an MV case with hourly refresh." Without the framing, code review on dashboards is generic ("optimize the query"). With it, it's actionable.

5. Senior interviews ask exactly this. "When would you use a materialized view vs a view vs Redis?" is a classic question for senior backend roles. The answer isn't "MVs are fast" — it's the matrix you learned here.

Pitfalls and common mistakes

Mistake 1 (conceptual): thinking that VIEW "saves" data

Symptom: "I created a VIEW to speed up the query but it still takes the same time."

Why it happens: a very frequent confusion. A VIEW doesn't store data. It's a syntactic abstraction — the planner expands the VIEW and runs the underlying SELECT every time you query it. If the underlying SELECT takes 4 seconds, the VIEW takes 4 seconds on every call.

How to tell: run EXPLAIN ANALYZE SELECT * FROM my_view. You'll see the full plan of the underlying SELECT, not a fast Seq Scan on a materialized table.

How to fix: if you need to store the result to speed up reads, use a MATERIALIZED VIEW. If you only wanted schema abstraction, the VIEW is fine — but don't expect a performance improvement.

Mistake 2 (conceptual): assuming an MV refreshes automatically

Symptom: you create the MV on Monday, on Friday the data is still from Monday. "Why isn't it updating?"

Why it happens: PostgreSQL does not refresh MVs automatically. The MV stays frozen with the data from the last refresh until you run REFRESH MATERIALIZED VIEW. There's no background process updating them for you.

How to tell: run SELECT * FROM mv_top_posts ORDER BY computed_at DESC LIMIT 1 (if your MV has a refresh timestamp column). The timestamp will be from the last manual refresh.

How to fix: define an explicit refresh strategy: cron, app-driven, or trigger. Lesson 03 covers the 3 options. Without a refresh strategy, the MV is useless after a few hours.

Mistake 3 (practical): choosing an MV for data that changes super fast

Symptom: you create an MV for "real-time activity" with a refresh every 30 seconds. Each refresh takes 25 seconds. The MV spends more time refreshing than serving reads. The database is saturated.

Why it happens: MVs win when the refresh cost is amortized over many reads. If the refresh is almost as frequent as the reads, the math doesn't add up. "Real-time data with aggressive refresh" is the classic anti-pattern.

How to tell: compute refresh_cost × refresh_frequency vs query_cost × read_frequency. If the first is ≥ the second, the MV doesn't help. For "real time" (sub-minute), consider Redis with invalidation or a streaming service (lesson 07).

How to fix: redefine the SLA with product. "Do you really need a refresh every 30s or is every 5 minutes acceptable?" 5 minutes is often enough and the MV makes sense again.

Mistake 4 (conceptual): not communicating staleness to the user

Symptom: the dashboard shows "Top 10 posts" without indicating when it was computed. Users report "the ranking is wrong" when in reality it's from the previous refresh. The support team suffers.

Why it happens: MVs imply staleness by design. If the user expects "moment-in-time data" but sees "data from 47 minutes ago," the difference gets attributed to a bug.

How to tell: read support tickets about "incorrect data" on the dashboard. If the data matches the last refresh and not the current moment, it's a communication problem, not a computation one.

How to fix: explicitly show "Last updated: X minutes ago" on the dashboard. It's standard practice (you'll see it in GitHub Insights, Stripe Dashboard, GA4). In the MV, add a computed_at TIMESTAMPTZ DEFAULT NOW() column that updates with the refresh.

Mistake 5 (practical): assuming an MV solves a multi-tenant RLS problem

Symptom: your app is multi-tenant with RLS on the base tables. You create an MV over those tables and suddenly one tenant sees another tenant's data.

Why it happens: MVs don't apply the RLS policies of the base tables — the refresh runs them with elevated privileges and materializes the full result. When users query the MV, there's no RLS filter because the MV is a new table with no RLS configured.

How to tell: check which tenants see which rows. If one tenant sees rows from others, it's a security bug — not optional.

How to fix: two options:

• Add RLS on the MV explicitly (including tenant_id in the materialized columns and policies that filter by current_setting('app.tenant_id')).
• Or use a VIEW instead of an MV for multi-tenant cases where RLS is critical — the VIEW respects the base tables' policies by construction.

Exercises

Exercise 1: classify 5 use cases

For each one, decide: VIEW, MATERIALIZED VIEW, or direct SELECT. Justify.

1. "Current user's avatar URL" (loaded on every page).
2. "List of the 50 most-commented posts of all time" (loaded on the homepage, ~500 visits/hour).
3. "User's current account balance" (billing view).
4. "Monthly revenue summary by category" (report sent by email every Monday).
5. "Free keyword search results" (any word the user types).

Exercise 2: identify the wrong case

The team created this MV. Identify what's wrong and propose an alternative.

CREATE MATERIALIZED VIEW mv_user_session AS
SELECT
    user_id,
    session_token,
    last_activity_at,
    expires_at
FROM sessions
WHERE expires_at > NOW();

-- Refresh every 10 seconds via cron:
REFRESH MATERIALIZED VIEW mv_user_session;

The /me endpoint reads from mv_user_session to validate the user's token.

-- Option 1: direct SELECT with an index
CREATE INDEX idx_sessions_token_active
    ON sessions (session_token)
    WHERE expires_at > NOW();

-- In the /me endpoint:
SELECT user_id FROM sessions
WHERE session_token = $1 AND expires_at > NOW();

Exercise 3: your own decision matrix

Fill out this matrix for your current app (or a hypothetical one). List 5 endpoints and for each one: query cost, query frequency, staleness tolerance, decision.

Exercise 4: translate a product brief into a technical decision

The PM sends you this brief:

Feature: "Last-hour trends on the homepage"

Show the 5 posts with the most views in the last hour. If a post goes viral, it should show up at the top within a few minutes. The homepage gets ~2000 visits/hour.

Decide: VIEW, MATERIALIZED VIEW, direct SELECT, other. Justify with numbers.

Summary and next step

In this lesson you learned the critical framing to decide between VIEW and MATERIALIZED VIEW:

• VIEW is pure metadata. It doesn't store data. Every query runs the underlying SELECT. It wins when the query is cheap, the data must be fresh, or you need schema abstraction.

• MATERIALIZED VIEW stores the result physically. Cheap reads (lookup over a materialized table). Data stale until the next refresh. It wins when the query is expensive, reads are frequent, and users tolerate staleness.

• The trade-off is always freshness vs latency. There's no free lunch. The choice comes from the intersection of three axes: query cost, query frequency, staleness tolerance.

• Practical decision tree: cheap query → direct SELECT or VIEW. Expensive query + staleness OK → MV. Expensive query + staleness not OK → Redis cache or another service (lesson 07).

• Common mistakes to avoid: assuming a VIEW stores data, assuming an MV refreshes itself, using an MV for volatile data, not communicating staleness to the user, ignoring the RLS impact.

Before moving on, you should be able to:

• Receive a brief for a new endpoint and decide VIEW/MV/direct in under 1 minute.
• Justify the decision with numbers (cost, frequency, acceptable staleness).
• Explain to a colleague why an MV with a 30-second refresh is usually an anti-pattern.
• Anticipate the problem of staleness communicated to the user (a "last updated" banner) as part of the design.

Next lesson — creation and refresh: fundamentals. Now that you know when to choose an MV, you're going to learn to create it. Lesson 03 takes you from CREATE MATERIALIZED VIEW to REFRESH MATERIALIZED VIEW end-to-end with your first runnable MV: a "last 7 days trends" over the views table. You'll see the full command, how to query the MV, and why the first refresh is always FULL (not concurrent). There you'll also understand the unique index gotcha that lesson 04 goes deeper into.

Resources

1. PostgreSQL 16 — CREATE VIEW — official reference. Useful for contrasting against MV.
2. PostgreSQL 16 — CREATE MATERIALIZED VIEW — syntax and semantics.
3. Crunchy Data — When to Use Materialized Views — operational guide on when each structure applies, with real cases.
4. pganalyze — Materialized Views in Postgres — analysis with benchmarks of when MV wins vs direct query.
5. Hashrocket — Materialized View Strategies — usage patterns in real applications.
6. PostgreSQL Wiki — Views Comparison — comparison of view types (including updatable views).

Module 5 — Advanced PostgreSQL for Backend Guide

Next lesson: Creation and refresh — fundamentals for your first runnable MV.