Category: Databases & Data Tags: materialized-views database-performance query-optimization

What Is a Materialized View?

By Oleksandr Andrushchenko — Published on
0 Likes
0 Dislikes

A materialized view is a database object that stores the result of a query physically so future reads can access the precomputed result instead of repeatedly executing the original query.

Materialized views are useful when a query is expensive but its result does not need to reflect every source-table change immediately. They trade additional storage and refresh work for faster reads.

Materialized View Example
Materialized View Example

Table of Contents

Why Materialized Views Exist

Some database queries are expensive because they scan large tables, join multiple relations, calculate aggregates, or perform complex transformations.

Consider a dashboard that displays revenue by customer and month:

SELECT
    customer_id,
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY
    customer_id,
    DATE_TRUNC('month', created_at);

On a small table, executing this query repeatedly may be inexpensive.

On hundreds of millions of orders, the same query can require substantial CPU, I/O, aggregation, and memory every time the dashboard loads.

Dashboard Request
       │
       ↓
Scan Orders
       │
       ↓
Filter Rows
       │
       ↓
Group Millions of Rows
       │
       ↓
Calculate Aggregates
       │
       ↓
Return Result

If the underlying data changes continuously but the dashboard only needs data accurate to the last few minutes, recalculating the entire result for every request is unnecessary.

A materialized view moves that expensive computation away from the read path:

Source Tables
      │
      ↓
Expensive Query
      │
      ↓
Materialized Result
      │
      ├── Dashboard Request
      ├── Dashboard Request
      ├── Dashboard Request
      └── Dashboard Request

The expensive query runs during refresh rather than every time the result is read.

How a Materialized View Works

A materialized view stores the output of a query in a table-like structure.

Conceptually:

orders ──────┐
customers ───┼──→ Query ──→ Materialized View ──→ Fast Reads
products ────┘

The database remembers the query definition and stores the generated rows.

Reading the materialized view therefore does not normally require rerunning the original query against all source tables.

Creating a Materialized View

In PostgreSQL, a materialized view can be created with:

CREATE MATERIALIZED VIEW monthly_customer_revenue AS
SELECT
    customer_id,
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY
    customer_id,
    DATE_TRUNC('month', created_at);

When the materialized view is created with data, the database executes the query and stores its result.

The result might look like:

customer_id   month        order_count   revenue

101           2026-08-01        38       4,820
101           2026-09-01        41       5,310
102           2026-08-01        17       1,920
102           2026-09-01        23       2,740

These rows are physically stored rather than recalculated every time they are requested.

Querying a Materialized View

The application can query the stored result like a regular relation:

SELECT
    month,
    order_count,
    revenue
FROM monthly_customer_revenue
WHERE customer_id = 101
ORDER BY month DESC;

The read path becomes:

Dashboard
    │
    ↓
Materialized View
    │
    ↓
Precomputed Rows

The original aggregation over the complete orders table is no longer part of every dashboard request.

View vs Materialized View

A normal database view and a materialized view can be defined using similar SQL queries, but their read behavior is fundamentally different.

A normal view stores the query definition.

View
 │
 └── stored query definition

SELECT from view
        │
        ↓
execute underlying query
        │
        ↓
source tables

A materialized view stores the query result.

Materialized View
        │
        └── stored query result

SELECT from materialized view
        │
        ↓
read stored rows
Characteristic View Materialized View
Stores query definition Yes Yes
Stores query result No Yes
Always reflects source data Query sees source data according to normal transaction visibility No
Requires refresh No Yes
Additional storage Minimal Yes
Useful for expensive repeated queries Does not precompute the result Yes

A normal view is primarily an abstraction over a query. A materialized view is also a performance and data-processing mechanism.

Refreshing a Materialized View

The main trade-off of a materialized view is freshness.

Suppose the view is generated at 10:00:

10:00  Materialized View Refreshed
          │
10:01     New Order
10:02     New Order
10:03     Order Updated
          │
10:04  Query Materialized View

Unless another refresh has occurred, the stored result still represents the state calculated at 10:00.

