zone="rules-materializedviews">
 <primary>materialized view</primary>
 <secondary>implementation through rules</secondary>
</indexterm>

<indexterm zone="rules-materializedviews">
 <primary>view</primary>
 <secondary>materialized</secondary>
</indexterm>

<para>
    Materialized views in <productname>PostgreSQL</productname> use the
    rule system like views do, but persist the results in a table-like form.
    The main differences between:

<programlisting>
CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;
</programlisting>

    and:

<programlisting>
CREATE TABLE mymatview AS SELECT * FROM mytab;
</programlisting>

    are that the materialized view cannot subsequently be directly updated
    and that the query used to create the materialized view is stored in
    exactly the same way that a view's query is stored, so that fresh data
    can be generated for the materialized view with:

<programlisting>
REFRESH MATERIALIZED VIEW mymatview;
</programlisting>

    The information about a materialized view in the
    <productname>PostgreSQL</productname> system catalogs is exactly
    the same as it is for a table or view. So for the parser, a
    materialized view is a relation, just like a table or a view.  When
    a materialized view is referenced in a query, the data is returned
    directly from the materialized view, like from a table; the rule is
    only used for populating the materialized view.
</para>

<para>
    While access to the data stored in a materialized view is often much
    faster than accessing the underlying tables directly or through a view,
    the data is not always current; yet sometimes current data is not needed.
    Consider a table which records sales:

<programlisting>
CREATE TABLE invoice (
    invoice_no    integer        PRIMARY KEY,
    seller_no     integer,       -- ID of salesperson
    invoice_date  date,          -- date of sale
    invoice_amt   numeric(13,2)  -- amount of sale
);
</programlisting>

    If people want to be able to quickly graph historical sales data, they
    might want to summarize, and they may not care about the incomplete data
    for the current date:

<programlisting>
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 &lt; CURRENT_DATE
    GROUP BY
      seller_no,
      invoice_date;

CREATE UNIQUE INDEX sales_summary_seller
  ON sales_summary (seller_no, invoice_date);
</programlisting>

    This materialized view might be useful for displaying a graph in the
    dashboard created for salespeople.  A job could be scheduled to update
    the statistics each night using this SQL statement:

<programlisting>
REFRESH MATERIALIZED VIEW sales_summary;
</programlisting>
</para>

<para>
    Another use for a materialized view is to allow faster access to data
    brought across from a remote system through a foreign data wrapper.
    A simple example using <literal>file_fdw</literal> is below, with timings,
    but since this is using cache on the local system the performance
    difference compared to access to a remote system would usually be greater
    than shown here.  Notice we are also exploiting the ability to put an
    index on the materialized view, whereas <literal>file_fdw</literal> does
    not support indexes; this advantage might not apply for other sorts of
    foreign data access.
</para>

<para>
    Setup:

<programlisting>
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;
</programlisting>

    Now let's spell-check a word.  Using <literal>file_fdw</literal> directly: