Materialized Views

Hook: Interviewers love this topic because it checks whether you know the difference between saving a query and saving its result.

Question: What are materialized views in SQL?

Answer: A materialized view is a stored copy of a query result. A normal view is just a saved SQL definition that runs every time you query it, but a materialized view stores rows on disk, so reads are much faster. The trade-off is freshness: the data can become stale until you refresh it.

Interview-Ready Answer: I think of a materialized view as a physically stored snapshot of a query. I use it when the same expensive join or aggregate is read many times, like in dashboards or reports. The big benefit is speed on reads, and the big cost is that it does not update automatically, so I need to refresh it. In PostgreSQL, I can also refresh concurrently in some cases, but that requires a unique index and is mainly for reducing blocking, not for making the data live.

What it is

A materialized view is like a table that the database fills from a query. The key idea is simple: the database runs the query once, stores the output, and later queries read that stored output directly. That is why it helps with expensive work such as big joins, grouped totals, or window-heavy reports.

How it works under the hood

1. You define a SELECT statement, often one that is slow when run repeatedly.
2. The database executes that query and writes the result into its own physical storage. This is the materialization step, where materialization means turning a computed result into stored rows.
3. When users query the materialized view, the database reads the stored rows instead of recomputing from the base tables.
4. When the source tables change, the stored result does not change automatically. It stays as a snapshot until you refresh it.
5. A refresh reruns the underlying query and replaces the stored rows. In PostgreSQL, a plain refresh takes a stronger lock, while CONCURRENTLY is designed to let readers keep going, but it needs a unique index and can take longer.

When and why to use it

• Use it for dashboards, analytics, and reporting where a slightly stale answer is acceptable.
• Use it when the base query is expensive and read many times per minute or per hour.
• Do not use it when you need transaction-level freshness, such as a payment balance or live inventory check.

Materialized view vs view vs table

Performance and complexity

The read path is usually much faster because the database avoids redoing joins and aggregates. If the original report takes 8 seconds over 50 million rows, the materialized view might answer in 20 to 300 milliseconds, depending on its size and indexes. Refresh cost is roughly the cost of running the underlying query again, plus writing the output. Storage cost is the size of the stored result, plus any indexes you add.

Think in simple complexity terms: reading a materialized view is closer to scanning the result set, while refreshing is closer to recomputing the original query. So the work moves from every read to every refresh.

Important edge cases

• It can be stale even when the base tables changed successfully. That is normal, not a bug.
• Refreshing can block or slow readers unless you use a concurrent refresh mode supported by your database.
• Indexes on the materialized view help read speed, but they do not remove refresh cost.
• Some databases differ in syntax and support. In PostgreSQL, materialized views were added in 9.3, and REFRESH ... CONCURRENTLY arrived in 9.4.

Real-World Story: Imagine an e-commerce checkout service that powers a finance dashboard showing revenue by day, by region, and by product category. The raw report joins orders, payments, refunds, and product tables, then groups millions of rows. Without a materialized view, every dashboard load takes several seconds and piles work onto the primary database.

The team adds a materialized view that stores the daily summary and refreshes it every 5 minutes. Now the dashboard opens quickly, and the database stops doing the same expensive aggregation over and over.

What goes wrong when someone misunderstands it: a developer assumes the view is live and uses it for a near-real-time alert on failed payments. The source tables update immediately, but the materialized view is still waiting for its next refresh, so the alert is late. Users see old numbers in the dashboard, logs show the refresh job missed its schedule, and support gets reports like “the site says yesterday’s revenue is still today’s total.” The symptom is not bad SQL syntax; it is stale data being mistaken for fresh data.

Follow-up & Tricky Questions:

• Follow-up: How do you keep a materialized view fresh? You refresh it on a schedule, after ETL jobs, or on demand. The key interview point is that freshness is an operational decision, not something the database does automatically for you.
• Follow-up: Can you index a materialized view? Yes. In many systems, including PostgreSQL, you can create indexes on the stored result to speed up filtering and joins against the materialized view itself.
• Follow-up: What does REFRESH MATERIALIZED VIEW CONCURRENTLY do? It lets reads continue while the refresh runs, but it needs a unique index and usually uses more time and space. It is a good fit when uptime matters more than refresh speed.
• Follow-up: When would you choose a materialized view over a cache in application code? Use a materialized view when you want the database to own the snapshot and when multiple tools or reports need the same precomputed result. Application caches are often closer to a single service and need extra invalidation logic.
• Follow-up: What happens if the base tables change during refresh? The refresh uses the database's refresh semantics and replaces the stored snapshot as a unit. The important idea is still that the materialized view is a separate stored object, not a live pointer to the base tables.
• Tricky: Is a materialized view just a cached query? Not exactly. It is like a persistent cache that the database manages as a first-class object, with refresh semantics and optional indexes.
• Tricky: Does a materialized view update itself when source rows change? No. That is the main trap. If you forget to refresh, the view can return old data even though the source tables are current.
• Tricky: Can you use a materialized view for strict real-time inventory? Usually no, because any refresh delay creates a correctness gap. For live transactional truth, read the base tables or use a design built for real-time consistency.

Common Mistakes:

• Mistake: Treating a materialized view like a normal view. Correction: A normal view computes on read; a materialized view stores results and must be refreshed.
• Mistake: Forgetting about staleness. Correction: Always ask, “How fresh does this data need to be?” before choosing one.
• Mistake: Assuming refresh is free. Correction: Refresh reruns the expensive query and writes the result, so schedule it carefully.
• Mistake: Using it for every query optimization. Correction: If an index on the base tables solves the problem, that is often simpler and cheaper.

Memory Hook: Photo, not video. A materialized view is a snapshot photo of query results: fast to look at, but it can be out of date until someone takes a new picture.

Cheat Sheet:

• Stores the result of a query physically.
• Faster reads, slower or scheduled refreshes.
• Great for dashboards, summaries, and reports.
• Not automatically fresh; staleness is the trade-off.
• Can often be indexed like a table.
• In PostgreSQL, CONCURRENTLY can reduce blocking, but needs a unique index.

Practice Tasks:

• Create a daily summary materialized view from an orders table.
• Add an index to speed up a common filter, then compare query plans before and after.
• Insert new rows, query the stale view, refresh it, and verify the numbers change.