What are views and materialized views?

Question

IT Interview Practice · Accepted Answer

A **view** is a saved query that acts like a virtual table — you query it like a table, but it runs the underlying query each time. A **materialized view** stores the query's *results* physically (refreshed periodically), trading freshness for read speed.

## A regular view — a saved query

```sql
-- define a view
CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE active = true;

-- query it like a table — it runs the underlying SELECT each time
SELECT * FROM active_users WHERE name LIKE 'A%';
```

A view is a **virtual table** — it stores no data itself, just the query definition. Each time you query the view, the underlying query runs against the current data (always fresh).

## Why use views

```text
✓ SIMPLIFY complex queries — encapsulate a complex JOIN/aggregation as a named view
✓ ABSTRACTION — present a clean, stable interface; hide underlying table complexity
✓ SECURITY — grant access to a view exposing only certain columns/rows (hide the rest)
✓ REUSABILITY — define common query logic once, reuse it everywhere
✓ Consistency — everyone uses the same definition of "active_users"
```

Views are great for encapsulating complex logic, providing a stable abstraction over tables, and security (exposing only specific data).

## Materialized views — stored results

```sql
-- stores the RESULTS physically (precomputed) for fast reads
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT month, SUM(amount) AS total FROM orders GROUP BY month;

SELECT * FROM monthly_sales;        -- fast — reads stored results, doesn't recompute
REFRESH MATERIALIZED VIEW monthly_sales;   -- update the stored data when needed
```

A **materialized view** physically stores the query results — so reading it is fast (no recomputation), but the data is only as fresh as the last `REFRESH`. Ideal for expensive aggregations queried frequently but tolerant of slight staleness.

## View vs materialized view trade-off

```text
View              → always FRESH, but re-runs the query each time (slower for complex queries)
Materialized view → FAST reads (precomputed), but data can be STALE until refreshed
→ Use views for abstraction/simplification; materialized views for caching expensive
  query results that are read often and don't need real-time freshness.
```

## Why it matters

Views are a useful database feature for **abstraction, simplification, and security**, and understanding them (and materialized views) is valuable for organizing and optimizing database access.

Regular **views** (saved queries acting as virtual tables) provide real benefits: simplifying complex queries (encapsulating intricate JOINs/aggregations as a clean named view), providing a stable abstraction over tables (so application code or other queries use a consistent interface), enabling **security** (granting access to a view that exposes only certain columns/rows while hiding sensitive data), and promoting reusability (defining common query logic once).

These make views valuable for maintainable, secure database design. **Materialized views** address a different need — **performance**: by physically storing precomputed query results (refreshed periodically), they make reading expensive aggregations fast, at the cost of potential staleness.

Understanding the **trade-off** — regular views are always fresh but re-run the query each time, while materialized views are fast but can be stale until refreshed — is important for choosing correctly (views for abstraction/simplification, materialized views for caching expensive, frequently-read results that tolerate slight staleness).

Since organizing query logic (views for abstraction and security) and optimizing expensive repeated queries (materialized views for caching) are real needs in database work, and since understanding both types and their freshness/performance trade-off helps you apply them appropriately, knowing views and materialized views is useful, practically-relevant knowledge for building maintainable, performant, secure database access.

A regular view — a saved query

Why use views

Materialized views — stored results

View vs materialized view trade-off

Why it matters

Related questions