Views and Materialized Views

LinkedIn Hook

"Your dashboard query took 14 seconds. You wrapped it in a VIEW. It still took 14 seconds. You are surprised."

Most developers reach for CREATE VIEW the first time a SELECT statement gets long, then quietly rage when the view runs no faster than the original query. That is because a regular view is not a cache — it is a saved query, expanded and re-executed every single time you SELECT from it. The optimizer inlines the view definition into your outer query and plans the whole thing from scratch. There is no stored data, no precomputed result, no magic.

Materialized views are the other half of the story. They actually store the result of the query as a physical table on disk. Reads are now a simple table scan — milliseconds, not seconds. The trade is staleness: the snapshot only updates when you call REFRESH MATERIALIZED VIEW, and that refresh takes a write lock on the table unless you remember the magic word CONCURRENTLY.

Picking the wrong one breaks production in opposite ways. A regular view on a million-row aggregate brings your dashboard to its knees. A materialized view on a constantly-changing table serves stale data to users and confuses everyone. And the upgrade path between them is not free — CONCURRENTLY requires a unique index, MySQL has no native materialized views at all, and updatable views have rules most engineers have never read.

In Lesson 12.1, I break down views and materialized views in PostgreSQL: how virtual views actually execute, when they help and when they hurt, how to build a materialized view that refreshes without locking readers, and the decision tree for picking the right tool.

Read the full lesson -> [link]

#SQL #PostgreSQL #Database #MaterializedView #BackendDevelopment #InterviewPrep #QueryPerformance

---

---

What You'll Learn

• The difference between a virtual view (saved query) and a materialized view (stored snapshot)
• How CREATE VIEW works and why it does not improve performance on its own
• When views are the right abstraction: security, simplification, schema stability
• How CREATE MATERIALIZED VIEW actually stores rows on disk like a table
• Why REFRESH MATERIALIZED VIEW takes a lock and how CONCURRENTLY fixes it
• The unique-index requirement for REFRESH ... CONCURRENTLY and why it exists
• Updatable views, the rules Postgres applies, and WITH CHECK OPTION
• Why MySQL does not have materialized views natively and how teams emulate them
• The decision tree: view vs materialized view vs summary table vs application cache

---

The Recipe Card vs The Prepared Meal Analogy

Imagine you run a busy diner. A customer orders the "Sunday Special," which is a complicated dish that takes 14 minutes of chopping, simmering, and plating. You have two ways to serve it faster the next time someone orders.

Option A — the recipe card. You write the recipe on an index card and pin it to the wall. The next time a customer orders the Sunday Special, you grab the card, read it, and cook the dish from scratch. The card saved you the work of remembering the recipe, but it did not save you any cooking time. Every order still takes 14 minutes. The advantage is that the dish is always made with the freshest ingredients — whatever is in the fridge right now.

Option B — the prepared meal. Every morning at 6 AM, you cook a giant batch of the Sunday Special and store it in the warmer. When a customer orders, you scoop a portion onto a plate and serve it in 30 seconds. The trade-off is obvious: the food is from this morning, not this minute. If the lunch rush eats it all, you are out until tomorrow's batch. And if a supplier delivers fresher tomatoes at noon, your prepared meal does not benefit.

A regular SQL view is the recipe card. It saves the query, not the result. Every SELECT against the view re-runs the underlying query against live data. A materialized view is the prepared meal. It cooks the query once, stores the rows on disk, and serves them instantly — at the cost of staleness until the next refresh.

+---------------------------------------------------------------+
|           VIEW (Recipe Card)                                  |
+---------------------------------------------------------------+
|                                                                |
|  SELECT * FROM monthly_sales_view;                             |
|         |                                                      |
|         v                                                      |
|  Optimizer expands view definition inline                      |
|         |                                                      |
|         v                                                      |
|  SELECT customer_id, SUM(amount)                               |
|    FROM orders                                                 |
|    JOIN customers USING (customer_id)                          |
|   WHERE created_at >= NOW() - INTERVAL '30 days'               |
|   GROUP BY customer_id;   <-- runs from scratch every time     |
|                                                                |
|  Cost:    same as the underlying query                         |
|  Freshness: always current                                     |
|  Storage: zero (no stored rows)                                |
|                                                                |
+---------------------------------------------------------------+

