Materialized Views: Update & Refresh

You’ve built views that give you a clean, reusable way to query your database. But what happens when that view is slow? Every query against a regular view re-executes the underlying SQL, and when that SQL involves heavy aggregations or joins across millions of rows, your dashboard or API can grind to a halt. That’s the problem this lesson solves: how to update views with materialized views—a PostgreSQL feature that physically stores the results of a query and lets you refresh them on demand. By the end, you’ll know how to convert a sluggish view into a lightning-fast materialized one, keep it fresh with REFRESH, and avoid the common pitfalls that trip up even experienced developers.

The problem this lesson solves

Imagine you have a reporting view that aggregates sales data by region:

CREATE VIEW sales_summary AS
SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;

Every time you run SELECT * FROM sales_summary, PostgreSQL executes the entire aggregation from scratch. On a large table, that’s a heavy cost—especially if the view is queried frequently. The query planner can’t cache the result, and each user request pays the full price. Your application slows down, and your database spends precious CPU cycles redoing the same work.

Materialized views solve this by storing the query result on disk. Think of it as a snapshot of your data at a point in time. Instead of recomputing every time, you query the snapshot. When your underlying data changes, you explicitly tell PostgreSQL to refresh the snapshot. This gives you the same logical abstraction as a view, but with near-instant reads. The trade-off is that the data can become stale—you’re not always seeing the absolute latest row, but you can control when that happens.

Core concept / mental model

What is a materialized view?

A materialized view is a database object that stores the result set of a query physically, like a table. Unlike a regular view, which is just a saved query, a materialized view has data that persists. You can create indexes on it, and you can refresh it to update the data to match the current base tables.

Regular view vs. materialized view

Why "update" is the wrong word—refresh is what you need

The word "update" in the context of materialized views is misleading. You can’t run UPDATE on a materialized view like you would a table. Instead, you use REFRESH MATERIALIZED VIEW to rebuild its contents from the base query. This is a crucial distinction: you aren’t changing individual rows; you’re recalculating the whole snapshot. In PostgreSQL, there is no built-in incremental refresh (as of version 16), so every refresh is a full recomputation.

How it works step by step

Creating a materialized view

The syntax is nearly identical to creating a regular view, but with the keyword MATERIALIZED:

CREATE MATERIALIZED VIEW sales_summary_mv AS
SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;

This creates the object and immediately populates it with the current data. If the base query is expensive, the creation can take time. But once done, you have a fast path for reads.

Querying a materialized view

You query it just like a table or a regular view:

SELECT * FROM sales_summary_mv;

Because the data is stored, this is a plain table scan (or index scan if you add indexes). No aggregation logic runs at query time.

Refreshing the data

When your base tables change, you need to refresh the view to see updated data:

REFRESH MATERIALIZED VIEW sales_summary_mv;

This runs the underlying query again and replaces the stored data. While the refresh runs, the view is locked, so concurrent reads will block until it finishes. For a large view, that can cause downtime. In PostgreSQL 9.4 and later, you can use REFRESH MATERIALIZED VIEW CONCURRENTLY, which requires a unique index and allows reads to continue during the refresh, but it takes longer and needs more resources.

Adding indexes for even faster reads

A materialized view becomes a table-like object, so you can index it:

CREATE INDEX idx_sales_mv_region ON sales_summary_mv (region);

This can dramatically speed up queries that filter on the indexed columns.

Hands-on walkthrough

Let’s walk through a complete example. We’ll start with a regular view, notice its performance, then create a materialized view and refresh it.

Step 1: Set up sample data

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    region TEXT NOT NULL,
    amount NUMERIC NOT NULL,
    sale_date DATE NOT NULL
);

INSERT INTO sales (region, amount, sale_date)
SELECT 'North', (random() * 1000)::int, '2025-01-01'::date + (random() * 365)::int
FROM generate_series(1, 10000);

Now let’s add a few more regions:

INSERT INTO sales (region, amount, sale_date)
SELECT 'South', (random() * 1000)::int, '2025-01-01'::date + (random() * 365)::int
FROM generate_series(1, 10000);

Step 2: Create a regular view

CREATE VIEW sales_summary AS
SELECT region, COUNT(*) AS order_count, SUM(amount) AS total_sales
FROM sales
GROUP BY region;

Query it—it works fine, but it recomputes every time.

Step 3: Create a materialized view

CREATE MATERIALIZED VIEW sales_summary_mv AS
SELECT region, COUNT(*) AS order_count, SUM(amount) AS total_sales
FROM sales
GROUP BY region;

Check the contents:

SELECT * FROM sales_summary_mv;

You’ll see two rows (North and South).

Step 4: Insert new data and refresh

Add more sales:

INSERT INTO sales (region, amount, sale_date)
SELECT 'West', (random() * 1000)::int, '2025-01-01'::date + (random() * 365)::int
FROM generate_series(1, 5000);

The materialized view still shows only North and South because it hasn’t been refreshed. Now refresh:

REFRESH MATERIALIZED VIEW sales_summary_mv;

Now it includes West. This is the core workflow: create once, refresh on schedule or on demand.

Step 5: Add an index and test performance

CREATE INDEX idx_sales_mv_region ON sales_summary_mv (region);

Now you can query with fast lookups:

SELECT * FROM sales_summary_mv WHERE region = 'North';

Expected output:

 region | order_count | total_sales
--------+-------------+-------------
 North  |       10000 |  4967048.58

Compare options / when to choose what

You have several ways to handle query performance in PostgreSQL. Here’s a quick comparison to help you decide.

When to choose materialized views

• Your reporting queries aggregate large datasets and are run frequently.
• You can accept a few minutes of staleness (e.g., dashboards, daily reports).
• You want to avoid recomputing heavy joins on every request.

When to avoid them

• If your application requires real-time consistency (e.g., banking transactions).
• If your data changes every second and you need to see the latest row immediately.
• If the base query result is small—a regular view might be fine.

Pro tip: Use REFRESH MATERIALIZED VIEW CONCURRENTLY when you need to keep the view available for reads during refresh. Just remember to create a unique index first.

Troubleshooting & edge cases

1. "cannot insert into view" error

If you try INSERT INTO sales_summary_mv, you’ll get an error like:

ERROR: cannot insert into view "sales_summary_mv"

Materialized views are read-only. You must refresh them, not update them directly.

2. REFRESH is slow or locks the view

By default, REFRESH locks the view, blocking reads. For large views, this can cause downtime. Solution: use CONCURRENTLY and ensure a unique index exists.

CREATE UNIQUE INDEX idx_sales_mv_id ON sales_summary_mv (region);
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary_mv;

3. Stale data appears after refresh

Make sure the refresh actually commits. Wait for the transaction to commit before querying if you’re in the same session.

4. Index cannot be created on a regular view

You’ll get ERROR: cannot create index on view. You must create a materialized view first.

5. Refresh with CONCURRENTLY fails if no unique index

PostgreSQL requires a unique index to perform concurrent refresh. Create one before attempting it.

What you learned & what's next

You now understand the core difference between regular and materialized views, how to create them, and how to refresh them to stay current. You’ve seen that you can add indexes to materialized views to further speed up reads, and you know the pitfalls like locking and stale data. You’ve applied these skills in a hands-on example, inserting new data and refreshing the view to reflect changes.

This knowledge is essential for building performant data pipelines. As a next step, you’ll want to explore automating refresh with pg_cron or scheduling REFRESH commands using a job scheduler. That will tie your materialized views into a real production workflow, ensuring your dashboards stay up-to-date without manual intervention.