Materialized view vs regular view decision in PostgreSQL - Hands-On Comparison

Materialized View vs Regular View Decision in PostgreSQL

📖 Scenario: You work as a data analyst for a retail company. You need to create a database object that shows the total sales per product category. The sales data is large and updated daily. You want to decide whether to use a regular view or a materialized view to get this summary efficiently.

🎯 Goal: Build a PostgreSQL materialized view and a regular view for total sales per product category. Learn how to create both and understand when to use each.

📋 What You'll Learn

Create a table called sales with columns product_category (text) and amount (numeric).

Create a regular view called category_sales_view that sums sales amounts by product category.

Create a materialized view called category_sales_matview that sums sales amounts by product category.

Add a command to refresh the materialized view.

💡 Why This Matters

🌍 Real World

Retail companies often need fast reports on sales data. Materialized views help speed up these reports by storing pre-calculated results.

💼 Career

Database administrators and data analysts use views and materialized views to optimize query performance and manage data freshness.

1

Create the sales table

Create a table called sales with columns product_category of type text and amount of type numeric.

PostgreSQL

-- Your code here to create the sales table

Need a hint?

Use CREATE TABLE with two columns: product_category as text and amount as numeric.

2

Create a regular view for total sales

Create a regular view called category_sales_view that shows the sum of amount grouped by product_category from the sales table.

PostgreSQL

CREATE TABLE sales (
    product_category text,
    amount numeric
);
-- Your code here to create the regular view

Need a hint?

Use CREATE VIEW with a SELECT that groups by product_category and sums amount.

3

Create a materialized view for total sales

Create a materialized view called category_sales_matview that shows the sum of amount grouped by product_category from the sales table.

PostgreSQL

CREATE TABLE sales (
    product_category text,
    amount numeric
);

CREATE VIEW category_sales_view AS
SELECT product_category, SUM(amount) AS total_amount
FROM sales
GROUP BY product_category;
-- Your code here to create the materialized view

Need a hint?

Use CREATE MATERIALIZED VIEW with the same query as the regular view.

4

Refresh the materialized view

Add a command to refresh the materialized view called category_sales_matview.

PostgreSQL

CREATE TABLE sales (
    product_category text,
    amount numeric
);

CREATE VIEW category_sales_view AS
SELECT product_category, SUM(amount) AS total_amount
FROM sales
GROUP BY product_category;

CREATE MATERIALIZED VIEW category_sales_matview AS
SELECT product_category, SUM(amount) AS total_amount
FROM sales
GROUP BY product_category;
-- Your code here to refresh the materialized view

Need a hint?

Use REFRESH MATERIALIZED VIEW followed by the materialized view name.