Las vistas materializadas en PostgreSQL utilizan el sistema de reglas al igual que las vistas, pero persisten los resultados en una forma similar a una tabla. Las principales diferencias entre:

CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;

y:

CREATE TABLE mymatview AS SELECT * FROM mytab;

son que la vista materializada no se puede actualizar directamente de forma posterior y que la consulta utilizada para crear la vista materializada se almacena exactamente de la misma manera que se almacena la consulta de una vista, de modo que se pueden generar datos frescos para la vista materializada con:

REFRESH MATERIALIZED VIEW mymatview;

La información sobre una vista materializada en los catálogos del sistema de PostgreSQL es exactamente la misma que para una tabla o vista. Por lo tanto, para el analizador (parser), una vista materializada es una relación, al igual que una tabla o una vista. Cuando se hace referencia a una vista materializada en una consulta, los datos se devuelven directamente desde la vista materializada, como si fuera una tabla; la regla solo se utiliza para rellenar la vista materializada.

Aunque el acceso a los datos almacenados en una vista materializada suele ser mucho más rápido que acceder a las tablas subyacentes directamente o a través de una vista, los datos no siempre están actualizados; sin embargo, a veces no se necesitan datos actualizados. Considera una tabla que registra las ventas:

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
);

Si la gente quiere poder graficar rápidamente datos históricos de ventas, podría querer resumirlos y no importarle los datos incompletos para la fecha actual:

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;

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

Esta vista materializada podría ser útil para mostrar un gráfico en el tablero creado para los vendedores. Se podría programar una tarea para actualizar las estadísticas cada noche utilizando esta sentencia SQL:

REFRESH MATERIALIZED VIEW sales_summary;

Otro uso de una vista materializada es permitir un acceso más rápido a los datos traídos desde un sistema remoto a través de un adaptador de datos externos (foreign data wrapper). A continuación se muestra un ejemplo sencillo utilizando file_fdw, con tiempos, pero dado que se está utilizando la caché en el sistema local, la diferencia de rendimiento en comparación con el acceso a un sistema remoto normalmente sería mayor de la que se muestra aquí. Observa que también estamos aprovechando la capacidad de poner un índice en la vista materializada, mientras que file_fdw no admite índices; esta ventaja podría no aplicarse a otros tipos de acceso a datos externos.

Configuración:

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;

Ahora comprobemos la ortografía de una palabra. Utilizando file_fdw directamente:

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

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

Con EXPLAIN ANALYZE, vemos:

 Aggregate  (cost=21763.99..21764.00 rows=1 width=0) (actual time=188.180..188.181 rows=1.00 loops=1)
   ->  Foreign Scan on words  (cost=0.00..21761.41 rows=1032 width=0) (actual time=188.177..188.177 rows=0.00 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

Si se utiliza la vista materializada en su lugar, la consulta es mucho más rápida:

 Aggregate  (cost=4.44..4.45 rows=1 width=0) (actual time=0.042..0.042 rows=1.00 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.00 loops=1)
         Index Cond: (word = 'caterpiler'::text)
         Heap Fetches: 0
         Index Searches: 1
 Planning time: 0.164 ms
 Execution time: 0.117 ms

De cualquier manera, la palabra está mal deletreada, así que busquemos lo que podríamos haber querido. De nuevo usando file_fdw y pg_trgm:

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.00 loops=1)
   ->  Sort  (cost=11583.61..11804.76 rows=88459 width=32) (actual time=1431.589..1431.591 rows=10.00 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.00 loops=1)
               Foreign File: /usr/share/dict/words
               Foreign File Size: 4953699
 Planning time: 0.128 ms
 Execution time: 1431.679 ms

Usando la vista materializada:

 Limit  (cost=0.29..1.06 rows=10 width=10) (actual time=187.222..188.257 rows=10.00 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.00 loops=1)
         Order By: (word <-> 'caterpiler'::text)
         Index Searches: 1
 Planning time: 0.196 ms
 Execution time: 198.640 ms

Si puedes tolerar la actualización periódica de los datos remotos en la base de datos local, el beneficio de rendimiento puede ser sustancial.