SQL Lesson 26: Why Your View Recomputes Every Single Time

8. Introduction

If you have written the same twelve-line SQL query for the fifth time this week, you have already felt the pull toward views. And if a dashboard that used to load in a second now spins for thirty, you are about to meet materialized views.

These two features sound like enterprise jargon, but they solve everyday problems: cutting repetition, hiding sensitive columns, and making slow reports fast. They also hide a trap that catches beginners and senior engineers alike — one that silently turns a "simple" query into a resource hog.

Interviewers love this topic because it reveals whether you understand what the database actually does when you run a query. Anyone can memorize CREATE VIEW. Far fewer can explain why a view recomputes on every read, what a materialized view trades away to be fast, and when reaching for the fancier tool is a mistake. That distinction is the difference between a developer who writes SQL and one who reasons about databases.

By the end of this article you will know exactly which one to reach for, and — just as important — when not to.

9. Problem Overview

Let's frame the real problem in plain terms.

You have complex queries: multi-table joins, filters, aggregations. Typing them repeatedly is error-prone and hard to maintain. You also have data you don't want everyone to see — salaries, personal details — but the underlying table mixes safe and sensitive columns together.

A view solves both. It is a named query. You define it once, and afterward you can SELECT from it as if it were an ordinary table. Nothing is stored on disk; the view is virtual. Each time you query it, the database re-runs the underlying query against the current data in the base table (the real, physical table where rows actually live).

That freshness is a feature — until it becomes a cost. When the underlying query is expensive, every single read pays the full computation price again. There is no memory of the last result.

A materialized view flips the trade. It runs the query once and stores the result on disk. Reads become nearly instant because the answer is already computed. The price: the stored copy freezes at the moment it was built and slowly drifts out of date. It only catches up when you explicitly refresh it.

So the core problem this lesson answers is: How do you reuse and secure complex queries, and how do you decide between always-fresh-but-recomputed and instant-but-possibly-stale?

10. Example

Imagine an orders table:

order_id	customer	amount	status
1	Ann	120	paid
2	Bilal	80	pending
3	Chen	200	paid
4	Dana	50	paid

You constantly need only the paid orders. Instead of repeating the filter, you create a view:

CREATE VIEW paid_orders AS
SELECT order_id, customer, amount
FROM orders
WHERE status = 'paid';

Now:

SELECT * FROM paid_orders;

Output:

order_id	customer	amount
1	Ann	120
3	Chen	200
4	Dana	50

The view returned three rows because three orders have status = 'paid'. Notice we queried paid_orders exactly like a table — no WHERE clause needed. The filter lives inside the definition.

Now insert a new paid order and query again: the view immediately shows it, because it re-ran the query. That is the always-fresh behavior. A materialized view, as we'll see, would still show the old three rows until refreshed.

11. Intuition

Here is how an experienced engineer thinks through this, without memorizing anything.

Start with a real annoyance: you keep re-typing a gnarly query. The instinct is "give this a name." That naming instinct is the entire idea behind a view. Think of a saved search on your phone — you don't save the results, you save the search. Open it tomorrow and it runs against tomorrow's data.

So a view is a nickname for a query. Cheap, always current, zero storage. Perfect.

Then reality arrives. Your named query joins ten tables and scans millions of rows. Every teammate hitting that view triggers the full computation. The database has no memory — it cooks the meal from scratch each time. You start wondering: why are we recalculating the exact same numbers a thousand times an hour?

That question is the seed of the materialized view. If the answer barely changes minute to minute, why not compute it once, store it, and let everyone read the stored copy? Think of a printed report pinned to the wall: someone ran the numbers, printed them, and now everyone reads instantly.

But a printed report has an obvious flaw — it's a snapshot. The moment new orders arrive, the print is behind reality. You must reprint (refresh) to catch up. And there's your trade, discovered naturally: you swapped always fresh but slow for instant but stale.

No one needs to hand you a rule. If freshness matters most → plain view. If read speed matters most and slightly-old is acceptable → materialized view. You derived it.

12. Brute Force Solution

Idea: Skip views entirely. Every time you need the data, write the full query inline.

SELECT order_id, customer, amount
FROM orders
WHERE status = 'paid';
-- copy-paste this everywhere it's needed

Algorithm:

1. Identify the data you need.
2. Write the complete query each time.
3. Repeat across every report, script, and application.