+---------------------------------------------------------------+
|           MATERIALIZED VIEW (Prepared Meal)                   |
+---------------------------------------------------------------+
|                                                                |
|  SELECT * FROM monthly_sales_mv;                               |
|         |                                                      |
|         v                                                      |
|  Read pre-computed rows from disk (just a table scan)          |
|                                                                |
|  Cost:    cheap, like reading any table                        |
|  Freshness: as old as the last REFRESH                         |
|  Storage: full result set on disk + indexes                    |
|                                                                |
|  REFRESH MATERIALIZED VIEW monthly_sales_mv;                   |
|         |                                                      |
|         v                                                      |
|  Re-runs the underlying query, replaces stored rows            |
|                                                                |
+---------------------------------------------------------------+

Napkin AI Visual Prompt: "Dark navy gradient (#0a0f1f -> #111a2e). Split panel comparison. LEFT side labeled 'VIEW' shows a translucent ghost frame of a SELECT statement with a sky blue (#4fc3f7) recursive arrow labeled 're-executes' looping back on itself, with a green badge 'always fresh'. RIGHT side labeled 'MATERIALIZED VIEW' shows a solid disk cylinder of stored rows with a rose (#ff5c8a) REFRESH button on top, the word 'CONCURRENTLY' in sky blue beside it, and an amber badge 'stale until refresh'. Connecting them: a dashed sky blue arrow labeled 'upgrade when reads dominate'. White monospace labels throughout. Subtle grid pattern overlay."

---

CREATE VIEW — The Saved Query

A view is a named SELECT statement stored in the database catalog. When you query the view, the planner expands the view definition into your outer query and plans the merged statement. There are no stored rows, no precomputed results, no caching. The benefit is purely organizational: complex joins, security filters, and column transformations live in one place instead of being copy-pasted across application code.

Example 1 — A Security and Simplification View

-- Underlying table with sensitive columns we do not want every app to see.
CREATE TABLE employees (
  employee_id   SERIAL PRIMARY KEY,
  full_name     TEXT NOT NULL,
  email         TEXT NOT NULL,
  department    TEXT NOT NULL,
  salary        NUMERIC(10, 2) NOT NULL,
  ssn           TEXT NOT NULL,
  hired_at      DATE NOT NULL
);

INSERT INTO employees (full_name, email, department, salary, ssn, hired_at) VALUES
  ('Ada Lovelace',    'ada@example.com',    'Engineering', 145000, '111-22-3333', '2021-03-15'),
  ('Linus Torvalds',  'linus@example.com',  'Engineering', 180000, '222-33-4444', '2019-08-01'),
  ('Grace Hopper',    'grace@example.com',  'Research',    160000, '333-44-5555', '2020-01-10'),
  ('Margaret Hamilton','margaret@example.com','Research',  155000, '444-55-6666', '2022-06-20');

-- View hides salary and ssn from non-HR applications.
-- Also adds a derived column "tenure_years" so callers do not duplicate the math.
CREATE VIEW employee_directory AS
SELECT
  employee_id,
  full_name,
  email,
  department,
  -- Derived column computed at query time
  EXTRACT(YEAR FROM AGE(CURRENT_DATE, hired_at))::INT AS tenure_years
FROM employees;

-- Grant read on the view, NOT on the underlying table.
-- The app role can never see salary or ssn.
GRANT SELECT ON employee_directory TO app_reader;

SELECT * FROM employee_directory ORDER BY tenure_years DESC;

Sample output:

 employee_id |     full_name      |        email         | department  | tenure_years
-------------+--------------------+----------------------+-------------+--------------
           2 | Linus Torvalds     | linus@example.com    | Engineering |            6
           3 | Grace Hopper       | grace@example.com    | Research    |            6
           1 | Ada Lovelace       | ada@example.com      | Engineering |            5
           4 | Margaret Hamilton  | margaret@example.com | Research    |            3
(4 rows)

The view did three things at once: it hid the sensitive columns, simplified the API for callers, and centralized the tenure calculation. If you ever change how tenure is computed, you change one view definition instead of grepping the codebase.

What the Planner Actually Does

When you write SELECT * FROM employee_directory WHERE department = 'Engineering', the planner does not read a stored copy of the view. It rewrites your query to:

SELECT employee_id, full_name, email, department,
       EXTRACT(YEAR FROM AGE(CURRENT_DATE, hired_at))::INT AS tenure_years
  FROM employees
 WHERE department = 'Engineering';

This is called view inlining, and it is why views are sometimes free (the planner pushes your filters down into the underlying tables) and sometimes painful (the planner cannot push filters past aggregates or window functions, so the view computes the whole world before filtering).

---

When Views Make Sense (and When They Do Not)

+---------------------------------------------------------------+
|           USE A VIEW WHEN...                                  |
+---------------------------------------------------------------+
|                                                                |
|   - You need column-level security (hide salary, ssn)         |
|   - The same join/filter is repeated across many queries      |
|   - You want a stable API while the schema evolves underneath |
|   - Reporting tools need a clean denormalized shape           |
|   - The underlying query is already cheap (indexed, small)    |
|                                                                |
+---------------------------------------------------------------+

+---------------------------------------------------------------+
|           DO NOT USE A VIEW WHEN...                           |
+---------------------------------------------------------------+
|                                                                |
|   - You expect it to be a cache (it is not)                   |
|   - The underlying query takes seconds (the view will too)    |
|   - You are aggregating millions of rows for a dashboard      |
|   - Filters cannot be pushed through (windows, DISTINCT ON)   |
|   - You stack views on views on views (planner blows up)      |
|                                                                |
+---------------------------------------------------------------+

The cardinal rule: a view does not make a slow query fast. If the underlying SELECT is slow, the view is slow. Reach for materialization, indexing, or query rewriting instead.

---

CREATE MATERIALIZED VIEW — The Stored Snapshot

A materialized view runs the underlying query once at creation time, stores the result as physical rows on disk, and lets you query those rows like any other table. Subsequent SELECTs do not re-execute the query — they just scan the stored data. This is the right tool when you have an expensive aggregation that is read often and tolerates being a few minutes (or hours) stale.

Example 2 — A Dashboard Aggregate Materialized View

-- Source tables for an e-commerce app.
CREATE TABLE customers (
  customer_id  SERIAL PRIMARY KEY,
  name         TEXT NOT NULL,
  country      TEXT NOT NULL
);

