Caching Expensive Queries with MATERIALIZED VIEWs

by Christoph Schiessl on PostgreSQL

PostgreSQL 9.3 introduced a new feature called MATERIALIZED VIEWs, and this article explains how to use it based on a contrived example. Let's assume you are working on a small database to keep track of your customers and the invoices you send them. Currently, your database consists of three tables:

postgres=# CREATE TABLE customers (
postgres(#   id INT PRIMARY KEY,
postgres(#   legal_name TEXT NOT NULL
postgres(# );
CREATE TABLE
postgres=# CREATE TABLE invoices (
postgres(#   id INT PRIMARY KEY,
postgres(#   customer_id INT NOT NULL REFERENCES customers (id),
postgres(#   issued_on DATE NOT NULL
postgres(# );
CREATE TABLE
postgres=# CREATE TABLE invoice_items (
postgres(#   id INT PRIMARY KEY,
postgres(#   invoice_id INT NOT NULL REFERENCES invoices (id),
postgres(#   amount_in_cents INTEGER NOT NULL
postgres(# );
CREATE TABLE

As you can see, a customer has many invoices, which in turn have many invoice items. With that in place, you now want a convenient way to calculate your customers' total revenue. After thinking about the requirement for a second or two, you come up with the following SELECT statement:

postgres=# SELECT customers.*,
postgres-#        SUM(invoice_items.amount_in_cents) AS revenue
postgres-# FROM customers
postgres-#   INNER JOIN invoices ON customers.id = invoices.customer_id
postgres-#   INNER JOIN invoice_items ON invoices.id = invoice_items.invoice_id
postgres-# GROUP BY customers.id ORDER BY revenue DESC;
 id | legal_name | revenue
----+------------+---------
(0 rows)

While this query certainly works, it is error-prone to type repeatedly and may also be too slow for your needs. The easiest way to circumnavigate both of these problems is a MATERIALIZED VIEW.

postgres=# CREATE MATERIALIZED VIEW customers_with_revenue AS
postgres-# SELECT customers.*,
postgres-#        SUM(invoice_items.amount_in_cents) AS revenue
postgres-# FROM customers
postgres-#   INNER JOIN invoices ON customers.id = invoices.customer_id
postgres-#   INNER JOIN invoice_items ON invoices.id = invoice_items.invoice_id
postgres-# GROUP BY customers.id ORDER BY revenue DESC;
SELECT 0

Firstly, it's very convenient because it hides complex queries from you — just like an ordinary VIEW would. Secondly, it also improves performance because it caches the query's result.

How does it work? When you create the MATERIALIZED VIEW, it immediately executes its defining SELECT statement and persists its result set. Later, when you select something from the MATERIALIZED VIEW, it uses that pre-computed result set instead of computing the defining SELECT query again and again.

There's only one drawback: If the underlying data changes, you must manually refresh the MATERIALIZED VIEW. This can be accomplished as follows ...

postgres=# REFRESH MATERIALIZED VIEW customers_with_revenue;
REFRESH MATERIALIZED VIEW

If your data doesn't change frequently, you might consider using a TRIGGER to execute the REFRESH MATERIALIZED VIEW statement automatically.

Ready to Learn More Web Development?

Join my Mailing List to receive 1-2 useful Articles per week.


I send up to two weekly emails on building performant and resilient Web Apps with Python, JavaScript and PostgreSQL. No spam. Unscubscribe at any time.

Continue Reading?

Here are a few more Articles for you ...


Repairing Corrupted Indexes with REINDEXing

This article outlines how to rebuild indexes with REINDEX. As an example, we will deliberately corrupt an index for a column that uses a custom ENUM column.

By Christoph Schiessl on PostgreSQL

Custom ENUM Type Columns and ORDER BY

Learn how custom ENUM types in PostgreSQL can affect SELECT queries and ORDER BY clauses. Be mindful of indexing and typecasting.

By Christoph Schiessl on PostgreSQL

Force Index Usage by Manipulating the Query Planner

Learn how to manipulate PostgreSQL's query planner to force it to use your indexes while working on optimizing the performance of your queries.

By Christoph Schiessl on PostgreSQL

Christoph Schiessl

Christoph Schiessl

Independent Consultant + Full Stack Developer


If you hire me, you can rely on more than a decade of experience, which I have collected working on web applications for many clients across multiple industries. My involvement usually focuses on hands-on development work using various technologies like Python, JavaScript, PostgreSQL, or whichever technology we determine to be the best tool for the job. Furthermore, you can also depend on me in an advisory capacity to make educated technological choices for your backend and frontend teams. Lastly, I can help you transition to or improve your agile development processes.