Indexing materialized views in PostgreSQL - Mini Project: Build & Apply

Indexing Materialized Views in PostgreSQL

📖 Scenario: You work as a database assistant for a bookstore. The store wants to speed up queries that summarize sales data by author. To do this, you will create a materialized view that stores total sales per author and then add an index to make searching faster.

🎯 Goal: Create a materialized view named author_sales_summary that shows total sales per author from the sales table. Then add an index on the author column of this materialized view to speed up queries.

📋 What You'll Learn

Create a materialized view called author_sales_summary with columns author and total_sales.

The materialized view should sum the amount column grouped by author from the sales table.

Create an index named idx_author_sales_summary_author on the author column of the materialized view.

Use standard PostgreSQL syntax.

💡 Why This Matters

🌍 Real World

Materialized views help speed up complex queries by storing precomputed results. Indexing them makes lookups even faster.

💼 Career

Database administrators and developers often use materialized views and indexes to optimize reporting and analytics in real-world applications.

1

Create the materialized view

Write a SQL statement to create a materialized view named author_sales_summary. It should select author and the sum of amount as total_sales from the sales table, grouped by author.

PostgreSQL

-- Your code here to create the materialized view author_sales_summary

Need a hint?

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

2

Add an index name variable

Create a variable called index_name and set it to the string 'idx_author_sales_summary_author'. This will hold the name of the index you will create.

PostgreSQL

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

-- Your code here to create the index_name variable

Need a hint?

Use \set index_name 'index_name_here' to define a psql variable.

3

Create the index on the materialized view

Write a SQL statement to create an index named idx_author_sales_summary_author on the author column of the author_sales_summary materialized view.

PostgreSQL

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

\set index_name 'idx_author_sales_summary_author'

-- Your code here to create the index on author column

Need a hint?

Use CREATE INDEX index_name ON view_name(column_name);

4

Refresh the materialized view

Write a SQL statement to refresh the author_sales_summary materialized view so it contains the latest data.

PostgreSQL

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

\set index_name 'idx_author_sales_summary_author'

CREATE INDEX idx_author_sales_summary_author ON author_sales_summary(author);

-- Your code here to refresh the materialized view

Need a hint?

Use REFRESH MATERIALIZED VIEW view_name; to update the data.