Introduction to PostgreSQL Materialized Views

Feb 8, 2024 by Robert Gravelle

PostgreSQL Materialized Views provide a powerful mechanism to enhance query performance by precomputing and storing the result set of a query as a physical table. This tutorial will guide you through the creation of Materialized Views in PostgreSQL, using the DVD Rental Database as a practical example.

A Materialized View is a snapshot of a query’s result set that is stored as a physical table. Unlike regular views, which are virtual and execute the underlying query every time they are referenced, Materialized Views persist the data, allowing for faster query performance at the cost of periodic refreshes.

Materialized Views are particularly useful in scenarios where the underlying data changes infrequently compared to the frequency of query executions. This makes them ideal for scenarios such as reporting, data warehousing, and situations where real-time data is not a strict requirement.

Before we dive into Materialized Views, let’s set up the DVD Rental Database. It’s PostgreSQL’s version of the popular Sakila Sample Database for MySQL. You can download the DVD Rental Database from the official PostgreSQL tutorial page (PostgreSQL Sample Database).

The database file is in ZIP format (dvdrental.zip) so you need to extract it to dvdrental.tar before loading the sample database into the PostgreSQL database server. Once you have extracted the .tar file, create a new database called “dvdrental” and execute the pg_restore command to populate the dvdrental database from the contents of the .tar file:

        pg_restore -U postgres -d dvdrental D:sampledbpostgresdvdrental.tar
    

Replace the path above with the one that points to the extracted dvdrental.tar on your system.

You can find the detailed installation instructions here.

Let’s say we want to create a Materialized View that shows the total revenue generated by each film category. Here’s a step-by-step guide:

• Connect to your PostgreSQL database
• Create the Materialized View using the following DML statement:

CREATE MATERIALIZED VIEW mv_category_revenue AS
SELECT
    c.name AS category,
    SUM(p.amount) AS total_revenue
FROM
    category c
    JOIN film_category fc ON c.category_id = fc.category_id
    JOIN film f ON fc.film_id = f.film_id
    JOIN inventory i ON f.film_id = i.film_id
    JOIN rental r ON i.inventory_id = r.inventory_id
    JOIN payment p ON r.rental_id = p.rental_id
GROUP BY
    c.name;

In this example, we join multiple tables from the DVD Rental Database to calculate the total revenue for each film category.

In Navicat For PostgreSQL (or Navicat Premium) 16:

• Click the “Materialized View” button to show the Materialized View Object List and then click on “+ New Materialized View” in the Objects toolbar to open the View Designer:

  

• Enter the SELECT portion of the above statement into the Definition editor:

  

• We can click the Preview button to verify that our statement works as expected:

  

• To create the new Materialized View, click the Save button. A dialog will appear prompting for the Materialized View Name. Let’s call it “mv_category_revenue” just as we did in the above CREATE MATERIALIZED VIEW statement above:

  

• Upon clicking the dialog Save button, Navicat will change the new materialized view name from “untitled” to the one we provided. It will also add our new materialized view to the Materialized Views in the left-hand Navigation Pane:

  

PostgreSQL Materialized Views are a valuable tool for optimizing query performance in scenarios where real-time data is not critical. By pre-computing and storing the results of complex queries, Materialized Views can significantly improve response times for analytical and reporting tasks. In this tutorial, we learned how to create a Materialized View for the DVD Rental Database, showcasing their practical application in a real-world scenario.