In PostgreSQL, a refresh can be triggered with:

REFRESH MATERIALIZED VIEW monthly_customer_revenue;

The backing query is executed again and the stored contents are replaced with refreshed results.

Scheduled Refresh

A common strategy is refreshing on a fixed schedule:

00:00 ── refresh
00:05 ── refresh
00:10 ── refresh
00:15 ── refresh
00:20 ── refresh

This works well when the acceptable freshness requirement is known.

Examples include:

  • analytics refreshed every five minutes;
  • hourly operational reports;
  • daily financial summaries;
  • nightly reporting datasets.

The refresh interval becomes part of the product's data-consistency contract.

On-Demand Refresh

A materialized view can also be refreshed when a particular event occurs.

Import Completes
      │
      ↓
Refresh Materialized View
      │
      ↓
Publish Updated Report

This can work well for batch-oriented systems where the source data changes in clearly defined processing cycles.

Refreshing after every individual row modification usually removes much of the benefit. If the materialized result must be synchronized after every write, a different design may be more appropriate.

Concurrent Refresh

A refresh can itself become an operational problem when applications need to query the materialized view continuously.

PostgreSQL provides:

REFRESH MATERIALIZED VIEW CONCURRENTLY
monthly_customer_revenue;

This allows concurrent reads to continue while the materialized view is refreshed.

For PostgreSQL, concurrent refresh requires an appropriate UNIQUE index on the materialized view.

CREATE UNIQUE INDEX
monthly_customer_revenue_key
ON monthly_customer_revenue (
    customer_id,
    month
);

The operational trade-off is important:

Regular Refresh

Refresh ──────────────→ complete
   │
   └── readers may be blocked


Concurrent Refresh

Old Result ───────────────→ available to readers
           Refresh Running
                    │
                    ↓
              New Result

Concurrent refresh improves read availability during refresh, but it has its own requirements and overhead. Only one refresh can run against a given PostgreSQL materialized view at a time.

Data Freshness and Staleness

A materialized view intentionally separates the source-of-truth write path from the optimized read representation.

This means the system needs an explicit staleness budget.

For example:

Payment balance:
acceptable staleness = 0 seconds

Operations dashboard:
acceptable staleness = 30 seconds

Analytics report:
acceptable staleness = 15 minutes

Historical report:
acceptable staleness = 24 hours

The appropriate refresh strategy follows from that requirement.

A materialized view is a poor choice when a request must always observe the latest committed source-table state.

It can be an excellent choice when slightly stale data is acceptable and query cost is much more important than immediate freshness.

This is a similar architectural trade-off to other forms of derived data: faster reads are obtained by accepting synchronization work and some interval during which the derived representation can lag behind its source.

Indexing Materialized Views

Precomputing an expensive query does not automatically make every query against the result efficient.

Suppose a materialized view contains ten million aggregated rows:

SELECT *
FROM monthly_customer_revenue
WHERE customer_id = 918273;

Without an appropriate index, the database may still scan a large part of the materialized result.

An index can optimize the expected read pattern:

CREATE INDEX
monthly_customer_revenue_customer_idx
ON monthly_customer_revenue (
    customer_id,
    month DESC
);

The optimization path becomes:

Expensive Source Query
        │
        ↓
Materialized View
        │
        ↓
      Index
        │
        ↓
Fast Application Query

Materialized-view indexes should be designed around the queries that consume the materialized data, not necessarily around the indexes on the original source tables.

What Is Database Indexing? covers index lookup behavior, selectivity, composite indexes, and the trade-offs involved in maintaining additional indexes.

Materialized Views for Aggregation

Aggregation is one of the strongest use cases for materialized views.

Suppose an events table receives hundreds of millions of rows:

events

user_id
event_type
created_at
country
device
duration_ms
...

A dashboard needs daily statistics:

SELECT
    DATE(created_at) AS day,
    country,
    COUNT(*) AS event_count,
    COUNT(DISTINCT user_id) AS active_users
FROM events
GROUP BY
    DATE(created_at),
    country;

