Materialized views concept in PostgreSQL - Mini Project: Build & Apply

Creating and Using Materialized Views in PostgreSQL

📖 Scenario: You work in a retail company database. The sales data is large and queries to calculate total sales per product are slow. To speed up reports, you will create a materialized view that stores the total sales per product.

🎯 Goal: Build a materialized view in PostgreSQL that pre-calculates total sales per product from the sales table. This will help speed up queries that show product sales summaries.

📋 What You'll Learn

Create a sales table with columns product_id (integer) and amount (numeric).

Insert sample sales data into the sales table.

Create a materialized view named product_sales_summary that sums amount grouped by product_id.

Query the materialized view to get total sales per product.

💡 Why This Matters

🌍 Real World

Materialized views are used in real-world databases to speed up complex queries by storing precomputed results, especially in reporting and analytics systems.

💼 Career

Database administrators and backend developers use materialized views to optimize performance and reduce load on live transactional tables.

1

Create the sales table and insert data

Create a table called sales with columns product_id as integer and amount as numeric. Then insert these exact rows into sales: (1, 100), (2, 150), (1, 200), (3, 50).

PostgreSQL

-- Create the sales table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Define the materialized view query

Write a SQL query that selects product_id and the sum of amount as total_sales from the sales table, grouped by product_id. Assign this query to create a materialized view named product_sales_summary.

PostgreSQL

CREATE TABLE sales (
    product_id INTEGER,
    amount NUMERIC
);

INSERT INTO sales (product_id, amount) VALUES
(1, 100),
(2, 150),
(1, 200),
(3, 50);

-- Create the materialized view product_sales_summary
-- Your code here

Need a hint?

Use CREATE MATERIALIZED VIEW view_name AS SELECT ... GROUP BY ... syntax.

3

Query the materialized view

Write a SQL query to select all columns from the materialized view product_sales_summary to see the total sales per product.

PostgreSQL

CREATE TABLE sales (
    product_id INTEGER,
    amount NUMERIC
);

INSERT INTO sales (product_id, amount) VALUES
(1, 100),
(2, 150),
(1, 200),
(3, 50);

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

-- Query the materialized view to get total sales per product
-- Your code here

Need a hint?

Use SELECT * FROM product_sales_summary; to see the data.

4

Refresh the materialized view after new data

Insert a new row into sales with product_id 2 and amount 100. Then write the SQL command to refresh the materialized view product_sales_summary so it updates with the new data.

PostgreSQL

CREATE TABLE sales (
    product_id INTEGER,
    amount NUMERIC
);

INSERT INTO sales (product_id, amount) VALUES
(1, 100),
(2, 150),
(1, 200),
(3, 50);

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

SELECT * FROM product_sales_summary;

-- Insert new sales data and refresh the materialized view
-- Your code here

Need a hint?

Use INSERT INTO to add data and REFRESH MATERIALIZED VIEW view_name; to update the view.