De vistas materializadas a agregados continuos: mejora de PostgreSQL con análisis en tiempo real

Permitir a los usuarios acceder a análisis de datos en tiempo real es una capacidad clave de muchas aplicaciones modernas. Imagínese utilizando su plataforma SaaS favorita: probablemente haya un panel intuitivo que presenta datos en tiempo real e información histórica. Probablemente pueda interactuar con la plataforma, crear informes personalizados, explorar métricas detalladas y visualizar tendencias que abarcan semanas o meses.

Ciertamente no querrás que esta plataforma sea lenta como usuario. Esto significa que la base de datos que impulsa estos productos debe ser rápida a la hora de ejecutar consultas sobre grandes volúmenes de datos, incluidas consultas analíticas complejas.

Mientras PostgreSQL es la base de datos más querida entre los desarrolladores de hoy , no es conocido por ser rápido a la hora de consultar grandes volúmenes de datos. Pero no te preocupes: Postgres siempre tiene una herramienta en su caja de herramientas. Una de las mejores son las vistas materializadas.

¿Qué son las vistas materializadas de PostgreSQL?

Basado en la técnica de materialización, las vistas materializadas de PostgreSQL precalculan comúnmente consultas y almacenan los resultados como una tabla. A diferencia de las vistas estándar de PostgreSQL, que ejecutan la consulta subyacente cada vez que se hace referencia a la vista, las vistas materializadas conservan el resultado de la consulta de origen en la base de datos. Lo mejor de esto es que su base de datos no tiene que ejecutar la consulta cada vez que la ejecuta: los resultados ya están accesibles en el disco; obtendrá la respuesta a su consulta mucho más rápido.

Esta es una manera increíble de optimizar las respuestas a consultas que requieren muchos recursos para su cálculo. Por ejemplo, consultas que pueden implicar el procesamiento de grandes volúmenes de datos, agregaciones o múltiples uniones.

Trabajar con vistas materializadas es súper sencillo. Para crear una vista, debe utilizar la declaración CREATE MATERIALIZED VIEW y la consulta que elija.

Una vez creada su vista materializada, puede consultarla como una tabla PostgreSQL normal:

 CREATE MATERIALIZED VIEW customer_orders AS SELECT customer_id, COUNT(*) as total_orders FROM orders GROUP BY customer_id;

 -- Query the materialized view SELECT * FROM customer_orders;

Esta vista materializada rápidamente quedará obsoleta hasta que la actualice: incluso si agrega nuevos datos a la tabla base (o actualiza o elimina datos), la vista materializada no incluye esos cambios automáticamente; es una instantánea del momento en que fue creada. Para actualizar la vista materializada, debe ejecutar REFRESH MATERIALIZED VIEW .

 REFRESH MATERIALIZED VIEW customer_orders;

Este último punto (cómo se manejan las actualizaciones) es el talón de Aquiles de las vistas materializadas, como veremos en la siguiente sección.

¿Qué pasa con el análisis en tiempo real? Limitaciones de las vistas materializadas de PostgreSQL

Como decíamos, las vistas materializadas de PostgreSQL son una poderosa herramienta para acelerar las consultas que se ejecutan con frecuencia, especialmente si estas consultas abarcan grandes volúmenes de datos. Pero las vistas materializadas tienen un aspecto menos que ideal: para mantenerlas actualizadas, es necesario actualizarlas.

Este único problema crea tres limitaciones importantes:

Las actualizaciones son ineficientes y computacionalmente costosas

Al actualizar una vista materializada, la consulta se vuelve a calcular en todo el conjunto de datos. En el fondo, cuando ejecuta una actualización, los datos materializados antiguos se eliminan y luego se sustituyen por datos nuevos rematerializados. Implementar actualizaciones incrementales (donde solo se actualizan los datos modificados) haría que el proceso agregado fuera mucho más eficiente, pero es difícil de implementar correctamente dentro de una base de datos relacional consistente. A veces es posible encontrar soluciones con secuencias de comandos adicionales, pero no son nada sencillas, especialmente para consultas complejas o si los datos llegan tarde.

Las actualizaciones no se ejecutan automáticamente

Como también se mencionó anteriormente, las vistas materializadas no incorporarán automáticamente los datos más recientes. Deben actualizarse ejecutando REFRESH MATERIALIZED VIEW . No es posible ejecutar actualizaciones manuales en un entorno de producción: una configuración mucho más realista sería automatizar la actualización.

Desafortunadamente, las vistas materializadas no tienen una funcionalidad de actualización automática incorporada, por lo que crear un programa de actualización automática para vistas materializadas en PostgreSQL requiere un programador de algún tipo. Esto se puede manejar dentro de la base de datos con una extensión o fuera de la base de datos con un programador como cron. Sin embargo, se gestiona porque las actualizaciones son caras y llevan mucho tiempo. Es muy fácil terminar en una situación en la que no puedes actualizar la vista lo suficientemente rápido.

Las vistas materializadas no muestran resultados actualizados

Una consecuencia de la naturaleza estática de las vistas materializadas es que cuando se consultan, se perderán los datos agregados o modificados desde la última actualización (incluso si esa actualización se realiza según una programación). Si su ventana de programación está configurada en una hora, entonces su agregado estará desactualizado hasta una hora más el tiempo real para realizar la actualización. Pero hoy en día muchas aplicaciones implican la ingesta de un flujo constante de datos y, a menudo, estas aplicaciones tienen que ofrecer resultados actualizados a sus usuarios para garantizar que estén recuperando información precisa al consultar la vista.

Es una lástima que las opiniones materializadas se vean limitadas por estas limitaciones. Si está creando una plataforma SaaS a partir de un conjunto de datos en vivo, con nuevos datos ingresando con frecuencia, ¿deberían descartarse por completo las vistas materializadas?

La respuesta es no. En Timescale, creamos una solución que mejora efectivamente las vistas materializadas para hacerlas más adecuadas para aplicaciones modernas: agregados continuos.

Conozca agregados continuos: vistas materializadas con actualizaciones automáticas para análisis en tiempo real

Imagine un mundo donde las vistas materializadas no sean sólo instantáneas estáticas, sino que se actualicen de forma dinámica y eficiente. Accederá a la mejora del rendimiento de las consultas que busca sin preocuparse por nada más. Bueno, parece que describimos los agregados continuos de Timescale.

Los agregados continuos (disponibles para todas las bases de datos PostgreSQL a través de la extensión TimescaleDB y en AWS a través de la plataforma Timescale) son vistas materializadas mejoradas con capacidades de actualización automatizadas y eficientes y un elemento en tiempo real. Se ven y se sienten casi exactamente como vistas materializadas, pero permiten lo siguiente:

• Actualizaciones automáticas a través de una política de actualización
• Un proceso de actualización más eficiente: cuando se ejecuta una actualización, solo tocará los datos que cambiaron desde la última actualización
• Resultados actualizados, ampliando los casos de uso donde se pueden aprovechar las vistas materializadas (como análisis en tiempo real, paneles en vivo, informes y otros)

Hacer que las actualizaciones sean automáticas y eficientes en el uso de recursos

Crear un agregado continuo es muy similar a crear una vista materializada (y también se puede consultar como una tabla PostgreSQL normal):

 CREATE MATERIALIZED VIEW hourly_sales WITH (timescaledb.continuous) AS SELECT time_bucket(INTERVAL '1 hour', sale_time) as hour, product_id, SUM(units_sold) as total_units_sold FROM sales_data GROUP BY hour, product_id;

Pero a diferencia de las vistas materializadas, crear una política de actualización es sencillo. Puede definir fácilmente el intervalo de actualización dentro de la base de datos, asegurando que su agregado continuo se actualice automática y periódicamente.

