How to Create a Materialized View in PostgreSQL

How do I create and refresh a materialized view in PostgreSQL?

A materialized view stores the result of a query on disk, letting you query a pre-computed snapshot that you can manually or automatically refresh.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does a materialized view do?

A materialized view caches a query result as a physical table, cutting execution time for expensive joins or aggregations. Unlike a normal view, the data is fixed until you refresh it.

When should I choose a materialized view?

Use one when reporting queries hit large fact tables, dashboards need sub-second latency, or you want to isolate analytical workloads from OLTP traffic.

How do I create a materialized view?

Run CREATE MATERIALIZED VIEW with a SQL SELECT. Include WITH DATA to populate immediately or WITH NO DATA to defer.

CREATE MATERIALIZED VIEW customer_lifetime_value AS SELECT c.id, c.name, SUM(o.total_amount) AS lifetime_value, COUNT(o.id) AS orders_count FROM Customers c JOIN Orders o ON o.customer_id = c.id GROUP BY c.id, c.name;

How do I refresh a materialized view?

Run REFRESH MATERIALIZED VIEW. Add CONCURRENTLY to avoid blocking reads; index the view first to enable it.

REFRESH MATERIALIZED VIEW CONCURRENTLY customer_lifetime_value;

Can I automate refreshes?

Yes. Schedule REFRESH MATERIALIZED VIEW in cron, PgAgent, or a managed scheduler so dashboards stay current without manual effort.

What are best practices?

Index the columns you query most. Add WITH NO DATA in migrations to shorten deploys, then populate off-peak. Combine multiple small queries into one view when possible.

Common mistakes and fixes

Missing indexes: Without indexes on the materialized view, refreshes with CONCURRENTLY fail. Create indexes first.

Over-refreshing: Refreshing every minute on a low-change dataset wastes I/O. Match schedule to business need.

Example: product stock snapshot

CREATE MATERIALIZED VIEW latest_product_stock AS SELECT id, name, price, stock FROM Products WHERE stock > 0; -- Later REFRESH MATERIALIZED VIEW latest_product_stock;

Why not always use materialized views?

They occupy disk space, can become stale, and require refresh logic. For lightweight or rapidly changing queries, use normal views instead.

Why How to Create a Materialized View in PostgreSQL is important

How to Create a Materialized View in PostgreSQL Example Usage


-- Summarize each customer's lifetime spend
CREATE MATERIALIZED VIEW customer_lifetime_value AS
SELECT c.id,
       c.name,
       SUM(o.total_amount) AS lifetime_value
FROM   Customers c
JOIN   Orders o ON o.customer_id = c.id
GROUP  BY c.id, c.name;

-- Keep it fresh each night
REFRESH MATERIALIZED VIEW customer_lifetime_value;

How to Create a Materialized View in PostgreSQL Syntax


CREATE MATERIALIZED VIEW [IF NOT EXISTS] view_name
[ (column_name [, ...]) ]
[ USING method ]
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace_name ]
AS query
[ WITH [ NO ] DATA ];

REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] view_name;

-- Ecommerce example
CREATE MATERIALIZED VIEW customer_order_totals AS
SELECT c.id,
       c.name,
       COUNT(o.id)              AS order_count,
       SUM(o.total_amount)      AS total_spent
FROM   Customers c
JOIN   Orders o ON o.customer_id = c.id
GROUP  BY c.id, c.name
WITH DATA;

Common Mistakes

Using REFRESH MATERIALIZED VIEW CONCURRENTLY without first creating a unique index on the materialized view. Fix by adding a unique index so the concurrent refresh can work.
Refreshing far more often than data changes, leading to unnecessary I/O and lock contention. Fix by aligning the refresh schedule with actual business requirements.