Advantages:

• Nothing to learn — plain SELECT.
• Always current data.
• No hidden abstraction; what you see is what runs.

Disadvantages:

• Massive repetition; a logic change must be edited in dozens of places.
• No column-level security — everyone sees the raw table, salaries included.
• Definitions drift, so teams report mismatched numbers.
• No path to caching heavy results.

Time complexity: Each read costs the full query execution — call it O(Q), where Q is the query's cost. Repeated N times, O(N · Q).

Space complexity: O(1) extra — nothing stored beyond the base table.

This "works," but it's a maintenance and security liability. Views fix the repetition and security; materialized views fix the repeated cost.

13. Optimal Solution

The optimal approach is to use the right abstraction for the workload.

Step 1 — Wrap repeated queries in a view

CREATE VIEW paid_orders AS
SELECT order_id, customer, amount
FROM orders
WHERE status = 'paid';

One definition, reused everywhere. Change the logic once, and every consumer updates.

Step 2 — Use views for column-level security

Picture a frosted glass window: people see the shapes you allow, nothing behind.

CREATE VIEW employee_directory AS
SELECT name, department
FROM employees;   -- salary column deliberately excluded

Grant interns access to the view, not the table:

GRANT SELECT ON employee_directory TO interns;

In the interns' world, the salary column simply does not exist.

Step 3 — When reads get expensive, materialize

Only after a real performance problem appears, store the result:

CREATE MATERIALIZED VIEW daily_sales AS
SELECT order_date, SUM(amount) AS total
FROM orders
GROUP BY order_date;

Now daily_sales holds actual summed rows. Dashboards read them instantly.

Step 4 — Refresh on a schedule that fits the freshness need

REFRESH MATERIALIZED VIEW daily_sales;

Here is the freshness flow at a glance:

Time	Base table orders	Materialized daily_sales
09:00 (refresh)	500 orders	total reflects 500
09:30	540 orders	still reflects 500
10:00 (refresh)	560 orders	total reflects 560

Between refreshes, the materialized view is intentionally a little in the past.

The decision table

Question	Plain View	Materialized View
Stored on disk?	No	Yes
Data freshness	Always current	Stale until refreshed
Read speed	Pays query cost every time	Instant
Needs refreshing?	Never	Yes
Best for	Live data, light queries, security	Heavy dashboards, tolerable staleness

Rule of thumb: Start with a plain view. Reach for materialized only when a measured speed problem shows up. Don't optimize a pain you don't have yet.

14. Python Code

Here is a runnable, self-contained simulation using Python's built-in sqlite3 (no external dependencies). SQLite lacks native materialized views, so we emulate one with a real table plus an explicit refresh — which is exactly the mental model that matters.

"""Demonstrate SQL views vs. materialized views using the standard library."""
import sqlite3


def setup(conn: sqlite3.Connection) -> None:
    """Create the base table and seed a few orders."""
    conn.executescript(
        """
        CREATE TABLE orders (
            order_id INTEGER PRIMARY KEY,
            customer TEXT,
            amount   INTEGER,
            status   TEXT
        );
        INSERT INTO orders (customer, amount, status) VALUES
            ('Ann',   120, 'paid'),
            ('Bilal',  80, 'pending'),
            ('Chen',  200, 'paid'),
            ('Dana',   50, 'paid');
        """
    )


def create_view(conn: sqlite3.Connection) -> None:
    """A view stores the query, not the rows — always fresh on read."""
    conn.execute(
        "CREATE VIEW paid_orders AS "
        "SELECT order_id, customer, amount FROM orders WHERE status = 'paid'"
    )


def refresh_materialized(conn: sqlite3.Connection) -> None:
    """Emulate a materialized view: store the computed result on disk."""
    conn.execute("DROP TABLE IF EXISTS paid_orders_matview")
    conn.execute(
        "CREATE TABLE paid_orders_matview AS "
        "SELECT order_id, customer, amount FROM orders WHERE status = 'paid'"
    )


def total(conn: sqlite3.Connection, source: str) -> int:
    """Return the summed amount from a view or materialized table."""
    (result,) = conn.execute(f"SELECT COALESCE(SUM(amount), 0) FROM {source}").fetchone()
    return result


