Cómo usar vistas materializadas en PostgreSQL

Introducción: Acerca de las vistas y las vistas materializadas

Para una consulta SQL compleja, no es práctico volver a escribir la consulta completa cada vez que se necesitan sus resultados. Puntos de vista resuelve este problema. Una vista es una consulta con nombre (predefinida) y una pseudotabla con el resultado de esa consulta. El código para hacer una vista basada en una consulta se ve así:

                      
                        -- pseudocode

CREATE VIEW my_view AS

    SELECT ...

        FROM ... JOIN ... ON ...

        WHERE ... AND ... 

        ORDER BY ...

        LIMIT ...

                      
                    

Esencialmente, la consulta se antepone con CREATE VIEW my_view AS . Esto crea una nueva vista, my_view ; consultarlo como si fuera una tabla normal:

                      
                        -- pseudocode

SELECT ...  

    FROM my_view 

    WHERE ...

                      
                    

Por lo tanto, puede acceder a los resultados de la consulta usando solo la vista sin tener que escribir toda la consulta. Una vista es parcialmente análoga a una “función” en un lenguaje de programación: un nombre corto para una consulta compleja. Ayuda con la experiencia del usuario y la legibilidad del código.

Pero bajo la superficie, cada vez que se accede a la vista, la base de datos convierte la vista en la consulta completa y la (re)evalúa antes de presentar el resultado. Volver a calcular una consulta compleja cada vez es ineficiente y no genera mejoras en el rendimiento de la base de datos. Por el contrario, la ejecución repetida de consultas complejas en tablas grandes degrada el rendimiento.

Me gustan las vistas, vistas materializadas ofrecer una interfaz consistente a la base de datos. Las vistas materializadas abstraen el diseño de la base de datos y los detalles de implementación para presentar una interfaz de consulta consistente a la capa API. Las vistas materializadas almacenan en caché los resultados de la consulta en una estructura persistente para que se pueda acceder a ellos sin tener que volver a calcularlos. Esto ahorra tiempo en consultas complejas a las que se accede repetidamente.

Las vistas materializadas resuelven este problema al almacenar (almacenar en caché) el resultado de una consulta con nombre (predefinida) en una estructura de datos persistente similar a una tabla. Tu corres SELECT consultas y crear índices sobre ellas como si fueran una tabla normal. También es posible construir vistas materializadas basadas en consultas sobre otras vistas materializadas.

Las vistas materializadas están disponibles en PostgreSQL, Oracle Database, SQL Server y algunos otros motores de bases de datos. Esta función no está disponible en MySQL.

requisitos previos

Es necesaria una familiaridad básica con la base de datos PostgreSQL para beneficiarse de esta guía. Se supone que tiene alguna experiencia con los conceptos básicos de PostgreSQL: instalación del software, creación de una nueva base de datos, creación de tablas, consultas estándar, etc. Para los ejemplos de SQL, se supone que ya tiene una instancia de PostgreSQL en ejecución configurada en Ubuntu , FreeBSD, CentOS o sus sucesores, o que está utilizando un servicio de base de datos administrado.

Los ejemplos de SQL de esta guía se prueban en PostgreSQL 14.5 ejecutándose en FreeBSD 13.1-RELEASE. Deben ser compatibles con todas las versiones recientes de PostgreSQL que se ejecutan en todos los sistemas operativos recientes.

Configurar tablas de prueba

Antes de crear una vista materializada, configure dos tablas de prueba y complételas con datos.

crear una tabla product :

                      
                        CREATE TABLE IF NOT EXISTS product (

    product_id INTEGER PRIMARY KEY, 

    name VARCHAR(20) NOT NULL, 

    price SMALLINT NOT NULL

);

                      
                    

crear una tabla orders :

                      
                        CREATE TABLE IF NOT EXISTS orders (

    order_id INTEGER, 

    product_id INTEGER REFERENCES product (product_id),

    PRIMARY KEY (order_id, product_id)

);

                      
                    

Consulta las descripciones de las tablas creadas:

                      
                        d orders

                      
                    

Inserte algunas filas con datos ficticios en el product mesa:

                      
                         INSERT INTO product (product_id, name, price) VALUES (1, 'Floppy Disk Drive', 40);

 INSERT INTO product (product_id, name, price) VALUES (2, 'Oculus Quest', 400);

                      
                    

Insertar datos ficticios en el orders mesa:

                      
                        INSERT INTO orders (order_id, product_id) VALUES (1, 1);

INSERT INTO orders (order_id, product_id) VALUES (1, 2);

INSERT INTO orders (order_id, product_id) VALUES (2, 1);

                      
                    

Revisa los datos en las tablas:

                      
                        SELECT * FROM orders;



SELECT * FROM product;

                      
                    

Crear una vista materializada

Cree una vista materializada en una consulta de combinación:

                      
                        CREATE MATERIALIZED VIEW mv_products_orders

AS

SELECT 

    p.product_id, 

    o.order_id, 

    p.name, 

    p.price 