Executing this aggregation for every dashboard request repeatedly processes the same historical events.

A materialized view can reduce the dataset to:

day          country   event_count   active_users

2026-10-01   US        8,421,918     912,841
2026-10-01   CA        1,038,218     118,420
2026-10-01   DE          881,931      96,210
2026-10-02   US        8,902,114     944,028

Instead of scanning hundreds of millions of event rows, the dashboard reads a much smaller pre-aggregated dataset.

The performance difference can be substantial when many users repeatedly request the same dimensions and aggregations.

Materialized Views for Expensive Joins

Materialized views can also precompute expensive joins.

Consider a reporting query combining:

orders
  │
  ├── customers
  │
  ├── order_items
  │
  ├── products
  │
  └── regions

If the same join is executed for every reporting request, the database repeatedly performs similar work.

A materialized view can precompute the reporting representation:

CREATE MATERIALIZED VIEW order_reporting AS
SELECT
    o.id AS order_id,
    o.created_at,
    c.id AS customer_id,
    c.name AS customer_name,
    r.name AS region_name,
    COUNT(oi.id) AS item_count,
    SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN customers c
    ON c.id = o.customer_id
JOIN regions r
    ON r.id = c.region_id
JOIN order_items oi
    ON oi.order_id = o.id
GROUP BY
    o.id,
    o.created_at,
    c.id,
    c.name,
    r.name;

Reporting queries can then read the flattened, precomputed result.

This is particularly useful when source tables are optimized for transactional writes while the materialized representation is optimized for a different read pattern.

Materialized View vs Cache

A materialized view and a cache both avoid repeating expensive work, but they operate differently.

Characteristic Materialized View Application Cache
Location Database Usually application or cache system
Representation Rows and columns Arbitrary values or objects
Queryable Yes, using SQL Usually key-oriented
Can be indexed Often yes Depends on cache technology
Refresh model Database-specific refresh TTL, invalidation, replacement, or write policy
Best fit Reusable derived datasets Repeated application access to cached values

For example, caching the response:

GET /dashboard/customer/101

cache key:
dashboard:customer:101

is different from maintaining a materialized dataset containing monthly statistics for every customer.

The materialized view remains queryable:

SELECT customer_id, SUM(revenue)
FROM monthly_customer_revenue
WHERE month >= DATE '2026-01-01'
GROUP BY customer_id
ORDER BY SUM(revenue) DESC
LIMIT 100;

That flexibility is one of its major advantages over simple key-value caching.

Caching Strategies covers cache-aside, write-through, write-behind, invalidation, and related caching trade-offs.

Materialized View vs Summary Table

A manually maintained summary table can solve many of the same problems as a materialized view.

For example:

daily_sales_summary

day
store_id
order_count
revenue

An application or data pipeline can update this table incrementally as new orders arrive.

The difference is ownership of the maintenance logic.

Materialized View

Query Definition
      │
      ↓
Database Refresh
      │
      ↓
Stored Result


Summary Table

Application / Pipeline
      │
      ↓
INSERT / UPDATE Logic
      │
      ↓
Stored Result

A materialized view is attractive when the database can conveniently recompute the derived dataset from its source query.

A summary table can be more appropriate when updates must be incremental, refresh logic is complex, data arrives from several systems, or the application needs complete control over how the derived representation changes.

When to Use a Materialized View

Materialized views work particularly well when:

  • the underlying query is expensive;
  • the same result or dataset is read frequently;
  • source data changes less frequently than it is read;
  • some data staleness is acceptable;
  • the result contains expensive aggregations;
  • the query joins several large tables;
  • reporting workloads should be isolated from repeated transactional computation;
  • the precomputed result can be significantly smaller than its source data;
  • the derived dataset benefits from its own indexes.

A typical workload looks like:

Source writes:          continuous
Expensive aggregation:  1 refresh / 5 min
Dashboard reads:        10,000 / 5 min

Computing the expensive aggregation once and reusing the result can be much cheaper than executing it 10,000 times.