El siguiente ejemplo configura una política de actualización para actualizar el agregado continuo cada 30 minutos. El parámetro end_offset define el rango de tiempo de los datos que se actualizarán y schedule_interval establece la frecuencia con la que se actualizará el agregado continuo:

 -- Setting up a refresh policy SELECT add_continuous_aggregate_policy('hourly_sales', end_offset => INTERVAL '1 minute', schedule_interval => INTERVAL '30 minutes');

Cuando esta política de actualización entre en vigor, el proceso será mucho más eficiente que si estuviéramos usando una vista materializada simple. A diferencia de ejecutar REFRESH MATERIALIZED VIEW , cuando se actualiza un agregado continuo, Timescale no descarta todos los datos antiguos y vuelve a calcular el agregado: el motor simplemente ejecuta la consulta con el período de actualización más reciente (por ejemplo, 30 minutos) y lo agrega. a la materialización.

De manera similar, se identifican los UPDATE y DELETE realizados durante este último período, recalculando el fragmento (partición) que los involucra. (Agregados continuos construidos en Timescale's hipertablas , que son tablas PostgreSQL particionadas automáticamente. Esta es una gran ventaja, ya que permite que el motor vuelva a calcular solo particiones específicas frente a toda la tabla cuando los datos han cambiado).

Mostrando resultados actualizados para análisis en tiempo real

Pero, ¿cómo solucionan los agregados continuos el problema de visualizar resultados actualizados? ¿Qué sucede si se agregaron nuevos datos después de la última actualización y consulto el agregado continuo?

Para permitir esta funcionalidad, agregamos funcionalidad de agregación en tiempo real a agregados continuos. Cuando la agregación en tiempo real está habilitada y consulta su agregado continuo, el resultado que verá combinará dos partes:

• Los datos materializados en la vista materializada subyacente, que se actualizó en la última actualización.
• Los datos sin procesar más recientes, aún no materializados, que aún se encuentran exclusivamente en su tabla base (o hipertabla, para ser exactos).

Esta funcionalidad transforma vistas materializadas de instantáneas estáticas en entidades dinámicas, asegurando que los datos almacenados no sean solo un reflejo histórico sino una representación actualizada de los conjuntos de datos subyacentes.

Uso de agregados continuos: ejemplo

Incluso si todo esto suena bien, (con suerte) quedará mucho mejor con un ejemplo.

Imagine una plataforma utilizada por agencias de transporte y empresas de viajes compartidos. Esta plataforma contiene un panel en el que las empresas pueden ver una descripción general del estado de su flota, incluida una tabla con el estado más reciente de las métricas clave y dos visualizaciones que muestran cómo están funcionando las métricas ese día en particular y dentro del contexto de la semana.

Para impulsar esta aplicación, primero tendríamos una hipertabla en la que se insertan constantemente los datos sobre los viajes. La hipertabla podría verse así:

 CREATE TABLE rides ( ride_id SERIAL PRIMARY KEY, vehicle_id INT, start_time TIMESTAMPTZ NOT NULL, end_time TIMESTAMPTZ NOT NULL, distance FLOAT NOT NULL, price_paid FLOAT NOT NULL ); SELECT create_hypertable('rides', 'start_time');

Las hipertablas son muy rápidas y muy escalables: esta tabla seguirá funcionando incluso cuando tenga miles de millones de filas.

Para potenciar la tabla proporcionando una descripción general en vivo, usaríamos un agregado continuo para dividir los datos en 30 minutos. Esto mantendría el proceso rápido y receptivo:

 -- Create continuous aggregate for live overview CREATE MATERIALIZED VIEW live_dashboard WITH (timescaledb.continuous, timescaledb.materialized_only=false)) AS SELECT vehicle_id, time_bucket(INTERVAL '30 minute', start_time) as minute, COUNT(ride_id) as number_of_rides, AVG(price_paid) as average_price FROM rides GROUP BY vehicle_id, minute;

 -- Set up a refresh policy SELECT add_continuous_aggregate_policy('live_dashboard', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL '15 minute');