FROM 

    product p 

JOIN 

    orders o 

ON 

    p.product_id = o.product_id;

                      
                    

Esto crea una vista materializada, mv_products_orders y lo rellena en función de los datos de las tablas subyacentes en el momento de la creación de la vista materializada. De forma predeterminada, los nombres de las columnas de la vista materializada derivan de los nombres de las columnas de las tablas subyacentes.

Compruebe la definición de la vista materializada recién creada:

                      
                        d mv_products_orders

                      
                    

Consulta los datos en mv_products_orders :

                      
                        SELECT * FROM mv_products_orders;

                      
                    

Para cambiar el nombre de una vista materializada, use el ALTER dominio:

                      
                        ALTER MATERIALIZED VIEW mv_products_orders RENAME TO my_mv;

                      
                    

Crear un índice en la vista materializada

Los índices en las vistas materializadas tienen los mismos beneficios que en las tablas normales: ayudan en las búsquedas rápidas. En particular, una forma común de actualizar (discutida en una sección posterior) las vistas materializadas requiere el uso de índices. Puede definir un índice en cualquier columna de una vista materializada.

                      
                        CREATE UNIQUE INDEX product_order ON mv_products_orders (order_id, product_id);

                      
                    

Compruebe que la descripción de la vista materializada ahora incluye el índice:

                      
                        d mv_products_orders

                      
                    

Vistas (materializadas) en vistas (materializadas)

Es posible construir vistas materializadas basadas en otras vistas materializadas.

                      
                        CREATE MATERIALIZED VIEW my_mv

AS

SELECT 

    * from mv_products_orders

    limit 2;

                      
                    

Del mismo modo, también es posible crear vistas basadas en vistas materializadas y vistas materializadas basadas en vistas.

Refrescando (Actualizando) Vistas Materializadas

PostgreSQL no actualiza automáticamente las vistas materializadas. Esto significa que, de forma predeterminada, los datos de la vista materializada quedan obsoletos cuando se actualizan las tablas subyacentes. Debe actualizar la vista materializada manualmente o configurar un sistema para hacerlo automáticamente.

Agregar una nueva fila a la orders mesa:

                      
                        INSERT INTO orders (order_id, product_id) VALUES (2, 2);

                      
                    

Vuelva a comprobar la vista materializada:

                      
                        SELECT * FROM mv_products_orders;

                      
                    

La salida sigue siendo la misma que antes.

Actualización manual

los REFRESH El comando se utiliza para actualizar el contenido de una vista materializada:

                      
                        REFRESH MATERIALIZED VIEW mv_products_orders;

                      
                    

Compruebe que la vista materializada ahora incluye los datos recién agregados a la orders mesa:

                      
                        SELECT * FROM mv_products_orders;

                      
                    

Hacer una actualización descarta los contenidos antiguos y recrea la vista materializada. Tenga en cuenta que no es posible consultar la vista materializada mientras se actualiza de esta manera. La operación de actualización coloca un bloqueo en la vista materializada y bloquea incluso SELECT consultas al respecto. Este bloqueo se mantiene hasta el final de la transacción (actualización).

los CONCURRENTLY Opción

Refrescante con el CONCURRENTLY opción resuelve este problema.

                      
                        REFRESH MATERIALIZED VIEW CONCURRENTLY mv_products_orders;

                      
                    

Con el CONCURRENTLY opción, la base de datos no bloquea SELECT consultas en la vista materializada mientras se actualiza. Cuando se especifica esta opción, internamente crea una estructura de datos temporal con los nuevos resultados de la consulta de la vista materializada. Los resultados antiguos y nuevos se comparan y los cambios se aplican a la vista materializada original utilizando UPDATE y INSERT operaciones.

Tenga en cuenta que para actualizar simultáneamente, la vista materializada debe contener al menos un índice único basado en columnas. Cuando intenta usar esto en una vista materializada que no tiene un índice único, arroja un error:

                      
                        ERROR:  cannot refresh materialized view "public.mv_products_orders" concurrently

HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view. 

                      
                    

Tenga en cuenta también que es posible ejecutar solo una operación de actualización en una vista materializada a la vez (incluso con el CONCURRENTLY opción).

Compensación

Si una actualización (refresco) implica una gran cantidad de datos nuevos, la velocidad de actualización es más rápida cuando el CONCURRENTLY la opción es no usó. Esto se debe a todas las comparaciones y operaciones de actualización involucradas en una actualización simultánea.

Consejo práctico: En general, es útil vaciar periódicamente una base de datos para limpiar las estructuras de datos no utilizadas y liberar espacio. Esto es especialmente relevante con una actualización simultánea porque esta operación implica la creación de estructuras de datos temporales. Es recomendable pasar la aspiradora después de la actualización. Pasar la aspiradora es un tema extenso en sí mismo y fuera del alcance de esta guía.

Actualización automática

A partir de noviembre de 2022, PostgreSQL no tiene funciones para actualizar automáticamente las vistas materializadas. Sin embargo, es posible configurar actualizaciones automáticas utilizando otras herramientas.