When Not to Use a Materialized View

A materialized view is not automatically the right solution for every slow query.

It may be a poor fit when:

  • the result must always reflect the latest committed data;
  • the source changes so frequently that refresh cost becomes excessive;
  • the original query is already inexpensive;
  • the materialized result is rarely read;
  • refreshing requires scanning enormous datasets too frequently;
  • the required derived state is better maintained incrementally;
  • a missing source-table index is the actual performance problem;
  • the application really needs a cache for individual objects or responses;
  • the workload belongs in a dedicated analytical system.

Materializing an inefficient query can hide the original cost from readers while introducing a large recurring refresh job.

The correct question is not simply:

"Would this query be faster from a materialized view?"

A better question is:

"Is the cost of storing and refreshing this result
lower than repeatedly computing it at the required
freshness level?"

Production Design Example

Consider a SaaS platform with 50 million completed orders. The main orders table supports transactional operations:

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    account_id BIGINT NOT NULL,
    status TEXT NOT NULL,
    total_amount NUMERIC(12, 2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
);

The application dashboard displays monthly revenue for each account.

Executing the raw aggregation on every request would require repeatedly processing large numbers of order rows:

SELECT
    account_id,
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY
    account_id,
    DATE_TRUNC('month', created_at);

Assume the query takes 4.8 seconds over the complete dataset.

The dashboard receives 200 requests per second. Repeating the aggregation for each request would create an unnecessary analytical workload on the transactional database.

A materialized view can precompute the result:

CREATE MATERIALIZED VIEW account_monthly_revenue AS
SELECT
    account_id,
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY
    account_id,
    DATE_TRUNC('month', created_at);

Add a unique index:

CREATE UNIQUE INDEX
account_monthly_revenue_key
ON account_monthly_revenue (
    account_id,
    month
);

The application query becomes:

SELECT
    month,
    order_count,
    revenue
FROM account_monthly_revenue
WHERE account_id = :account_id
ORDER BY month DESC
LIMIT 24;

The architecture is now:

                    ┌──────────────────┐
Orders Writes ─────→│      orders      │
                    └────────┬─────────┘
                             │
                       every 5 minutes
                             │
                             ↓
                    ┌──────────────────┐
                    │ Materialized View│
                    └────────┬─────────┘
                             │
                     indexed reads
                             │
                             ↓
                    ┌──────────────────┐
                    │    Dashboard     │
                    └──────────────────┘

If the product accepts data that can be up to five minutes old, the refresh can run every five minutes:

REFRESH MATERIALIZED VIEW CONCURRENTLY
account_monthly_revenue;

The unique index supports the PostgreSQL concurrent-refresh requirement while also providing a useful lookup path for account-based reads.

The application should still make the freshness model visible. For example, the dashboard can expose:

Data updated: 2026-10-03 20:35 UTC

That is better than presenting stale derived data as if it were guaranteed to be real time.

Production monitoring should cover both read performance and refresh behavior.

Useful metrics include:

  • refresh duration;
  • refresh success and failure count;
  • time since last successful refresh;
  • materialized-view size;
  • source-query execution time;
  • read latency;
  • refresh CPU and I/O impact;
  • lock or blocking behavior during refresh;
  • growth in the number of materialized rows.

For example:

Refresh interval:              5 min
Refresh p95 duration:         18 sec
Time since last refresh:       2 min
Materialized rows:           1.8 M
Materialized size:           620 MB
Dashboard query p95:          14 ms
Refresh failures:              0

An alert should fire if the view stops refreshing successfully even when dashboard queries continue returning data.

Otherwise, a refresh failure can silently turn a five-minute staleness budget into hours of stale data.