def demo() -> None:
    conn = sqlite3.connect(":memory:")
    setup(conn)
    create_view(conn)
    refresh_materialized(conn)  # snapshot taken now

    # Both agree at first: 120 + 200 + 50 = 370
    assert total(conn, "paid_orders") == 370
    assert total(conn, "paid_orders_matview") == 370

    # A new paid order arrives after the snapshot.
    conn.execute("INSERT INTO orders (customer, amount, status) VALUES ('Eve', 90, 'paid')")

    # The view recomputes and sees Eve immediately.
    assert total(conn, "paid_orders") == 460
    # The materialized copy is stale until refreshed.
    assert total(conn, "paid_orders_matview") == 370

    # Refresh reprints the report — now they line up again.
    refresh_materialized(conn)
    assert total(conn, "paid_orders_matview") == 460

    print("All assertions passed: view is fresh, matview is stale until refreshed.")


if __name__ == "__main__":
    demo()

15. Code Walkthrough

• setup() builds the physical orders base table — the real data on disk — and seeds four rows. This is the ground truth everything else derives from.
• create_view() runs CREATE VIEW. Critically, it stores only the SELECT statement. No rows are copied. Every future query against paid_orders re-executes this filter.
• refresh_materialized() emulates a materialized view with CREATE TABLE ... AS SELECT. This physically copies the current result into paid_orders_matview. Because SQLite has no built-in refresh, we drop and rebuild — conceptually identical to REFRESH MATERIALIZED VIEW.
• total() is a small helper that sums amounts from whichever source you name, so we can compare the two side by side. COALESCE guards against an empty result returning NULL.
• demo() is where the lesson lands. After inserting Eve, the view instantly reports 460 because it recomputes; the materialized table still says 370 because it froze at snapshot time. Only after a second refresh do they agree.

16. Dry Run

Let's trace demo() step by step:

1. Seed: paid orders are Ann (120), Chen (200), Dana (50). Sum = 370.
2. Create view + first refresh: both paid_orders and paid_orders_matview reflect 370. ✅ First two asserts pass.
3. Insert Eve (90, paid): base table now has four paid orders summing to 460.
4. Query the view: it re-runs WHERE status = 'paid' against the current table → 120 + 200 + 50 + 90 = 460. ✅ Matches.
5. Query the materialized copy: it reads the stored snapshot, which never saw Eve → still 370. ✅ Demonstrates staleness.
6. Refresh: we rebuild the snapshot from current data → now 460. ✅ Final assert passes.

The program prints its success line, proving the exact behavioral difference between the two.

17. Complexity Analysis

Let Q be the cost of executing the underlying query and R the number of result rows.

Plain view:

• Read time: O(Q) on every read — it recomputes. For N reads, O(N · Q).
• Storage: O(1) extra. Only the definition is stored.

Materialized view:

• Read time: O(R) — you simply scan stored rows, independent of the original query's complexity. This is the win.
• Refresh time: O(Q) each time you refresh.
• Storage: O(R) — the result set lives on disk.

Why this is correct: A view has nothing cached, so the engine must re-derive the answer from base tables — you can't beat the query's own cost. A materialized view pre-pays that O(Q) cost at refresh time and amortizes it across many cheap O(R) reads. If you read far more often than you refresh, the average cost per read collapses. If you refresh as often as you read, you get the storage cost and the compute cost — the worst of both worlds.

18. Alternative Solutions

Indexed / summary tables maintained by triggers. Instead of periodic refreshes, a trigger updates a summary table on every insert or update. This keeps the cache continuously fresh, but it slows down writes and adds complexity — you now maintain trigger logic. Compared to a materialized view, it trades batch staleness for per-write overhead. Choose it only when you need near-real-time aggregates and can afford slower writes.

Application-layer caching (e.g., Redis, functools.lru_cache). Cache the query result in your app rather than the database. Fast and flexible, but it moves invalidation logic into application code and can diverge across instances. A materialized view keeps the cache inside the database where the data lives — usually simpler to reason about.

REFRESH MATERIALIZED VIEW CONCURRENTLY (PostgreSQL). Refreshes without locking readers, at the cost of requiring a unique index and doing more work. Ideal for dashboards that must stay queryable during refresh.

19. Edge Cases