Trabajos de cron

Un enfoque común para actualizar automáticamente las vistas materializadas es mediante el uso de trabajos cron:

                      
                        15 * * * * psql -d name_of_your_database -c "REFRESH MATERIALIZED VIEW CONCURRENTLY mv_products_orders"

                      
                    

Agregar esta línea al crontab del usuario postgres llamará al psql comando cada 15 minutos y pasarle como parámetros el nombre de la base de datos y el comando SQL para refrescar la vista materializada.

Para el psql comando anterior, tenga en cuenta que si no creó o se conectó explícitamente a una base de datos específica, de manera predeterminada, las consultas se ejecutan en el postgres base de datos.

Consejo práctico: Dado que solo se puede ejecutar una operación de actualización a la vez, es importante tener una idea de cuánto tiempo lleva una operación de actualización antes de programar trabajos cron para ella.

disparadores

También es posible utilizar disparadores para actualizar vistas materializadas. Para ello, cree una función que actualice la vista materializada. En aquellas tablas cuyos datos van a la vista materializada, configure un disparador para llamar a esta función después INSERT , UPDATE y DELETE operaciones.

Crear un PL/pgSQL – el lenguaje de procedimientos SQL función que refresca la vista materializada:

                      
                        CREATE OR REPLACE FUNCTION mv_refresh()

RETURNS trigger LANGUAGE plpgsql AS $$

BEGIN

    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_products_orders;

    RETURN NULL;

END;

$$;

                      
                    

Cree un disparador que llame a esta función cuando ciertas operaciones ( INSERT , UPDATE y DELETE ) se ejecutan en el orders mesa:

                      
                        CREATE TRIGGER mv_trigger 

AFTER INSERT OR UPDATE OR DELETE

ON orders

FOR EACH STATEMENT EXECUTE PROCEDURE mv_refresh();

                      
                    

Compruebe que la definición de la orders la tabla incluye el gatillo:

                      
                        d orders

                      
                    

Del mismo modo, agregue un disparador para llamar al mv_refresh() funcionan cuando los datos en el products cambios de mesa.

Eliminar una fila de la tabla de pedidos:

                      
                        DELETE FROM orders WHERE order_id = 1 AND product_id = 2;

                      
                    

Compruebe que la vista materializada ya no incluye la fila eliminada:

                      
                        SELECT * FROM mv_products_orders;

                      
                    

Descartar vistas materializadas

Descartar una vista materializada es similar a descartar una vista normal.

                      
                        DROP MATERIALIZED VIEW mv_products_orders;

                      
                    

Para colocar la vista materializada junto con todos los demás objetos que dependen de ella, use el CASCADE opción:

                      
                        DROP MATERIALIZED VIEW IF EXISTS mv_products_orders CASCADE;

                      
                    

El comando anterior descarta ambas vistas materializadas: mv_products_orders tanto como my_mv que fue creado en base a él.

Conclusión

Como todas las herramientas de optimización, el uso de vistas materializadas implica compensaciones. Es importante comprender las necesidades específicas de cada caso de uso antes de decidir si una herramienta es adecuada.

Costos

Las vistas materializadas consumen almacenamiento adicional, pero en la práctica, el costo del almacenamiento adicional no es un factor decisivo cuando el almacenamiento es barato. Además, tenga en cuenta la actualidad de los datos; si las tablas subyacentes se actualizan con frecuencia, es probable que los datos almacenados en caché por la vista materializada estén parcialmente desactualizados cuando se utilice. Este es un problema para las consultas que necesitan devolver datos en tiempo real. Las actualizaciones automáticas ayudan con la actualidad de los datos, pero su uso conlleva compensaciones, especialmente para tablas grandes y bases de datos con muchas operaciones de escritura.

Casos de uso

Las vistas materializadas ayudan a mejorar el rendimiento, a menudo de manera significativa, en situaciones en las que el sistema necesita manejar grandes volúmenes de las mismas consultas complejas (conocidas de antemano). Para example:

  • Aplicaciones de informes y análisis que involucran consultas complejas en tablas grandes

  • Diseños de bases de datos que involucran datos no estructurados o semiestructurados, donde la consulta es ineficiente

  • Tableros que presentan información cotejada o consolidada (diaria, mensual, etc.)

  • Consultas que involucran tablas externas y almacenes de datos, donde puede ser lento o costoso consultar la fuente de datos repetidamente

  • Proporcionar servicios de API a terceros donde los requisitos contractuales a menudo requieren una estructura de API consistente y donde se esperan cargas pesadas

El uso de vistas materializadas no es necesario cuando la consulta subyacente es simple o rápida.

Las vistas materializadas son no adecuado para consultas que impulsan aplicaciones en tiempo real como transacciones en vivo, ofertas en línea, resultados deportivos, mensajería, noticias en vivo y similares.

Título del artículo Nombre (opcional) Correo electrónico (opcional) Descripción

Enviar sugerencia

Related Posts