Search⌘ K
AI Features
Home
Courses
The Art of PostgreSQL

Materialized Views

Explore the use of materialized views in PostgreSQL to cache database query results for improved performance. Understand how to create materialized views, handle cache invalidation with refresh commands, and apply caching strategies based on update frequency to optimize data retrieval.

We'll cover the following...

Implementing materialized view

It’s easy enough to cache a snapshot of the database into a permanent relation for later querying thanks to PostgreSQL implementation of the materialized view:

PostgreSQL
create schema if not exists twcache;
create materialized view twcache.message
    as select messageid, userid, datetime, message,
              rts, favs,
              location, lang, url
         from tweet.message_with_counters;
create unique index on twcache.message(messageid);

As usual, read the PostgreSQL documentation about the command CREATE MATERIALIZED VIEW for complete details about the command and its options.

The application code can now query twcache.message instead of tw.message and get the extra pre-computed columns for the rts and favs counter. The information in the materialized view is static; it’s only updated with a specific command. We have effectively implemented a cache in SQL, and now we have to solve the cache ...