Vista materializada

Qué es

Una vista materializada es una consulta cuyo resultado conserva la
base de datos: leerla devuelve filas calculadas anteriormente, en
lugar de ejecutar de nuevo la consulta. En inglés británico se
escribe materialised view. Se sitúa entre otros dos objetos más
antiguos. Una vista normal solo guarda su consulta, que se ejecuta
sobre las tablas subyacentes en cada lectura. Una tabla creada con
`CREATE TABLE AS` guarda un resultado, pero olvida la consulta que
lo produjo. Una vista materializada conserva ambos: las filas que se
leen y la consulta, que puede ejecutarse de nuevo para sustituirlas.

PostgreSQL incorporó las vistas materializadas en la versión 9.3,
publicada el 9 de septiembre de 2013; las notas de la versión
atribuyen el trabajo a Kevin Grittner. La documentación explica que
utilizan el sistema de reglas como las vistas normales, pero
conservan el resultado con una estructura similar a una tabla. Para
el analizador de consultas, una vista materializada es una relación
como cualquier tabla o vista: un `SELECT` lee directamente las filas
almacenadas; la consulta guardada solo importa al rellenar la vista.
La vista no puede actualizarse directamente. Para sustituir su
contenido por el resultado actualizado de una consulta, hay que
volver a ejecutar la consulta guardada.

Dos órdenes realizan ese trabajo. `CREATE MATERIALIZED VIEW` define
la vista y, salvo que se indique `WITH NO DATA`, ejecuta la consulta
de inmediato para rellenarla. `REFRESH MATERIALIZED VIEW` vuelve a
ejecutar la consulta guardada y sustituye todo el contenido por el
nuevo resultado. La versión 9.4, publicada el

18 de diciembre de 2014, añadió `CONCURRENTLY`, que permite
actualizar sin bloquear las sesiones que leen la vista. Ambas
órdenes son extensiones de PostgreSQL y no forman parte del estándar
SQL; otras bases de datos ofrecen la idea con su propia sintaxis y
sus reglas.

Por qué importa

Algunas consultas son costosas y se repiten mucho: un panel que suma
por día un año de pedidos, un informe que une seis tablas o una
página que clasifica productos por ventas. Sobre las tablas de
origen, cada solicitud vuelve a pagar la exploración, la unión y la
agregación completas, aunque la respuesta apenas cambie entre
solicitudes. Una vista materializada paga ese coste una vez por
actualización; cada lectura posterior explora una tabla pequeña
calculada de antemano. La documentación de PostgreSQL indica que
leer los datos almacenados es «a menudo mucho más rápido que acceder
a las tablas subyacentes directamente o mediante una vista».

El precio es la actualidad de los datos. Entre actualizaciones, la
vista muestra cómo estaban en la última; en PostgreSQL, nada la
actualiza si no lo solicita alguien o una tarea programada. Muchos
usos pueden aceptarlo. Las ventas de ayer no cambian hoy y un
informe mensual no necesita los pedidos de este minuto. La vista
materializada obliga a decidir explícitamente qué antigüedad puede
tener la respuesta y quién debe impedir que la supere.

También proporciona un lugar para los índices: el resultado
almacenado puede tener índices propios, diseñados para las consultas
que lo leen. La documentación de PostgreSQL muestra otro uso: los
datos traídos de otro sistema mediante un adaptador de datos
externos, que quizá no admita índices, pueden copiarse a una vista
materializada que sí los admita.

Para un equipo que mantiene normalizado su esquema, este suele ser
el lugar adecuado para la estructura aplanada. La normalización
almacena cada hecho una vez y responde a la mayoría de las preguntas
mediante uniones. Una vista materializada puede conservar el
resultado de las uniones y las sumas que necesitan los informes,
mientras las tablas de origen mantienen una sola copia de cada hecho
y todas las escrituras siguen yendo a un único lugar.

Cómo se aplica en la práctica

Pensemos en las tablas de pedidos de una tienda: `orders`, con una
fila por pedido y la fecha en que se hizo, y `order_lines`, con el
SKU, la cantidad y el precio unitario de cada línea. Un panel de
ventas necesita unidades e ingresos por producto y día. La consulta
es breve, pero con varios años de pedidos lee todas las líneas:

```sql
CREATE MATERIALIZED VIEW daily_sales AS
SELECT o.ordered_on                   AS day,
       l.sku,
       sum(l.quantity)                AS units,
       sum(l.quantity * l.unit_price) AS revenue
FROM   orders o
JOIN   order_lines l USING (order_id)
WHERE  o.ordered_on < CURRENT_DATE
GROUP  BY o.ordered_on, l.sku;
```

La cláusula `WHERE` excluye el día actual, cuyas cifras aún cambian:
el panel solo muestra días completos. El ejemplo de la documentación
de PostgreSQL trata del mismo modo un día de ventas aún sin
terminar. La orden ejecuta la consulta una vez y guarda el
resultado. Después se crea un índice:

```sql
CREATE UNIQUE INDEX daily_sales_sku_day ON daily_sales (sku, day);
```

El índice cumple dos funciones. Permite al panel encontrar los días
de un producto sin leer toda la vista y hace posible una
actualización concurrente posterior. La consulta agrupa por día y
SKU: cada pareja aparece una vez y el índice puede ser único. A
partir de aquí, el panel lee la vista como una tabla:

```sql
SELECT day, units, revenue
FROM   daily_sales
WHERE  sku = 'TEA-250'
ORDER  BY day;
```

Cada noche, poco después de medianoche, una tarea programada la pone
al día:

```sql
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;
```

Sin `CONCURRENTLY`, una actualización que afecta a muchas filas
tiende a consumir menos recursos y terminar antes, pero podría
bloquear las lecturas del panel hasta acabar. Con esa opción, las
lecturas continúan durante la actualización; la documentación señala
que incluso puede ser más rápida si cambian pocas filas, como aquí,
donde cada noche se añade un día. La opción exige dos condiciones.
La vista debe contener ya datos y tener al menos un índice único
sobre nombres de columnas simples que abarque todas las filas: no
sirve un índice de expresiones ni uno con cláusula `WHERE`. El
índice anterior satisface la segunda; crear la vista con sus datos
satisfizo la primera.

Otras bases de datos fijan límites distintos. En BigQuery, las
vistas materializadas se mantienen en segundo plano: los cambios de
las tablas de base se añaden de forma incremental sin solicitarlos,
una consulta a la vista incorpora los cambios recientes para
mantener actualizado el resultado y la optimización inteligente
puede redirigir hacia la vista una consulta escrita sobre las tablas
de base. Esto se aplica a las vistas incrementales, que admiten un
subconjunto limitado del lenguaje SQL. Una vista definida con
`allow_non_incremental_definition = true` admite la mayoría de las
consultas, pero cada actualización ejecuta la consulta completa y
sus resultados no están siempre actualizados.

SQL Server logra un resultado similar con vistas indexadas. Añadir
un índice agrupado que imponga unicidad a una vista, que debe
crearse con `WITH SCHEMABINDING` y tener una definición
determinista, almacena su resultado con la misma organización que
una tabla indexada; el optimizador puede usarlo incluso en una
consulta que no nombre la vista. SQL Server actualiza la vista
indexada con cada inserción, actualización y borrado en sus tablas
de origen: no hay actualización periódica que programar. El coste se
traslada a las escrituras y la documentación de Microsoft advierte
de que su rendimiento puede degradarse de forma considerable.

La misma contrapartida aparece en el trabajo de producción. En un
puesto anterior, la fundadora construyó flujos de procesamiento ETL
y ELT en PL/pgSQL que utilizaban tablas particionadas y vistas
materializadas para acelerar las lecturas en grandes conjuntos de
datos, junto con restricciones de claves primarias y externas para
la integridad referencial e índices únicos y compuestos para las
uniones.

Dónde surgen los problemas

Cifras antiguas que parecen actuales. Una tarea de actualización
detenida hace una semana deja que la vista responda a todas las
consultas deprisa y de forma incorrecta, sin que el resultado
indique su antigüedad. Haga que la tarea registre la hora de cada
actualización correcta donde el panel pueda mostrarla y emita una
alerta cuando el tiempo transcurrido supere el máximo aceptado por
los lectores.

