The Art of PostgreSQL/

...

/

Materialized Views

Materialized Views

Learn to implement and use materialized views in PostgreSQL.

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:

Press + to interact
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 ...