Materialized view vs regular view decision in PostgreSQL

Introduction

We use views to save queries for easy reuse. Materialized views save the query results physically, while regular views run the query every time.

When you want fast access to complex query results that don't change often.

When you need always up-to-date data and can afford to run the query each time.

When your query takes a long time and you want to avoid running it repeatedly.

When you want to save storage space and don't want to store query results.

When you want to refresh data manually or on a schedule for performance.

Syntax

PostgreSQL

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

CREATE MATERIALIZED VIEW mat_view_name AS
SELECT column1, column2
FROM table_name
WHERE condition
WITH NO DATA; -- optional

A regular view does not store data; it runs the query each time you use it.

A materialized view stores the query result physically and needs to be refreshed to update.

Examples

This regular view shows orders from the last 7 days and runs fresh every time.

PostgreSQL

CREATE VIEW recent_orders AS
SELECT order_id, order_date
FROM orders
WHERE order_date > CURRENT_DATE - INTERVAL '7 days';

This materialized view stores total spent by customers but starts empty until refreshed.

PostgreSQL

CREATE MATERIALIZED VIEW top_customers AS
SELECT customer_id, SUM(amount) AS total_spent
FROM sales
GROUP BY customer_id
ORDER BY total_spent DESC
WITH NO DATA;

This command updates the materialized view with current data.

PostgreSQL

REFRESH MATERIALIZED VIEW top_customers;

Sample Program

This example shows creating a regular view and a materialized view on the same data. It inserts some sales, queries both views before and after refreshing the materialized view.

PostgreSQL

CREATE TABLE sales (
  sale_id SERIAL PRIMARY KEY,
  customer_id INT,
  amount NUMERIC,
  sale_date DATE
);

INSERT INTO sales (customer_id, amount, sale_date) VALUES
(1, 100, '2024-06-01'),
(2, 150, '2024-06-02'),
(1, 200, '2024-06-03');

CREATE VIEW sales_view AS
SELECT customer_id, SUM(amount) AS total
FROM sales
GROUP BY customer_id;

CREATE MATERIALIZED VIEW sales_mat_view AS
SELECT customer_id, SUM(amount) AS total
FROM sales
GROUP BY customer_id
WITH NO DATA;

-- Query regular view
SELECT * FROM sales_view ORDER BY customer_id;

-- Query materialized view before refresh
SELECT * FROM sales_mat_view ORDER BY customer_id;

-- Refresh materialized view
REFRESH MATERIALIZED VIEW sales_mat_view;

-- Query materialized view after refresh
SELECT * FROM sales_mat_view ORDER BY customer_id;

Important Notes

Materialized views use storage space because they save data.

Regular views always show current data but can be slower for complex queries.

You must manually refresh materialized views to update their data.

Summary

Regular views run the query every time you use them.

Materialized views save query results and need refreshing to update.

Choose materialized views for speed with less frequent updates, regular views for always fresh data.