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

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

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

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

Hint

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

Practice

1. What is the main purpose of a materialized view in PostgreSQL?

easy

A. To store the result of a query physically for faster access

B. To create a temporary table that disappears after the session

C. To automatically update data in real-time without manual refresh

D. To define a view that does not store any data

Solution

1. Step 1: Understand what a materialized view does

   A materialized view stores the result of a query physically on disk, unlike a normal view which only stores the query definition.

2. Step 2: Compare options with this definition

   To store the result of a query physically for faster access matches this exactly. Options A, B, and C describe other concepts like temporary tables or normal views.

3. Final Answer:

   To store the result of a query physically for faster access -> Option A

4. Quick Check:

   Materialized view = stored query result [OK]

Hint: Materialized views store data physically, unlike normal views [OK]

Common Mistakes:

• Confusing materialized views with normal views
• Thinking materialized views auto-update without refresh
• Assuming materialized views are temporary

2. Which of the following is the correct syntax to create a materialized view named sales_summary from a table sales?

easy

A. CREATE MATERIALIZED TABLE sales_summary AS SELECT * FROM sales;

B. CREATE VIEW MATERIALIZED sales_summary AS SELECT * FROM sales;

C. CREATE MATERIALIZED VIEW sales_summary AS SELECT * FROM sales;

D. CREATE TABLE MATERIALIZED VIEW sales_summary AS SELECT * FROM sales;

Solution

1. Step 1: Recall the correct syntax for materialized views

   The correct syntax is: CREATE MATERIALIZED VIEW view_name AS SELECT ...

2. Step 2: Match options with correct syntax

   CREATE MATERIALIZED VIEW sales_summary AS SELECT * FROM sales; matches exactly. Options A, B, and D mix keywords incorrectly or use wrong object types.

3. Final Answer:

   CREATE MATERIALIZED VIEW sales_summary AS SELECT * FROM sales; -> Option C

4. Quick Check:

   CREATE MATERIALIZED VIEW = correct syntax [OK]

Hint: Remember: CREATE MATERIALIZED VIEW, not CREATE VIEW MATERIALIZED [OK]

Common Mistakes:

• Swapping keywords 'VIEW' and 'MATERIALIZED'
• Using CREATE MATERIALIZED TABLE instead of VIEW
• Adding TABLE keyword incorrectly

3. Given the materialized view mv_products created as CREATE MATERIALIZED VIEW mv_products AS SELECT id, price FROM products;, what will be the result of this query immediately after inserting a new product into products but before refreshing mv_products?

SELECT * FROM mv_products WHERE id = 101;

medium

A. The new product with id 101 will appear in the result

B. The materialized view will automatically refresh and show the new product

C. Query will cause a syntax error

D. No rows will be returned for id 101

Solution

1. Step 1: Understand materialized view data update behavior

   Materialized views do not update automatically when base tables change. They require manual refresh.

2. Step 2: Analyze query result after insert but before refresh

   The new product with id 101 is not yet in the materialized view, so the query returns no rows for id 101.

3. Final Answer:

   No rows will be returned for id 101 -> Option D

4. Quick Check:

   Materialized view data = stale until refresh [OK]

Hint: Materialized views need manual refresh to show new data [OK]

Common Mistakes:

• Assuming materialized views auto-update on insert
• Expecting syntax error from a valid SELECT
• Confusing materialized views with normal views

4. You have a materialized view mv_orders created as CREATE MATERIALIZED VIEW mv_orders AS SELECT * FROM orders;. You try to refresh it using REFRESH MATERIALIZED VIEW mv_orders; but get an error: ERROR: permission denied for relation orders. What is the most likely cause?

medium

A. The user running the refresh lacks SELECT permission on the base table orders

B. The materialized view name is misspelled

C. REFRESH MATERIALIZED VIEW syntax is incorrect

D. Materialized views cannot be refreshed after creation

Solution

1. Step 1: Understand permission requirements for refreshing

   Refreshing a materialized view requires SELECT permission on the underlying base tables.

2. Step 2: Analyze the error message

   The error says permission denied on relation orders, indicating the user lacks SELECT rights on that table.

3. Final Answer:

   The user running the refresh lacks SELECT permission on the base table orders -> Option A

4. Quick Check:

   Permission denied = missing SELECT rights [OK]

Hint: Check base table permissions if refresh gives permission denied [OK]

Common Mistakes:

• Assuming syntax error instead of permission issue
• Ignoring user privileges on base tables
• Thinking materialized views cannot be refreshed

5. You want to speed up a complex report query that aggregates sales data by month. You create a materialized view monthly_sales with the aggregation. However, the sales data updates daily. Which approach best keeps monthly_sales up to date with minimal performance impact?

hard

A. Manually drop and recreate the materialized view after each data change

B. Schedule a nightly REFRESH MATERIALIZED VIEW to update the data once per day

C. Use a normal view instead of a materialized view for real-time data

D. Set the materialized view to auto-refresh on every query

Solution

1. Step 1: Consider update frequency and performance

   Since sales data updates daily, refreshing the materialized view nightly balances freshness and performance.

2. Step 2: Evaluate other options

   Dropping and recreating is costly and manual. Normal views are slow for complex queries. Auto-refresh on every query is not supported and would hurt performance.

3. Final Answer:

   Schedule a nightly REFRESH MATERIALIZED VIEW to update the data once per day -> Option B

4. Quick Check:

   Nightly refresh = best balance for daily updates [OK]

Hint: Use scheduled refresh for daily updated materialized views [OK]

Common Mistakes:

• Trying to auto-refresh materialized views on every query
• Dropping and recreating instead of refreshing
• Using normal views for heavy aggregation queries