A materialized view is a query whose result is stored physically, like a table, and refreshed on demand instead of being recomputed on every read. Interviewers probe it because it is the cleanest example of the oldest trade-off in data systems: pay at write or refresh time so that reads are cheap, and accept that the answer may be stale. For a Backend or AI engineer it is the tool behind dashboards, leaderboards, reporting endpoints and pre-aggregated feature tables, and the question "why not just cache it in Redis?" is the natural follow-up.
PLAIN VIEW MATERIALIZED VIEW
CREATE VIEW v AS SELECT ... CREATE MATERIALIZED VIEW mv AS SELECT ...
stores: only the query text stores: the query text AND its result rows
read: query is expanded into the read: scan the stored rows (can be indexed)
base tables, then executed
fresh: always current fresh: as of the last refresh
cost: paid on EVERY read cost: paid on each REFRESH, reads are cheap
extra: no storage extra: storage + refresh job + staleness
reader --> v --> rewrite to base tables --> JOIN/GROUP BY over millions of rows
reader --> mv --> read a small pre-aggregated table (maybe 365 rows)A plain view is just a name for a query; it never speeds anything up, because the database expands it into the underlying SQL and runs it every time. A materialized view moves the expensive work (joins, aggregation, sorting) from read time to refresh time. That only pays off when the same expensive result is read many times between refreshes, and when the reader can tolerate the answer being as old as the last refresh. Because the stored result is a real relation, it can carry its own indexes, which is often as valuable as the pre-computation itself. Support differs by engine: PostgreSQL, Oracle, Snowflake and ClickHouse have materialized views in some form; MySQL/InnoDB has no native one, so people build the same thing with a summary table; SQL Server offers indexed views that are maintained synchronously with the base tables, which is a different (and costlier for writes) design.
1. FULL REFRESH recompute everything, replace the stored rows
simple, always correct, cost ~ size of the whole query
PostgreSQL: REFRESH MATERIALIZED VIEW mv;
2. INCREMENTAL apply only the delta caused by changed base rows
cost ~ size of the change, but the engine (or you) must
know how to merge the delta (SUM/COUNT are easy, MAX/DISTINCT
and multi-table joins are hard)
Oracle: fast refresh with materialized view logs
PostgreSQL: no built-in incremental refresh; extensions
such as pg_ivm exist, or you maintain a summary table yourself
3. ON A SCHEDULE full or incremental, driven by cron / pg_cron / an orchestrator
4. ON WRITE triggers or application code update the summary in the same
transaction as the base write: always fresh, but every write
pays for it and hot summary rows become contention points
5. VIA EVENTS a stream (CDC / outbox) feeds a consumer that updates the read
model asynchronously: see cqrs.htmlThe refresh strategy is really a choice of where on the staleness/cost curve you sit. Full refresh is the default because it is trivially correct, but its cost grows with total data, not with the amount that changed, so it stops scaling once the base tables are large or the required freshness is tight. Incremental maintenance fixes that cost, but correctness becomes your problem: a delta for SUM is one addition, while a delta for MAX after a deletion may require a rescan. Refresh-on-write gives freshness at the price of making every write slower and serializing writers on the summary row. The SQLite experiment in the last section performs a full refresh and an incremental refresh of one changed group so you can see both.
time -->
base table: INSERT o1 INSERT o2 INSERT o3 INSERT o4
| | | |
mv content: [ v0 ..........................][ v1 ...........................]
^ refresh at t1 ^ refresh at t2
reader at any point sees v0 or v1, never "half a refresh" (refresh is atomic)
worst-case staleness = refresh interval + refresh duration
(time to recompute is not free)
Who tolerates it? Who does not?
daily revenue dashboard "your order was just placed" confirmation page
leaderboard (30 s old) account balance, stock or seat availability
search-facet counts anything checked before taking an irreversible actionStaleness is not a defect to be hidden; it is the price, and it should be an explicit product decision with a number attached: "the dashboard is at most 5 minutes old". Two practical points follow. First, the bound is refresh interval plus refresh duration, so a view that takes 10 minutes to rebuild cannot honestly promise 5 minutes of freshness. Second, never use a materialized view to make a decision that the source of truth must make: a checkout must read the live inventory row, not a summary that was correct a few minutes ago. A good practice is to expose the refresh time next to the data, for example a refreshed_at column in a summary table, so consumers and monitoring can see how old the result is.
-- PostgreSQL (syntax only; behaviour described in words, no output claimed)
CREATE MATERIALIZED VIEW daily_sales AS
SELECT created_day, COUNT(*) AS n_orders, SUM(amount) AS revenue
FROM orders
GROUP BY created_day
WITH DATA; -- WITH NO DATA creates it empty and unreadable
REFRESH MATERIALIZED VIEW daily_sales;
-- rebuilds the whole result; takes an ACCESS EXCLUSIVE lock, so readers
-- (even plain SELECTs) block until the refresh commits
CREATE UNIQUE INDEX ON daily_sales (created_day); -- prerequisite for CONCURRENTLY
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;
-- computes the new result on the side, diffs it against the current rows, and
-- applies inserts/updates/deletes; readers keep reading the old rows meanwhilePlain REFRESH is fast but blocks readers for its duration, which is unacceptable for a hot endpoint. CONCURRENTLY keeps the view readable by building the new result separately and applying only the differences, at the cost of more total work (a full recompute plus a diff), more temporary space, and a requirement that the view has at least one unique index that covers all rows (no WHERE clause, only plain columns). It cannot be used on a view that has never been populated. Because the diff produces ordinary row inserts and deletes, it also leaves dead rows that autovacuum must clean up, so a very frequent concurrent refresh of a large view adds vacuum pressure. Exact locking rules are worth confirming against the documentation for your PostgreSQL version.
Materialized view Redis / app cache Denormalized table
where it lives inside the database outside (memory) inside the database
defined by a SQL query application code schema + write code
kept in sync by REFRESH (pull) TTL / explicit delete every write updates
the copy (push)
queryable with SQL yes, and indexable no (key lookups) yes
survives restart yes depends on config yes
consistency risk stale until refresh stale until TTL/evict drift if any write
path forgets the copy
best for heavy aggregates, hot keys, per-user hot read paths that
reports, dashboards objects, sessions need row-level freshnessThey all trade freshness or write cost for read speed, and they differ in where the complexity lives. A materialized view is declarative: the database knows the query, so nothing in application code can forget to maintain it, but freshness is limited by refresh granularity. A cache is the right tool for individual hot objects and needs invalidation logic (see redis-and-caching.html). A denormalized table (see normalization-and-denormalization.html) can be kept exactly fresh but forces every write path to maintain the copy, and it drifts silently if one path forgets. When the read model is different enough that it should be built asynchronously from a change stream, that is the CQRS pattern in cqrs.html.
MAX, DISTINCT and multi-table joins.REFRESH MATERIALIZED VIEW blocks readers; CONCURRENTLY does not, but needs a unique index, does more work, and cannot refresh a view that was never populated.WITH DATA).CONCURRENTLY computes the new result, diffs it against the old, and applies the difference while readers continue.-- SQLite has no CREATE MATERIALIZED VIEW, so we emulate one with a summary table.
-- Save as mv.sql and run: sqlite3 :memory: < mv.sql
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount INTEGER, created_day TEXT);
INSERT INTO orders VALUES
(1,'asha',120,'2026-01-01'),(2,'asha',80,'2026-01-01'),
(3,'ravi',200,'2026-01-01'),(4,'ravi',50,'2026-01-02'),
(5,'asha',30,'2026-01-02'),(6,'meena',400,'2026-01-02');
CREATE VIEW v_sales AS
SELECT created_day, COUNT(*) AS n_orders, SUM(amount) AS revenue
FROM orders GROUP BY created_day;
CREATE TABLE mv_sales AS SELECT * FROM v_sales; -- the "materialized" copy
.headers on
.mode column
.print '== 1. right after creation: view and mv agree =='
SELECT 'view' AS src, * FROM v_sales;
SELECT 'mv' AS src, * FROM mv_sales;
INSERT INTO orders VALUES (7,'ravi',1000,'2026-01-02');
.print
.print '== 2. after INSERT of a 1000 order: mv is stale =='
SELECT 'view' AS src, * FROM v_sales WHERE created_day='2026-01-02';
SELECT 'mv' AS src, * FROM mv_sales WHERE created_day='2026-01-02';
BEGIN; -- full refresh, atomic for readers
DELETE FROM mv_sales;
INSERT INTO mv_sales SELECT * FROM v_sales;
COMMIT;
.print
.print '== 3. after full refresh =='
SELECT 'mv' AS src, * FROM mv_sales;
CREATE UNIQUE INDEX ux_mv_day ON mv_sales(created_day);
INSERT INTO orders VALUES (8,'meena',10,'2026-01-03');
INSERT INTO mv_sales -- incremental refresh: only the changed day
SELECT created_day, COUNT(*), SUM(amount) FROM orders
WHERE created_day = '2026-01-03' GROUP BY created_day
ON CONFLICT(created_day) DO UPDATE
SET n_orders = excluded.n_orders, revenue = excluded.revenue;
.print
.print '== 4. after incremental refresh of one day =='
SELECT * FROM mv_sales ORDER BY created_day;
-- Real output (SQLite 3.51.0):
-- == 1. right after creation: view and mv agree ==
-- src created_day n_orders revenue
-- ---- ----------- -------- -------
-- view 2026-01-01 3 400
-- view 2026-01-02 3 480
-- src created_day n_orders revenue
-- --- ----------- -------- -------
-- mv 2026-01-01 3 400
-- mv 2026-01-02 3 480
--
-- == 2. after INSERT of a 1000 order: mv is stale ==
-- view 2026-01-02 4 1480 <- the view sees the new order
-- mv 2026-01-02 3 480 <- the stored copy does not
--
-- == 3. after full refresh ==
-- mv 2026-01-01 3 400
-- mv 2026-01-02 4 1480
--
-- == 4. after incremental refresh of one day ==
-- 2026-01-01 3 400
-- 2026-01-02 4 1480
-- 2026-01-03 1 10-- PostgreSQL (cannot be run in the sandbox; no output is shown, behaviour in words)
CREATE MATERIALIZED VIEW daily_sales AS
SELECT created_day, COUNT(*) AS n_orders, SUM(amount) AS revenue
FROM orders GROUP BY created_day;
CREATE UNIQUE INDEX ON daily_sales (created_day);
-- Session A -- Session B
REFRESH MATERIALIZED VIEW daily_sales; SELECT * FROM daily_sales;
-- B waits until A commits (exclusive lock)
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales; SELECT * FROM daily_sales;
-- B returns immediately with the previous contents; after A commits, B sees the new onesQ: What is the difference between a view and a materialized view?
A: A view stores only a query, which the database expands and executes on every read, so it is always current but never faster than the underlying query. A materialized view also stores the result rows, so reads are cheap and can use their own indexes, but the content is only as fresh as the last refresh and it costs storage plus a refresh job. The choice is where you want to pay: at read time or at refresh time.
Q: What refresh strategies exist, and how do you choose?
A: Full refresh recomputes everything and is simple and correct but scales with total data; incremental refresh applies only the delta and scales with the change but is hard for aggregates like MAX and for multi-table joins; on-write maintenance via triggers or application code is always fresh but slows every write and concentrates contention on summary rows; event-driven refresh through CDC is asynchronous and decoupled. I choose based on the required freshness, the data size, and the write rate, starting from a scheduled full refresh and moving right only when measurement shows it is too slow or too stale.
Q: How do you reason about the staleness trade-off?
A: Staleness is bounded by refresh interval plus refresh duration, so I first ask the product what age is acceptable and turn it into a number. Reports and leaderboards tolerate minutes; a checkout or balance check does not and must read the source of truth. I also expose a last-refreshed timestamp and alert when a refresh runs longer than its interval, since that is when the view silently falls behind.
Q: What does REFRESH MATERIALIZED VIEW CONCURRENTLY do in PostgreSQL, and what does it cost?
A: It builds the new result aside, diffs it against the existing rows, and applies only inserts, updates and deletes, so readers keep seeing the old contents instead of blocking on an exclusive lock as they do with a plain refresh. The costs are more total work and temporary space, a required unique index that covers all rows, no use on a never-populated view, and dead rows that autovacuum must clean up. It is the right choice for a hot read endpoint and unnecessary for a nightly view nobody reads during the refresh.
Q: When would you use a materialized view instead of a cache or a denormalized table?
A: Use a materialized view for heavy, shared, aggregate results that you want to query with SQL and index, and where minutes of staleness are fine, because the database owns the definition and application code cannot forget to maintain it. Use a cache for hot individual objects with a TTL and explicit invalidation, and a denormalized table when a row-level read path needs exact freshness and you can guarantee every write path updates the copy. The wrong choice is a materialized view for data that must be current, or a cache for a large aggregate you also need to filter and join.
Follow-up: The nightly refresh is now taking longer than the time between refreshes. What do you do?
A: First confirm whether the time is the query itself (missing indexes on the base tables, a full scan of history) or lock waiting, using EXPLAIN on the defining query. Then reduce the work: partition the base data so old, immutable periods are not recomputed, move to an incremental summary table that only recomputes changed days or groups, or split one giant view into smaller ones. If freshness must improve as well, that is the signal to maintain the aggregate from a change stream instead of recomputing it.
Follow-up: How would you make an incremental refresh correct when rows can be updated or deleted?
A: Track which groups were touched (for example via a changed-keys table filled by triggers or from CDC events), then recompute those groups from the base table rather than trying to adjust the old totals arithmetically, and upsert the result. Recomputing a whole group is idempotent, so a retried job cannot double count, whereas "add the delta" logic breaks on retries and on deletes. The SQLite demo above does exactly this for one day.
Follow-up: Can a materialized view be read inside a transaction alongside live tables and give a consistent picture?
A: Not a consistent picture of the same instant: the view reflects the base tables as of its last refresh while live tables reflect now, so joining them can produce numbers that disagree with each other. If a single request needs both, either accept and document the skew, or compute everything from the live tables for that request.
Materialized views appear in system design whenever an expensive read is shared by many users: an analytics dashboard over an orders table, a "top sellers this week" strip, per-tenant usage totals for billing screens, or pre-aggregated features for a model. The design question is always where the aggregation runs and how stale it may be. A small system can run REFRESH MATERIALIZED VIEW CONCURRENTLY from a scheduler on the primary or a replica; a larger one moves the aggregation to a warehouse or maintains counters from an event stream, and the database view becomes just one implementation of a read model, exactly the idea in CQRS. It also pairs with caching: the view removes the heavy computation, and a short-TTL cache in front of it removes the repeated reads. For an AI backend the same pattern computes usage and cost reports, or keeps a per-document statistics table that retrieval code can filter on without scanning raw event logs.
You are buying cheap reads with staleness, storage, and an operational job that can fail or fall behind. Full refresh is simple but its cost tracks total data and (without CONCURRENTLY) it blocks readers; concurrent and incremental refresh keep readers happy but add complexity, extra I/O and vacuum pressure, and put correctness in your hands. It is the wrong choice when the data must be current for a decision, when the base data changes so fast that refreshes never finish, when the query is already fast enough with the right index, or when many different filter combinations mean you would need a view per combination. In those cases fix the query and indexes first, then consider a cache for hot objects or an event-driven read model, and keep the source of truth as the only thing that authorizes writes.