• Empty base table: A view returns zero rows (correct). A materialized view returns whatever it last stored — possibly rows that no longer exist. Refresh to reconcile.
• All rows filtered out: paid_orders on a table with no paid orders returns nothing; SUM yields NULL — hence the COALESCE guard.
• Duplicates: Views faithfully reflect duplicate base rows; they add no deduplication unless your query says DISTINCT.
• Very large result sets: A materialized view storing millions of rows consumes real disk and takes real time to refresh — factor that in.
• Minimum/boundary values: Negative or zero amounts pass straight through; a view applies no validation. Validate in the base table, not the view.
• Never-refreshed materialized view: Silently serves ancient data forever. This is the classic production incident.

20. Common Mistakes

1. Assuming a materialized view updates itself. Most databases will not refresh unless you tell them. Yesterday's numbers won't move because you never reprinted the report.
2. Refreshing too aggressively. Refreshing every minute on a heavy query burns the very compute you were trying to save — you've paid for storage and gained nothing.
3. Stacking views on views on views. A five-layer view tower means one innocent SELECT secretly triggers a monster query. A tiny call can take thirty seconds, and you'll spend an afternoon finding out why.
4. Reaching for materialized views prematurely. Optimizing a performance problem you don't actually have adds staleness bugs for no benefit. Measure first.
5. Forgetting security is not automatic. A view hides columns only if you grant access to the view and revoke it on the base table. Grant the table too, and the frosted glass is wide open.
6. Confusing a view with a stored copy. Editing base data and expecting a plain view to "remember" the old state — views never store anything.

21. Interview Questions

• What is the difference between a view and a materialized view, and when would you choose each?
• Why does a plain view recompute on every query? What does the database actually store?
• How do you refresh a materialized view, and what is the freshness trade-off?
• How can views enforce column-level security? What must you also do to the base table?
• What are the risks of nesting views several layers deep?
• What does REFRESH MATERIALIZED VIEW CONCURRENTLY do, and what does it require?
• Can you UPDATE through a view? When is a view updatable versus read-only?
• How would you keep a materialized view nearly real-time without over-refreshing?

22. Similar Problems

• Design a caching layer (system design): Same fresh-vs-fast trade-off as materialized views, one abstraction level up.
• LRU Cache (LeetCode 146): Teaches storing computed results to avoid recomputation — the core idea behind materialization.
• Design In-Memory File System / Design a Leaderboard: Both force decisions about precomputing versus computing on read.
• Range Sum Query – Immutable (LeetCode 303): Precomputing a prefix-sum array is materialization in miniature — pay once, read instantly.
• Range Sum Query – Mutable (LeetCode 307): Adds the "data keeps changing" wrinkle, exactly the staleness problem refresh solves.

23. Key Takeaways

• A view is a named query. It stores the recipe, runs fresh every time, and is perfect for hiding complexity and locking down columns.
• A materialized view stores the results for speed, but must be refreshed and can drift out of date.
• The whole decision is a freshness-versus-speed trade: plain view for live data and light queries, materialized view for heavy dashboards where slightly stale is fine.
• Start with a plain view. Only materialize when a real, measured speed problem appears — and never stack views into deep towers.

24. Watch the Video

Want to see these concepts drawn out with the frosted-glass and printed-report analogies in motion? Watch the full walkthrough here: {LINK}. Seeing the refresh flow animated makes the staleness idea click in a way text alone can't.

25. About the Series

This lesson is part of Fun with Learning Technology — a series that turns intimidating engineering topics into friendly, intuition-first lessons. We skip the dense jargon and build understanding the way a good mentor would: one clear analogy at a time, always answering why before how. Whether you're preparing for interviews or leveling up on the job, each episode leaves you with a mental model you actually keep.

26. Call To Action

If this cleared up views and materialized views for you:

• 👍 Like the video on YouTube — it genuinely helps more developers find it.
• 🔔 Subscribe to the channel so you don't miss the next SQL lesson.
• 📬 Subscribe on Substack for the written deep-dives like this one.
• 💬 Comment with the trickiest view bug you've ever debugged — I read every one.
• 🔁 Share this with a teammate who's still copy-pasting the same query everywhere.

---

CREATE MATERIALIZED VIEW daily_sales AS
SELECT day, SUM(amount) AS total
FROM orders
GROUP BY day;

-- rerun the numbers on demand
REFRESH MATERIALIZED VIEW daily_sales;