Materialized views concept in PostgreSQL

Introduction

Materialized views store the result of a query physically. This helps to get data faster when you need the same results often.

When you want to speed up complex queries that take a long time to run.

When you need to show reports that use the same data repeatedly.

When your data does not change very often but you want quick access to results.

When you want to reduce the load on the main database by using stored query results.

Syntax

PostgreSQL

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

-- To refresh the data later:
REFRESH MATERIALIZED VIEW view_name;

The materialized view stores data physically unlike a normal view which runs the query every time.

You must refresh the materialized view to update its data when the original tables change.

Examples

This creates a materialized view that sums sales quantity by product.

PostgreSQL

CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;

This updates the materialized view with the latest data from the sales table.

PostgreSQL

REFRESH MATERIALIZED VIEW sales_summary;

This queries the materialized view to find products with more than 100 sold units.

PostgreSQL

SELECT * FROM sales_summary WHERE total_quantity > 100;

Sample Program

This example creates a sales table, inserts some data, creates a materialized view to sum quantities by product, and then selects from the view.

PostgreSQL

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  product_id INT,
  quantity INT
);

INSERT INTO sales (product_id, quantity) VALUES
(1, 10), (2, 5), (1, 15), (3, 7);

CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;

SELECT * FROM sales_summary ORDER BY product_id;

Important Notes

Materialized views need manual refresh; they do not update automatically.

Refreshing can be slow if the underlying data is large.

Use materialized views when query speed is more important than always having the latest data.

Summary

Materialized views store query results physically for faster access.

You must refresh them to update data after changes.

They are useful for speeding up repeated complex queries.