CREATE MATERIALIZED VIEW in PostgreSQL

Introduction

A materialized view saves the result of a query so you can quickly get the data without running the query again.

When you want to speed up slow queries by storing their results.

When you need to reuse complex calculations multiple times.

When your data changes less often but you want fast access.

When you want to reduce load on the main database during busy times.

Syntax

PostgreSQL

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

Materialized views store data physically, unlike regular views which run the query each time.

You need to refresh the materialized view to update its data after changes in the original tables.

Examples

This creates a materialized view showing orders from the last 7 days.

PostgreSQL

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

This materialized view stores the top 10 customers by total spending.

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
LIMIT 10;

Sample Program

This creates a materialized view of users who logged in during the last 30 days and then selects all rows from it.

PostgreSQL

CREATE MATERIALIZED VIEW active_users AS
SELECT user_id, last_login
FROM users
WHERE last_login > CURRENT_DATE - INTERVAL '30 days';

-- To see the data:
SELECT * FROM active_users;

Important Notes

Remember to run REFRESH MATERIALIZED VIEW view_name; to update the data when the original tables change.

Materialized views use storage space because they save data physically.

They improve read speed but add maintenance overhead.

Summary

Materialized views store query results physically for faster access.

You must refresh them to keep data current.

They are useful for speeding up complex or slow queries.