Materialized views

A materialized view in PostgreSQL uses the rule system, similar to a standard view. However, it also stores the query results in a table. For a materialized view such as:

    CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;

and view the following:

    CREATE TABLE mymatview AS SELECT * FROM mytab;

The main difference is that the materialized view cannot be directly updated. The query used to create the materialized view is stored in the same way as a standard view query. To refresh the data in the materialized view, run the following SQL statement:

    REFRESH MATERIALIZED VIEW mymatview;

In the PostgreSQL system catalogs, the information about a materialized view is identical to the information for a table or a view. Therefore, the parser treats a materialized view as a relation, just like a table or a view. When a query references a materialized view, data is returned directly from it, as if from a table. The rules are used only to populate the materialized view.

Accessing data stored in a materialized view is often much faster than accessing the underlying tables directly or through a view. However, the data is not always current. In some cases, up-to-date data is not required. For example, consider a table that records sales:

    CREATE TABLE invoice (
        invoice_no    integer        PRIMARY KEY,
        seller_no     integer,       -- ID of the salesperson
        invoice_date  date,          -- Date of the invoice
        invoice_amt   numeric(13,2)  -- Amount of the invoice
    );

To quickly chart historical sales data, you can summarize the data. You might not be concerned with incomplete data for the current day:

    CREATE MATERIALIZED VIEW sales_summary AS
      SELECT
          seller_no,
          invoice_date,
          sum(invoice_amt)::numeric(13,2) as sales_amt
        FROM invoice
        WHERE invoice_date < CURRENT_DATE
        GROUP BY
          seller_no,
          invoice_date
        ORDER BY
          seller_no,
          invoice_date;

    CREATE UNIQUE INDEX sales_summary_seller
      ON sales_summary (seller_no, invoice_date);

This materialized view is useful for displaying a chart on a dashboard for salespeople. You can use a scheduled job to update the statistics every night with the following SQL statement:

    REFRESH MATERIALIZED VIEW sales_summary;

Another use for materialized views is to enable faster access to data from a remote system through a foreign data wrapper (FDW). The following is a simple example that uses file_fdw. Because the local system can use a cache, the performance difference compared to accessing a remote system can be greater than what is shown here. Note that because file_fdw does not support indexes, an index is created on the materialized view instead. This advantage might not apply to other types of foreign data access.

Setup:

    CREATE EXTENSION file_fdw;
    CREATE SERVER local_file FOREIGN DATA WRAPPER file_fdw;
    CREATE FOREIGN TABLE words (word text NOT NULL)
      SERVER local_file
      OPTIONS (filename '/usr/share/dict/words');
    CREATE MATERIALIZED VIEW wrd AS SELECT * FROM words;
    CREATE UNIQUE INDEX wrd_word ON wrd (word);
    CREATE EXTENSION pg_trgm;
    CREATE INDEX wrd_trgm ON wrd USING gist (word gist_trgm_ops);
    VACUUM ANALYZE wrd;

Now, you can check the spelling of a word. First, use file_fdw directly:

    SELECT count(*) FROM words WHERE word = 'caterpiler';

     count
    -------
         0
    (1 row)

The output from EXPLAIN ANALYZE shows the following:

     Aggregate  (cost=21763.99..21764.00 rows=1 width=0) (actual time=188.180..188.181 rows=1 loops=1)
       ->  Foreign Scan on words  (cost=0.00..21761.41 rows=1032 width=0) (actual time=188.177..188.177 rows=0 loops=1)
             Filter: (word = 'caterpiler'::text)
             Rows Removed by Filter: 479829
             Foreign File: /usr/share/dict/words
             Foreign File Size: 4953699
     Planning time: 0.118 ms
     Execution time: 188.273 ms

When you use the materialized view, the query is much faster:

     Aggregate  (cost=4.44..4.45 rows=1 width=0) (actual time=0.042..0.042 rows=1 loops=1)
       ->  Index Only Scan using wrd_word on wrd  (cost=0.42..4.44 rows=1 width=0) (actual time=0.039..0.039 rows=0 loops=1)
             Index Cond: (word = 'caterpiler'::text)
             Heap Fetches: 0
     Planning time: 0.164 ms
     Execution time: 0.117 ms

In either case, the word is misspelled. To find potential corrections, you can use file_fdw and pg_trgm again:

    SELECT word FROM words ORDER BY word <-> 'caterpiler' LIMIT 10;

         word
    ---------------
     cater
     caterpillar
     Caterpillar
     caterpillars
     caterpillar's
     Caterpillar's
     caterer
     caterer's
     caters
     catered
    (10 rows)

     Limit  (cost=11583.61..11583.64 rows=10 width=32) (actual time=1431.591..1431.594 rows=10 loops=1)
       ->  Sort  (cost=11583.61..11804.76 rows=88459 width=32) (actual time=1431.589..1431.591 rows=10 loops=1)
             Sort Key: ((word <-> 'caterpiler'::text))
             Sort Method: top-N heapsort  Memory: 25kB
             ->  Foreign Scan on words  (cost=0.00..9672.05 rows=88459 width=32) (actual time=0.057..1286.455 rows=479829 loops=1)
                   Foreign File: /usr/share/dict/words
                   Foreign File Size: 4953699
     Planning time: 0.128 ms
     Execution time: 1431.679 ms

Using the materialized view:

     Limit  (cost=0.29..1.06 rows=10 width=10) (actual time=187.222..188.257 rows=10 loops=1)
       ->  Index Scan using wrd_trgm on wrd  (cost=0.29..37020.87 rows=479829 width=10) (actual time=187.219..188.252 rows=10 loops=1)
             Order By: (word <-> 'caterpiler'::text)
     Planning time: 0.196 ms
     Execution time: 198.640 ms

If your application can tolerate periodic updates of remote data to the local database, the performance gains can be substantial.