En el código anterior, el parámetro end_offset garantiza que el agregado no intente actualizar inmediatamente los datos más recientes, lo que permite algo de tiempo de búfer para adaptarse a cualquier retraso en la llegada de los datos. Establecer end_offset en 10 minutes significa que el agregado actualizará los datos que tengan al menos 10 minutos de antigüedad, lo que garantiza que no se pierdan actualizaciones debido a retrasos menores en el flujo de datos. En un caso de uso del mundo real, ajustaría este valor en función del retraso promedio que observe en su canalización de datos.

Para potenciar la visualización que ofrece la vista diaria, crearíamos un segundo agregado continuo. En este gráfico, los datos se muestran por horas, por lo que no necesitamos una granularidad por minuto como la anterior:

 -- Create continuous aggregate for daily overview CREATE MATERIALIZED VIEW hourly_metrics WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS SELECT vehicle_id, time_bucket(INTERVAL '1 hour', start_time) as hour, COUNT(ride_id) as number_of_rides, SUM(price_paid) as total_revenue FROM rides WHERE start_time > NOW() - INTERVAL '1 day' GROUP BY vehicle_id, hour;

 -- Define refresh policy SELECT add_continuous_aggregate_policy('hourly_metrics', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL `1 hour`);

Finalmente, para potenciar el gráfico que ofrece la vista semanal, crearíamos un agregado continuo más, esta vez agregando los datos por día:

 -- Create continuous aggregate to power chart with weekly overview CREATE MATERIALIZED VIEW daily_metrics WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS SELECT vehicle_id, time_bucket(INTERVAL '1 day', start_time) as day, COUNT(ride_id) as number_of_rides, SUM(price_paid) as total_revenue FROM rides WHERE start_time > NOW() - INTERVAL '1 week' GROUP BY vehicle_id, day;

 -- Define refresh policy SELECT add_continuous_aggregate_policy('daily_metrics', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL '1 day);

PD: Para hacer aún más eficiente la experiencia de definir agregados continuos, Timescale introdujo agregados continuos jerárquicos en TimescaleDB 2.9. Una vez que se familiarice con los agregados continuos, puede comenzar a crearlos además de otros agregados continuos; por ejemplo, en el ejemplo anterior, también podría definir los agregados por horas además del agregado por minutos.

Conclusión

Incluso si PostgreSQL no se creó originalmente para aplicaciones que necesitan procesar grandes conjuntos de datos en vivo, adivinen qué: estos tipos de cargas de trabajo ahora están en todas partes. Sin embargo, PostgreSQL viene con características que ayudan con esta tarea. Las vistas materializadas se encuentran entre las más poderosas, ya que permiten precalcular los resultados de las consultas y almacenarlos en el disco para una recuperación rápida.

Sin embargo, las opiniones materializadas tienen tres limitaciones importantes. En primer lugar, activar actualizaciones es muy ineficiente desde el punto de vista computacional. En segundo lugar, incluso configurar estas actualizaciones automáticas no es un proceso perfecto. En tercer lugar, las vistas materializadas no muestran resultados actualizados, ya que excluyen los datos que se agregaron o modificaron desde la última actualización.

Estas limitaciones hacen que las vistas materializadas sean una solución poco práctica para muchas aplicaciones modernas. Para resolver esto, construimos agregados continuos. Estas son vistas materializadas de PostgreSQL en las que puede definir fácilmente una política de actualización, para que las actualizaciones se realicen automáticamente. Esas actualizaciones también son incrementales y, por tanto, mucho más eficientes. Por último, los agregados continuos le permiten combinar los datos que se han materializado con los datos sin procesar agregados y modificados desde la última actualización, lo que garantiza que solo obtendrá resultados actualizados.

Si está ejecutando PostgreSQL en su hardware, puede acceder a agregados continuos mediante instalar la extensión TimescaleDB . Si está en AWS, consulte la plataforma Timescale . Los primeros 30 días son gratis.

Escrito por Carlota Soto y Mat Arye.