CREATE TABLE orders (
  order_id     BIGSERIAL PRIMARY KEY,
  customer_id  INT NOT NULL REFERENCES customers(customer_id),
  amount       NUMERIC(10, 2) NOT NULL,
  status       TEXT NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO customers (name, country) VALUES
  ('Acme Co',     'US'),
  ('Globex',      'US'),
  ('Initech',     'CA'),
  ('Umbrella',    'UK');

INSERT INTO orders (customer_id, amount, status, created_at) VALUES
  (1,  120.00, 'paid',     NOW() - INTERVAL '2 days'),
  (1,   45.50, 'paid',     NOW() - INTERVAL '5 days'),
  (2,  300.00, 'paid',     NOW() - INTERVAL '1 day'),
  (2,   80.00, 'refunded', NOW() - INTERVAL '10 days'),
  (3,  500.00, 'paid',     NOW() - INTERVAL '3 days'),
  (4,  220.00, 'paid',     NOW() - INTERVAL '7 days');

-- The expensive query: revenue per country over the last 30 days.
-- On a real dataset this might scan millions of rows and take seconds.
CREATE MATERIALIZED VIEW country_revenue_30d AS
SELECT
  c.country,
  COUNT(*)         AS order_count,
  SUM(o.amount)    AS total_revenue,
  AVG(o.amount)::NUMERIC(10, 2) AS avg_order_value
FROM orders o
JOIN customers c USING (customer_id)
WHERE o.status = 'paid'
  AND o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY c.country
WITH DATA;  -- Populate immediately. Use WITH NO DATA to defer.

-- A unique index is REQUIRED to support REFRESH ... CONCURRENTLY later.
-- Without this, only blocking refresh works.
CREATE UNIQUE INDEX country_revenue_30d_pk
  ON country_revenue_30d (country);

SELECT * FROM country_revenue_30d ORDER BY total_revenue DESC;

Sample output:

 country | order_count | total_revenue | avg_order_value
---------+-------------+---------------+-----------------
 CA      |           1 |        500.00 |          500.00
 US      |           3 |        465.50 |          155.17
 UK      |           1 |        220.00 |          220.00
(3 rows)

The query that backed this materialized view might have taken 4 seconds against the live orders table with millions of rows. Reading from country_revenue_30d is now a 3-row table scan — under a millisecond. Your dashboard renders instantly. The trade-off: the numbers are frozen until someone runs REFRESH.

---

REFRESH MATERIALIZED VIEW — Blocking and CONCURRENTLY

Materialized views do not refresh themselves. You decide when. There are two flavors of refresh, and the difference matters in production.

Blocking Refresh (the default)

REFRESH MATERIALIZED VIEW country_revenue_30d;

This takes an ACCESS EXCLUSIVE lock on the materialized view. Any concurrent SELECT against the view blocks until the refresh finishes. For a small view this is fine. For a large view that takes 30 seconds to rebuild, every dashboard query during that window stalls — and if the refresh runs every 5 minutes via cron, you have built yourself a tiny outage every 5 minutes.

Concurrent Refresh

REFRESH MATERIALIZED VIEW CONCURRENTLY country_revenue_30d;

CONCURRENTLY builds the new snapshot in a temporary table, then computes the diff against the old snapshot and applies INSERTs, UPDATEs, and DELETEs to the existing rows. Readers continue to see the old data the entire time and switch to the new data atomically at the very end. The lock taken is EXCLUSIVE, which still blocks other refreshes and writers but does not block readers.

There is one mandatory cost: CONCURRENTLY requires a unique index on the materialized view. Postgres needs a stable row identity to compute the diff. If you forget the index, the command errors out:

ERROR:  cannot refresh materialized view "country_revenue_30d" concurrently
HINT:   Create a unique index with no WHERE clause on one or more columns of the materialized view.

+---------------------------------------------------------------+
|           REFRESH BLOCKING vs CONCURRENTLY                    |
+---------------------------------------------------------------+
|                                                                |
|   REFRESH MATERIALIZED VIEW mv;                                |
|         |                                                      |
|         v                                                      |
|   ACCESS EXCLUSIVE lock                                        |
|         |                                                      |
|         v                                                      |
|   readers BLOCKED -> rebuild -> swap -> readers resume         |
|                                                                |
|   Pros: simple, no unique index needed                         |
|   Cons: outage during rebuild                                  |
|                                                                |
+---------------------------------------------------------------+

+---------------------------------------------------------------+
|   REFRESH MATERIALIZED VIEW CONCURRENTLY mv;                   |
|         |                                                      |
|         v                                                      |
|   build new snapshot in a temp table                           |
|         |                                                      |
|         v                                                      |
|   diff old vs new using the unique index                       |
|         |                                                      |
|         v                                                      |
|   apply INSERT/UPDATE/DELETE in place                          |
|         |                                                      |
|         v                                                      |
|   readers NEVER blocked                                        |
|                                                                |
|   Pros: zero read downtime                                     |
|   Cons: needs UNIQUE INDEX, slower than blocking refresh,      |
|         double disk usage during the rebuild                   |
|                                                                |
+---------------------------------------------------------------+

In production, the default should always be CONCURRENTLY plus a scheduled refresh job (cron, pg_cron, application-level scheduler). Reserve the blocking form for one-off backfills or maintenance windows.

---

Updatable Views — When Postgres Lets You INSERT Through a View

A view is read-only by default, but Postgres can make a view updatable automatically if the view satisfies a strict set of rules: the view is built from exactly one base table, contains no aggregates, no DISTINCT, no GROUP BY, no HAVING, no UNION, no window functions, and only column references in the SELECT list. When all those rules hold, you can INSERT, UPDATE, and DELETE through the view and Postgres rewrites the operation against the base table.

Example 3 — An Updatable View with WITH CHECK OPTION

-- Base table holds orders for every region.
CREATE TABLE all_orders (
  order_id    SERIAL PRIMARY KEY,
  region      TEXT NOT NULL,
  customer    TEXT NOT NULL,
  amount      NUMERIC(10, 2) NOT NULL
);

INSERT INTO all_orders (region, customer, amount) VALUES
  ('US', 'Acme',    100.00),
  ('US', 'Globex',  200.00),
  ('EU', 'Initech', 300.00);

-- Updatable view that scopes to a single region.
-- WITH CHECK OPTION enforces the WHERE clause on INSERT and UPDATE,
-- so the view cannot be used to smuggle non-US rows into the table.
CREATE VIEW us_orders AS
SELECT order_id, customer, amount
  FROM all_orders
 WHERE region = 'US'
 WITH CHECK OPTION;

-- INSERT through the view: Postgres rewrites to INSERT into all_orders
-- and supplies region = 'US' from the view's WHERE clause.
INSERT INTO us_orders (customer, amount) VALUES ('Stark Inc', 450.00);

-- This INSERT would VIOLATE the CHECK OPTION because the new row
-- would not satisfy the view's WHERE clause. Postgres rejects it.
-- (Demonstration only — would fail.)
-- INSERT INTO all_orders (region, customer, amount) VALUES ('EU', 'Wayne', 99);

SELECT * FROM us_orders ORDER BY order_id;

Sample output:

 order_id | customer  | amount
----------+-----------+--------
        1 | Acme      | 100.00
        2 | Globex    | 200.00
        4 | Stark Inc | 450.00
(3 rows)

When automatic update rules are not enough — for example, a view that joins multiple tables — you can still make it writable using INSTEAD OF triggers. Those let you write arbitrary code that runs in place of the INSERT/UPDATE/DELETE on the view. They are powerful but easy to misuse, so most teams prefer to write directly against base tables and reserve views for reads.

---

Materialized Views in MySQL — They Do Not Exist

MySQL does not have native materialized views. Every "materialized view" in a MySQL codebase is one of three things:

1. A regular table refreshed by a cron job that runs TRUNCATE summary_table; INSERT INTO summary_table SELECT .... Simple, but takes a write lock and breaks during the refresh window.
2. A regular table maintained by triggers on the source tables. Always fresh, but every write to the source carries the cost of updating the summary, and the triggers are easy to get wrong.
3. An incrementally updated table managed by application code. Hardest to build, best performance, and prone to drift if any code path forgets to update both places.

If you are running on PostgreSQL and reach for a "summary table refreshed by cron," reach for a materialized view first. You get atomic refreshes, CONCURRENTLY for free, and the schema is documented as part of the database catalog.

---

When to Pick Which — The Decision Tree

+---------------------------------------------------------------+
|           VIEW vs MATERIALIZED VIEW vs TABLE                  |
+---------------------------------------------------------------+
|                                                                |
|  Is the underlying query already fast?                         |
|     YES -> use a VIEW (organization, security, abstraction)   |
|     NO  -> continue                                            |
|                                                                |
|  Can users tolerate stale data (minutes / hours)?              |
|     NO  -> fix the underlying query (indexes, rewrite)         |
|     YES -> continue                                            |
|                                                                |
|  Does the result set fit in a reasonable amount of disk?       |
|     NO  -> partial materialization, summary tables, OLAP DB    |
|     YES -> continue                                            |
|                                                                |
|  Do you need zero-downtime refreshes during read traffic?      |
|     YES -> MATERIALIZED VIEW + UNIQUE INDEX + CONCURRENTLY     |
|     NO  -> MATERIALIZED VIEW with blocking REFRESH is fine     |
|                                                                |
|  Does freshness need to be sub-second?                         |
|     -> Use a real cache (Redis), trigger-maintained table,     |
|        or a streaming pipeline. Materialized views are not     |
|        the right tool below the minute scale.                  |
|                                                                |
+---------------------------------------------------------------+

---

Common Mistakes

1. Treating a regular view as a cache. Wrapping a slow query in CREATE VIEW does not make it faster. The view is just a saved SELECT — every query against it re-runs the underlying SQL from scratch. If the original query took 4 seconds, the view takes 4 seconds. Fix the query, or materialize it. Do not deploy a view and check the dashboard expecting magic.

2. Forgetting the unique index on a materialized view. You build a materialized view, schedule a refresh job, deploy to production, and the first cron run fails with cannot refresh materialized view ... concurrently. Postgres needs a unique index to compute the row diff for CONCURRENTLY. Always create the unique index in the same migration that creates the materialized view, and pick a column (or column set) that is genuinely unique in the result.

3. Using blocking REFRESH on a hot dashboard view. REFRESH MATERIALIZED VIEW mv; takes ACCESS EXCLUSIVE and blocks every reader until the refresh completes. On a 30-second refresh of a dashboard view that runs every 5 minutes, you have built yourself a 30-second outage every 5 minutes. Always use CONCURRENTLY in production unless you are inside a planned maintenance window.

4. Stacking views on views on views. A view that selects from a view that selects from a view that selects from a view eventually produces a query plan the optimizer cannot reason about. Filters fail to push down, joins explode, and EXPLAIN becomes incomprehensible. Limit view nesting to one or two levels, and flatten anything deeper into a single SELECT or a materialized view.

5. Expecting MySQL to have materialized views. MySQL has no native materialized view object. Code that says "I will just create a materialized view on the read replica" assumes a feature that does not exist. The MySQL equivalent is a summary table refreshed by cron, triggers, or application code — all of which have sharp edges that materialized views avoid in PostgreSQL.

---

Interview Questions

1. "What is the difference between a view and a materialized view, and how does each one execute a SELECT against it?"

A regular view is a saved query stored in the database catalog. When you SELECT * FROM my_view, the optimizer expands the view definition inline into your outer query and plans the merged statement against the live base tables. There are no stored rows, no precomputed result, and no caching — every query re-executes the underlying SQL from scratch. A materialized view runs the underlying query once at creation time (or at REFRESH time), stores the result as physical rows on disk, and exposes them as a queryable object that behaves like a table. SELECTs against a materialized view are just table scans against the stored snapshot. The trade-off is freshness: a view is always current, while a materialized view is exactly as stale as the time since the last REFRESH. Pick a view when you want abstraction, security, or schema stability. Pick a materialized view when the underlying query is expensive and the data tolerates being a few minutes stale.

2. "Why does REFRESH MATERIALIZED VIEW CONCURRENTLY require a unique index, and what does it actually do under the hood?"

CONCURRENTLY works by building a fresh copy of the materialized view in a temporary location, then computing the difference between the old stored rows and the new rows, and applying that diff as a series of INSERTs, UPDATEs, and DELETEs on the existing materialized view rows in place. To compute that diff, Postgres needs a stable row identity — a way to say "this row in the new snapshot corresponds to that row in the old snapshot." That identity comes from the unique index. Without it, Postgres has no way to match rows across the two snapshots and refuses to run concurrently. The benefit of all this work is that readers are never blocked: they continue to see the old snapshot for the entire duration of the refresh and atomically switch to the new snapshot at the very end. The cost is that the refresh is slower than a blocking refresh, uses roughly twice the disk space during the rebuild, and takes an EXCLUSIVE lock that prevents concurrent writes and concurrent refreshes (but allows reads).

3. "When is a regular view the wrong tool, and what should you reach for instead?"

A regular view is the wrong tool whenever you expected it to improve performance. Wrapping a slow aggregate query in a view does nothing for query time because the view re-executes the query on every SELECT. If your motivation is performance, you have three real options. First, fix the query: add indexes, rewrite joins, eliminate sequential scans, or push filters earlier. Second, materialize it: a materialized view stores the result on disk and serves reads from a precomputed snapshot, with REFRESH ... CONCURRENTLY keeping the data fresh on a schedule. Third, move the workload: for true real-time analytics on huge datasets, a column store or analytics database (ClickHouse, BigQuery, DuckDB, Snowflake) is the right answer instead of trying to make Postgres pretend. Views are for organization, security, and stable APIs — never for speed.

4. "What are updatable views in PostgreSQL, what rules must they satisfy, and what does WITH CHECK OPTION do?"

A view is automatically updatable in Postgres when it meets a strict set of rules: it must be built from exactly one base table or updatable view, must not contain DISTINCT, GROUP BY, HAVING, LIMIT, OFFSET, UNION, INTERSECT, EXCEPT, set-returning functions, aggregates, window functions, or sub-queries in the SELECT list, and the SELECT list must contain only simple column references. When all rules hold, INSERT, UPDATE, and DELETE against the view are rewritten by Postgres as the equivalent operations on the base table. WITH CHECK OPTION extends this by enforcing the view's WHERE clause on every write. For example, a view defined as SELECT * FROM orders WHERE region = 'US' WITH CHECK OPTION will reject any INSERT or UPDATE through the view that would produce a row with region <> 'US'. Without WITH CHECK OPTION, you could insert non-US rows through a US-scoped view and they would silently disappear from view queries — a subtle and confusing bug. For views too complex to be automatically updatable, INSTEAD OF triggers let you write arbitrary code to handle writes.

5. "How would you design a refresh strategy for a materialized view that powers a real-time dashboard?"

The first question is what "real-time" actually means to the users. If they need second-level freshness, a materialized view is the wrong tool — use a trigger-maintained summary table, a streaming pipeline (Debezium + ksqlDB), or an in-memory cache like Redis backed by event-driven invalidation. If they accept minute-level or hour-level staleness, a materialized view with REFRESH MATERIALIZED VIEW CONCURRENTLY on a schedule is the right answer. Build the view with a unique index on whichever columns produce a unique key in the result. Schedule the refresh from pg_cron, your application scheduler, or an external job runner — start with an interval that matches the freshness requirement (every 5 or 10 minutes is common). Monitor refresh duration: if it ever approaches the refresh interval, the view is too expensive and you need to add indexes to the source tables, narrow the time window, or shard the materialized view by partition. Always alert on refresh failures, because a stuck refresh silently serves stale data forever. And document the staleness contract: the dashboard should display the last refresh timestamp so users know what they are looking at.

---

Quick Reference — Cheat Sheet

+---------------------------------------------------------------+
|           VIEWS & MATERIALIZED VIEWS CHEAT SHEET              |
+---------------------------------------------------------------+
|                                                                |
|  CREATE A VIEW (saved query, no storage):                      |
|    CREATE VIEW v AS SELECT ... ;                               |
|    -- always fresh, never faster than the base query           |
|                                                                |
|  CREATE A MATERIALIZED VIEW (stored snapshot):                 |
|    CREATE MATERIALIZED VIEW mv AS SELECT ... WITH DATA;        |
|    CREATE UNIQUE INDEX mv_pk ON mv (key_column);               |
|                                                                |
|  REFRESH (BLOCKING - locks readers):                           |
|    REFRESH MATERIALIZED VIEW mv;                               |
|                                                                |
|  REFRESH (CONCURRENT - never blocks readers):                  |
|    REFRESH MATERIALIZED VIEW CONCURRENTLY mv;                  |
|    -- requires UNIQUE INDEX, uses 2x disk during rebuild       |
|                                                                |
|  UPDATABLE VIEW WITH SECURITY:                                 |
|    CREATE VIEW v AS SELECT ... WHERE ...                       |
|      WITH CHECK OPTION;                                        |
|                                                                |
|  DROP:                                                         |
|    DROP VIEW v;                                                |
|    DROP MATERIALIZED VIEW mv;                                  |
|                                                                |
+---------------------------------------------------------------+

+---------------------------------------------------------------+
|           KEY RULES                                            |
+---------------------------------------------------------------+
|                                                                |
|  1. A view is a saved query, not a cache                       |
|  2. A materialized view is a stored snapshot, not live data    |
|  3. CONCURRENTLY needs a UNIQUE INDEX — always create one      |
|  4. Always use CONCURRENTLY in production refresh jobs         |
|  5. Materialized views never refresh themselves — schedule it  |
|  6. Updatable views need a single base table and no aggregates |
|  7. WITH CHECK OPTION prevents smuggling rows through a view   |
|  8. MySQL has no materialized views — emulate with tables      |
|  9. Do not stack views more than two levels deep               |
|  10. Monitor refresh duration and alert on failures            |
|                                                                |
+---------------------------------------------------------------+

Concern	View	Materialized View
Storage	None	Full result on disk
Read cost	Same as base query	Cheap table scan
Freshness	Always live	Stale until REFRESH
Refresh needed	Never	Manual or scheduled
Locks readers	Never	Only with blocking REFRESH
Indexable	No (the base tables are)	Yes, like any table
Writable	If rules met + WITH CHECK	No (read-only)
MySQL support	Yes	No (emulate with tables)
Best for	Security, abstraction	Expensive aggregates
Worst for	Slow queries	Real-time data

---

Prev: Lesson 11.6 -- SERIALIZABLE and Isolation Comparison Next: Lesson 12.2 -- Stored Procedures and Functions

---

This is Lesson 12.1 of the Database Interview Prep Course -- 12 chapters, 58 lessons.