Search⌘ K
Home
Courses
The Art of PostgreSQL

Materialized Views

Explore how to implement materialized views in PostgreSQL to cache and quickly retrieve computed data such as points per season. Understand creating, refreshing, and using materialized views to optimize application performance and reduce query time on frequently accessed datasets.

We'll cover the following...

Use case: Computing points per season

Let’s now compute constructor and driver points per season. In the following query, we compute points for the ongoing season and the dataset available:

PostgreSQL
\set season 2017
  select drivers.surname as driver,
         constructors.name as constructor,
         sum(points) as points
    
    from results
         join races using(raceid)
         join drivers using(driverid)
         join constructors using(constructorid)
   
   where races.year = :season
group by grouping sets(drivers.surname, constructors.name)
  having sum(points) > 150
order by drivers.surname is not null, points desc;

Here’s the result, which we know is wrong because the season was not over yet at the time of the computation. The having clause has been used only to reduce the number of lines to display; in a real application, we would certainly get all the results at once. Here’s our result set:

   driver   | constructor | points 
------------+-------------+--------
            | Mercedes    |    668
            | Ferrari     |    522
            | Red Bull    |    368
            |
...