Vistas materializadas en PostgreSQL

Si trabajas mucho con SQL (preferiblemente usas PostgreSQL), es probable que hayas escrito un buen número de consultas complejas para responder a preguntas clave. Y a veces necesitas reutilizar los resultados de esas consultas con bastante frecuencia. Ejecutar varias veces una consulta compleja puede ser costoso. ¿Y si pudieras guardar en caché los resultados para mejorar el rendimiento? Ahí es donde entran en juego las vistas materializadas.

Quizá pienses que las vistas simples también guardan el resultado de una consulta. Pero una vista simple y una vista materializada difieren en varios aspectos. Esto es lo que dice la documentación oficial de PostgreSQL sobre las vistas simples:

La vista no se materializa físicamente. En su lugar, la consulta se ejecuta cada vez que la vista se referencia en una consulta.

Es decir, las vistas simples no se almacenan físicamente en la base de datos. Cada vez que usas una vista en una consulta, se ejecuta la consulta que la define. Esto hace que las vistas simples sean algo menos eficientes en tiempo de acceso.

En cambio, las vistas materializadas te dan mucha flexibilidad porque te permiten persistir físicamente una vista en la base de datos. Y puedes operar con ellas igual que con las vistas simples (pero con menor tiempo de acceso). En el resto del tutorial, verás cómo funcionan las vistas materializadas en PostgreSQL. En uno de sus webinars, el Chief Data Scientist de DataCamp, David Robinson, mostró cómo usa vistas materializadas para almacenar de forma eficiente los resultados de consultas complejas.

Nota: para seguir este tutorial, te conviene estar familiarizado con los contenidos del curso Joining Data in SQL.

Configuración inicial de la base de datos

Lo ideal es preparar una base de datos en PostgreSQL y crear una tabla básica para poder consultarla con eficacia. Esto también te ayudará en las siguientes secciones del tutorial. Puedes restaurar esta copia de seguridad, que contiene la tabla que usarás aquí. Si quieres aprender a restaurar una copia de seguridad en PostgreSQL, sigue la primera sección de este tutorial.

Si has podido restaurar la copia, deberías ver una tabla llamada international_debt en la base de datos (si no tienes base de datos, crea una primero). Echemos un vistazo rápido a las primeras filas de la tabla (con una consulta select sencilla) :

La tabla contiene información sobre estadísticas de deuda de distintos países del mundo para el año en curso y en diferentes categorías (consulta las columnas indicator_name, indicator_code). La columna debt muestra el importe de la deuda (en USD) que tiene un país en una categoría concreta. Son datos del ámbito de la economía y se usan a menudo para analizar la situación económica de diferentes países. Los datos proceden del Banco Mundial.

Ahora que ya tienes la base de datos lista, puedes pasar a las siguientes secciones.

Creación de vistas materializadas

Vamos a crear una vista materializada a partir de esta consulta:

select country_name,
sum(debt) as total_debt
from international_debt
group by country_name order by country_name;
Con la tecnología de

La consulta devuelve los países con su deuda total (en orden alfabético). El resultado debería verse así:

Ahora, la vista materializada:

CREATE MATERIALIZED VIEW country_total_debt
as
  select country_name,
  sum(debt) as total_debt
  from international_debt
  group by country_name order by country_name;
Con la tecnología de

Deberías ver un mensaje de éxito como este: Query returned successfully: 124 rows affected, 242 ms execution time.

Actualizar una vista materializada

También puedes indicarle a PostgreSQL que no cargue datos en la vista materializada al crearla especificando la opción WITH NO DATA:

CREATE MATERIALIZED VIEW country_total_debt_2
as
  select country_name,
  sum(debt) as total_debt
  from international_debt
  group by country_name order by country_name
WITH NO DATA;
Con la tecnología de

Recibirás un mensaje como: Query returned successfully with no result in 41 ms. Fíjate en la diferencia entre ambos mensajes. Si añades la opción WITH NO DATA, se crea una vista materializada vacía. No puedes consultarla todavía. Sin embargo, puedes poblarla ejecutando:

REFRESH MATERIALIZED VIEW country_total_debt_2;

Consultar una vista materializada

A estas alturas deberías tener creadas dos vistas materializadas (country_total_debt, country_total_debt_2). Ejecuta una consulta select sencilla usando cualquiera de ellas:

select * from country_total_debt_2;
Con la tecnología de

La consulta debería devolver algo como esto:

La salida es exactamente la misma que la consulta que envolvimos en la vista materializada. También puedes ejecutar consultas con funciones de agregación sobre vistas materializadas:

select max(total_debt) from country_total_debt_2;
Con la tecnología de

Deberías obtener:

Eliminar una vista materializada

Las vistas materializadas se pueden eliminar igual que las tablas en PostgreSQL. Solo tienes que especificar Materialized View después de Drop:

Drop Materialized View country_total_debt_2;
Con la tecnología de

Si quieres comparar MySQL con su segundo competidor más popular, echa un vistazo a nuestra comparativa en profundidad PostgreSQL vs. MySQL.

Conclusión

Y hasta aquí el tutorial. Gracias por llegar hasta el final. Has aprendido qué son las vistas materializadas en PostgreSQL y cómo trabajar con ellas. Por su eficacia, se usan mucho en entornos de data warehousing. Si te apetece aprender consultas más complejas, echa un vistazo a estos cursos de DataCamp:

• Intermediate SQL
• Reporting in SQL