Una actualización bloquea a los lectores. Un `REFRESH MATERIALIZED
VIEW` normal sobre una vista grande podría bloquear todas las
sesiones que intenten leerla hasta terminar. Añada un índice único y
use `CONCURRENTLY`, o programe las actualizaciones normales en horas
sin lectores. Si cambia la mayoría de las filas en cada ejecución,
la actualización normal puede seguir siendo la más barata.

Se rechaza `CONCURRENTLY`. La opción necesita un índice único sobre
columnas simples que abarque todas las filas. No sirve un índice
único sobre `lower(email)` ni uno limitado por una cláusula `WHERE`.
También necesita una vista con datos: falla si se creó con `WITH NO
DATA`, y ambas opciones no pueden combinarse. Añada un índice válido
y rellene la vista una vez con una actualización normal.

Una vista que no se puede consultar. `WITH NO DATA` crea la vista
sin ejecutar su consulta y la marca como no disponible para lectura;
cualquier consulta falla hasta la primera actualización. Puede ser
lo adecuado en una migración que no deba esperar una consulta larga,
pero entonces hay que planificar esa primera actualización y no
olvidarla.

Un cambio de definición que nunca llegó. `CREATE MATERIALIZED VIEW
IF NOT EXISTS` en una migración no hace nada más que emitir un aviso
si ya existe una vista con ese nombre; la documentación no garantiza
que la existente se parezca a la nueva definición. Cambiar la
consulta de esa forma deja la anterior intacta. Elimine la vista con
`DROP MATERIALIZED VIEW` y vuelva a crearla en la misma migración.

Tareas de actualización que se solapan. Una vista materializada solo
puede tener una actualización en curso, incluso con `CONCURRENTLY`;
programarla con más frecuencia de lo que tarda solo hace que la
vista se retrase más. Mida la duración y programe en función de
ella.

Se confía en el orden de las filas. Si la consulta que define la
vista incluye `ORDER BY`, el contenido inicial sale en ese orden,
pero una actualización no promete conservarlo. Incluya `ORDER BY` en
las consultas que lean la vista.

Funciones que pierden sus tablas. Al ejecutar la consulta que define
la vista, durante la creación o la actualización, PostgreSQL
establece temporalmente `search_path` en `pg_catalog, pg_temp` y
restringe lo que puede hacer: una función que cree tablas temporales
falla. Una función que busque sus tablas mediante nombres sin
esquema puede superar todas las pruebas manuales y fallar solo
durante la actualización. Califique con el esquema los nombres de
tabla de toda función invocada por la vista.

La tarea se ejecuta con el rol equivocado. Actualizar requiere el
privilegio `MAINTAIN` sobre la vista materializada. Una tarea
ejecutada con un rol de informes que puede leerla, pero no
mantenerla, falla cada noche. Conceda ese privilegio al rol que
ejecute la actualización.

Se guarda una copia cuando bastaría un índice. Si una consulta es
lenta porque falta un índice, una vista materializada oculta el
problema y añade datos antiguos. Lea primero el plan de consulta. Si
la consulta solo necesita un nombre, basta una vista normal, que
siempre está actualizada; reserve las materializadas para resultados
costosos de calcular y que puedan leerse ligeramente desactualizados
sin riesgo.

Lecturas adicionales

  * Vistas materializadas — el capítulo de PostgreSQL sobre su
relación con vistas y tablas, con un resumen de ventas actualizado
cada noche y un ejemplo de adaptador de datos externos.

  * CREATE MATERIALIZED VIEW — la referencia de la orden, incluidos
`WITH NO DATA`, `IF NOT EXISTS` y el `search_path` restringido
durante la consulta.

  * REFRESH MATERIALIZED VIEW — la orden de actualización, las
condiciones de `CONCURRENTLY` y el privilegio `MAINTAIN` requerido.

  * Notas de PostgreSQL 9.3 — la versión de septiembre de 2013 que
incorporó las vistas materializadas.

  * Introducción a las vistas materializadas — las vistas
incrementales y no incrementales de BigQuery, actualización
automática y optimización inteligente.

  * Crear vistas indexadas — las vistas indexadas de SQL Server, sus
requisitos y el coste para las escrituras.

hEsta página en la web	URL:https://engineer.company/es/glossary/materialized-view/	engineer.company	70