Common Materialized View Mistakes

  • Assuming the data is automatically current. Materialized results can become stale until refreshed.
  • Refreshing too frequently. Expensive refreshes can consume more resources than the reads they were intended to optimize.
  • Refreshing too infrequently. The resulting staleness may violate product requirements.
  • Ignoring refresh failures. Queries can continue succeeding while returning increasingly old data.
  • Not indexing the materialized result. Precomputation does not eliminate the need to optimize queries against the stored dataset.
  • Using a materialized view to hide a missing source-table index. Fix the underlying query when the original problem is ordinary query optimization.
  • Putting real-time correctness-critical data behind an asynchronous refresh. The architecture must match the freshness requirement.
  • Ignoring refresh locking behavior. A large refresh can affect production readers depending on the database and refresh mode.
  • Creating many overlapping materialized views. Storage and refresh workloads can grow quickly.
  • Assuming refresh is cheap because reads are fast. The expensive source query still has to execute somewhere.
  • Using materialized views where incremental derived tables are required. Full or large refresh operations may become impractical at sufficient scale.
  • Not recording the last successful refresh time. Data freshness should be observable.

Production Checklist

  • Measure the original query before introducing materialization.
  • Define the maximum acceptable data staleness.
  • Choose a refresh strategy based on that staleness budget.
  • Measure refresh duration under production-scale data.
  • Understand whether refresh blocks readers.
  • Use concurrent refresh where appropriate and supported.
  • Create indexes for the materialized view's actual read patterns.
  • Monitor the last successful refresh time.
  • Alert on refresh failures.
  • Monitor refresh CPU, I/O, and database load.
  • Track materialized-view storage growth.
  • Avoid unnecessary overlapping materialized datasets.
  • Keep the source query understandable and maintainable.
  • Re-evaluate refresh cost as source tables grow.
  • Expose data freshness when users need to understand it.
  • Consider a summary table when incremental maintenance is more appropriate.
  • Consider caching when the requirement is primarily key-based application caching.
  • Consider a dedicated analytical database when analytical workloads outgrow the transactional database.

Frequently Asked Questions

Materialized views are simple conceptually, but most production decisions involve refresh behavior, acceptable staleness, and the cost of maintaining the stored result.

Is a Materialized View a Table?

It behaves like a table in several important ways because its rows are physically stored and can be queried directly.

However, it is derived from a stored query definition and is refreshed from that query rather than being treated as an ordinary application-owned table.

Is a Materialized View Automatically Updated?

Not necessarily. Refresh behavior depends on the database system.

In PostgreSQL, changes to the underlying tables do not automatically update the materialized view. It must be refreshed explicitly, usually through a scheduled job or application-controlled process.

Can a Materialized View Have Indexes?

Yes, in systems such as PostgreSQL. Indexing the materialized result can be essential when it contains a large number of rows.

The indexes should reflect how applications query the materialized dataset rather than simply copying the source-table indexes.

How Often Should a Materialized View Be Refreshed?

The refresh interval should be derived from the maximum acceptable staleness and the cost of refreshing.

If data may be five minutes old, refreshing approximately every five minutes can be reasonable. If a report only changes meaningfully once per day, a nightly refresh may be sufficient.

Refreshing more frequently is not automatically better because every refresh consumes database resources.

Should a Materialized View Be Used for Real-Time Data?

Usually not when strict real-time freshness is required and the database's materialized-view mechanism depends on periodic recomputation.

For near-real-time derived data, incremental processing, change streams, application-maintained summary tables, or specialized analytical systems may provide a better architecture.

Conclusion

A materialized view stores the result of a query so applications can read precomputed data instead of repeatedly executing expensive joins, aggregations, and scans.

The performance improvement comes with two costs: additional storage and synchronization. Once the result is materialized, it can become stale until the next refresh.

The central design decision is therefore not simply whether a query is expensive. It is whether the result can be reused often enough, and remain stale long enough, to justify precomputing and maintaining it.

Key takeaway: a materialized view moves expensive computation from the read path to a refresh process, trading immediate freshness for faster and more predictable reads.

Author

Oleksandr Andrushchenko

Oleksandr Andrushchenko

Software Engineer & Solution Architect
Nov 03, 2025 6 217
Enjoyed this article?

Support Oleksandr Andrushchenko

Buy me a coffee

This helps Oleksandr Andrushchenko continue creating useful content

Related articles

Comments (0)