Estrategias para implementar la compresión de columnas en grandes bases de datos PostgreSQL

Escalar una base de datos Postgres es un rito de iniciación para aplicaciones en crecimiento. A medida que ve que sus tablas se expanden con millones o incluso miles de millones de filas, sus consultas que alguna vez fueron ágiles comienzan a retrasarse y los crecientes costos de infraestructura comienzan a proyectar una larga sombra sobre sus resultados. Está atrapado en un enigma: no quiere separarse de su amado PostgreSQL, pero parece que necesitará una forma más efectiva de lidiar con sus crecientes conjuntos de datos.

En este artículo, le contaremos la historia de cómo creamos un mecanismo de compresión en columnas flexible y de alto rendimiento para PostgreSQL para mejorar su escalabilidad. Al combinar el almacenamiento en columnas con algoritmos de compresión especializados, podemos lograr tasas de compresión impresionantes, incomparables en cualquier otra base de datos relacional (+95 %).

Al comprimir su conjunto de datos, puede hacer crecer aún más sus bases de datos PostgreSQL. Como veremos a lo largo de este artículo, este diseño de compresión altamente efectivo le permite reducir el tamaño de sus tablas PostgreSQL grandes hasta 10-20 veces. Puede almacenar muchos más datos en discos más pequeños (es decir, ahorrar dinero) mientras mejora el rendimiento de las consultas. La compresión de escala de tiempo también es completamente mutable, lo que facilita la administración y las operaciones de la base de datos: puede agregar, modificar y eliminar columnas en tablas comprimidas, y puede INSERTAR, ACTUALIZAR y ELIMINAR datos directamente.

¡Bienvenido a un PostgreSQL más escalable!

Descripción general del contenido

• Por qué PostgreSQL necesita compresión de bases de datos
• Pero ¿qué pasa con las TOSTADAS?
• ¿Por qué la compresión no es nativa de PostgreSQL? Introducción a las bases de datos orientadas a filas versus columnas
• Bases de datos orientadas a filas o columnas: ¿qué elegir?
• Creación de almacenamiento en columnas en una base de datos orientada a filas
• Convertir PostgreSQL en una tienda híbrida de filas y columnas
• Detrás de escena: desde datos de filas hasta matrices de columnas comprimidas
• Consulta eficiente de datos comprimidos
• Agrupar datos comúnmente consultados por segmentby columna
• Definición de columnas segmentby
• Ajuste avanzado mediante orderby
• La evolución de la compresión de la escala de tiempo
• El resultado final: consultas más rápidas, menos espacio de almacenamiento para bases de datos PostgreSQL grandes
• Compresión y almacenamiento por niveles: ciclo de vida del almacenamiento a escala temporal
• Quédese con PostgreSQL

Por qué PostgreSQL necesita compresión de bases de datos

Pero antes de entrar en detalles sobre cómo construimos la compresión, dediquemos un par de minutos a responder esta pregunta: ¿por qué es necesario agregar un nuevo mecanismo de compresión de bases de datos a PostgreSQL?

Primero comprendamos las necesidades de las aplicaciones modernas y un poco de historia del software.

Nos encanta Postgres: creemos que es la mejor base para crear aplicaciones, ya que su combinación de confiabilidad, flexibilidad y ecosistema rico es muy difícil de igualar con cualquier otra base de datos. Pero Postgres nació hace décadas; esta solidez no está exenta de desventajas.

Hoy en día, los desarrolladores utilizan PostgreSQL para mucho más que el caso de uso tradicional OLTP (procesamiento de transacciones en línea) por el que es más conocido. Muchas aplicaciones exigentes y con uso intensivo de datos, que funcionan las 24 horas del día, los 7 días de la semana y manejan volúmenes de datos cada vez mayores, funcionan con PostgreSQL:

• Las bases de datos PostgreSQL se utilizan para incorporar grandes cantidades de datos de sensores provenientes de sistemas de gestión de tráfico, redes de servicios públicos y monitores de seguridad pública.

  
  

• Las empresas de energía están utilizando PostgreSQL para almacenar y analizar métricas de redes inteligentes y fuentes de energía renovables.

  
  

• En el sector financiero, PostgreSQL es el núcleo de los sistemas que rastrean los datos del mercado en tiempo real.

• Las plataformas de comercio electrónico utilizan PostgreSQL para rastrear y analizar eventos generados por las interacciones de los usuarios.

  
  

• Postgres incluso se está utilizando como base de datos vectorial para impulsar la nueva ola de aplicaciones de IA.

  
  

Como resultado, las tablas de Postgres están creciendo muy rápidamente y llegar a miles de millones de filas es la nueva normalidad en la producción.

Desafortunadamente, PostgreSQL no está preparado de manera nativa para manejar este volumen de datos: el rendimiento de las consultas comienza a retrasarse y la administración de la base de datos se vuelve complicada. Para abordar estas limitaciones, construimos Escala de tiempoDB , una extensión que escala el rendimiento de PostgreSQL para aplicaciones exigentes mediante partición automática, agregación continua, mejoras en el planificador de consultas y muchas más funciones.

La creación de un mecanismo de compresión de alto rendimiento para PostgreSQL fue un desbloqueo igualmente importante. Estos conjuntos de datos en constante crecimiento no sólo suponen un desafío para el buen rendimiento, sino que su acumulación de datos genera discos cada vez más grandes y facturas de almacenamiento más elevadas. PostgreSQL necesitaba una solución.

Pero ¿qué pasa con las TOSTADAS?

Pero ¿qué pasa con el método TOAST existente de PostgreSQL? A pesar de su increíble nombre 🍞😋, TOAST no es eficaz para reducir sistemáticamente el tamaño de sus grandes bases de datos PostgreSQL .

TOAST es el mecanismo automático que utiliza PostgreSQL para almacenar y administrar valores grandes que no caben en páginas de bases de datos individuales. Si bien TOAST incorpora la compresión como una de sus técnicas para lograr esto, la función principal de TOAST no es optimizar el espacio de almacenamiento en todos los ámbitos.

Por ejemplo, si tiene una base de datos de 1 TB formada por pequeñas tuplas, TOAST no le ayudará a convertir sistemáticamente ese 1 TB en 80 GB, sin importar cuántos ajustes intente. TOAST comprimirá automáticamente los atributos de gran tamaño en una fila cuando superen el umbral de 2 KB, pero TOAST no ayuda con valores pequeños (tuplas), ni puede aplicar configuraciones más avanzadas configurables por el usuario, como comprimir todos los datos con más de un mes. en una tabla específica. La compresión de TOAST se basa estrictamente en el tamaño de los valores de las columnas individuales, no en las características más amplias de la tabla o del conjunto de datos.

TOAST también puede introducir una importante sobrecarga de E/S, especialmente para tablas grandes con columnas de gran tamaño a las que se accede con frecuencia. En tales casos, PostgreSQL necesita recuperar los datos fuera de línea de la tabla TOAST, que es una operación de E/S separada del acceso a la tabla principal, ya que PostgreSQL debe seguir los punteros de la tabla principal a la tabla TOAST para leer los datos. datos completos. Esto normalmente conduce a un peor rendimiento.

Por último, la compresión de TOAST no está diseñada para proporcionar índices de compresión especialmente altos, ya que utiliza un algoritmo estándar para todos los tipos de datos.

¿Por qué la compresión no es nativa de PostgreSQL? Introducción a las bases de datos orientadas a filas versus columnas

Esta breve mención de TOAST también nos ayuda a comprender las limitaciones de PostgreSQL para comprimir datos de manera efectiva. Como acabamos de ver, la compresión de TOAST maneja los datos fila por fila, pero esta arquitectura orientada a filas dispersa la homogeneidad en la que prosperan los algoritmos de compresión, lo que lleva a un límite fundamental sobre cuán operativa puede ser una compresión. Esta es una razón fundamental por la que las bases de datos relacionales (como Postgres nativo) a menudo se quedan cortas cuando se trata de optimización del almacenamiento.

Analicemos esto. Tradicionalmente, las bases de datos se clasifican en una de dos categorías:

• Las bases de datos orientadas a filas organizan los datos por filas, y cada fila contiene todos los datos de un registro en particular. Están optimizados para el procesamiento transaccional donde las inserciones, actualizaciones y eliminaciones de registros son frecuentes y son eficientes para sistemas OLTP donde las operaciones involucran registros individuales o pequeños subconjuntos de datos (por ejemplo, recuperar toda la información sobre un cliente específico).

  
  

• Las bases de datos orientadas a columnas (también conocidas como “columnares”) , por otro lado, organizan los datos por columnas. Cada columna almacena todos los datos de un atributo particular en varios registros. Por lo general, están optimizados para sistemas OLAP (procesamiento analítico en línea), donde las consultas a menudo implican agregaciones y operaciones en muchos registros.

  
  

Ilustremos esto con un ejemplo. Digamos que tenemos una tabla users con cuatro columnas: user_id , name , logins y last_login . Si esta tabla almacena los datos de un millón de usuarios, tendrá efectivamente un millón de filas y cuatro columnas, almacenando físicamente los datos de cada usuario (es decir, cada fila) de forma contigua en el disco.

En esta configuración orientada a filas, toda la fila para user_id = 500.000 se almacena de forma contigua, lo que agiliza la recuperación. Como resultado, las consultas superficiales y amplias serán más rápidas en un almacén de filas (por ejemplo, "buscar todos los datos para el usuario X"):

 -- Create table CREATE TABLE users ( user_id SERIAL PRIMARY KEY, name VARCHAR(100), logins INT DEFAULT 0, last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Assume we have inserted 1M user records into the 'users' table -- Shallow-and-wide query example (faster in row store) SELECT * FROM users WHERE user_id = 500000;

Por el contrario, un almacén de columnas almacenará todos los user_id juntos, todos los nombres juntos, todos los valores de inicio de sesión juntos, etc., de modo que los datos de cada columna se almacenen de forma contigua en el disco. Esta arquitectura de base de datos favorece consultas profundas y específicas, por ejemplo, "calcular el número promedio de inicios de sesión para todos los usuarios":

 -- Deep-and-narrow query example (faster in column store) SELECT AVG(logins) FROM users;

Los almacenes en columnas funcionan particularmente bien con consultas limitadas sobre datos amplios . En una base de datos en columnas, solo es necesario leer los datos de la columna logins para calcular el promedio, lo que se puede hacer sin tener que cargar todo el conjunto de datos para cada usuario desde el disco.

Como ya habrás adivinado, almacenar datos en filas frente a columnas también influye en qué tan bien se pueden comprimir los datos. En una base de datos en columnas, las columnas individuales de datos suelen ser del mismo tipo y, a menudo, se extraen de un dominio o rango más limitado.

Como consecuencia, los almacenes en columnas normalmente se comprimen mejor que las bases de datos orientadas a filas. Por ejemplo, nuestra columna logins anterior sería toda de tipo entero y probablemente consistiría en solo un pequeño rango de valores numéricos (y por lo tanto tendría una entropía baja, que se comprime bien). Compare esto con un formato orientado a filas, donde una fila completa de datos comprende muchos tipos y rangos de datos diferentes.

Pero incluso si muestran ventajas en las consultas de estilo OLAP y en la compresibilidad, los almacenes en columnas no están exentos de compensaciones:

• Las consultas que recuperan filas individuales tienen mucho menos rendimiento (a veces incluso son imposibles de ejecutar).

  
  

• Su arquitectura no es tan adecuada para las transacciones ACID tradicionales.

  
  

• A menudo no es posible realizar actualizaciones en tiendas de columnas.

  
  

• Es más fácil para las tiendas basadas en filas aprovechar una índice (por ejemplo, árbol B) para encontrar rápidamente los registros apropiados.

  
  

• Con un almacén de filas, es más fácil normalizar su conjunto de datos, de modo que pueda almacenar de manera más eficiente conjuntos de datos relacionados en otras tablas.

Bases de datos orientadas a filas o columnas: ¿qué elegir?

Entonces, ¿qué es mejor: orientado por filas o por columnas?

Tradicionalmente, se evaluarían las compensaciones entre ambos dependiendo de su carga de trabajo. Si estuviera ejecutando un caso de uso típico de OLTP, probablemente elegiría una base de datos relacional orientada a filas como PostgreSQL; Si su caso de uso era claramente OLAP, podría inclinarse por una tienda en columnas como ClickHouse.

Pero ¿qué pasa si tu carga de trabajo es en realidad una combinación de ambas?

• Las consultas de su aplicación generalmente pueden ser superficiales y amplias, con una consulta individual accediendo a muchas columnas de datos, así como a datos de muchos dispositivos/servidores/elementos diferentes. Por ejemplo, podría estar impulsando una visualización orientada al usuario que requiera mostrar la última temperatura y humedad registradas para todos los sensores en una planta de fabricación específica. Una consulta de este tipo necesitaría acceder a varias columnas en todas las filas que coincidan con los criterios de creación, lo que podría abarcar miles o millones de registros.

  
  

• Pero algunas de sus consultas también pueden ser profundas y limitadas, con una consulta individual que selecciona una cantidad menor de columnas para un sensor específico durante un período más largo. Por ejemplo, es posible que también necesite analizar la tendencia de temperatura de un dispositivo específico durante el último mes para inspeccionar anomalías. Este tipo de consulta se centraría en una sola columna (temperatura), pero necesitaría recuperar esta información de una gran cantidad de filas que corresponden a cada intervalo de tiempo durante el período objetivo.

  
  

• Su aplicación también puede consumir muchos datos y realizar muchas inserciones (añadidas). Como comentamos anteriormente, lidiar con cientos de miles de escrituras por segundo es la nueva normalidad. Es probable que sus conjuntos de datos también sean muy granulares; por ejemplo, es posible que esté recopilando datos cada segundo. Continuando con el ejemplo anterior, su base de datos necesitaría servir estas escrituras pesadas junto con lecturas constantes para potenciar su visualización de cara al usuario en tiempo real.

  
  

• Sus datos se adjuntan principalmente , pero no necesariamente solo se agregan . Es posible que necesite actualizar ocasionalmente registros antiguos o posiblemente registrar datos que lleguen tarde o que estén desordenados.

  
  

Esta carga de trabajo no es ni OLTP ni OLAP en el sentido tradicional. Más bien, incluye elementos de ambos. ¿Entonces lo que hay que hacer?

¡Vuélvete híbrido!

Creación de almacenamiento en columnas en una base de datos orientada a filas

Para atender una carga de trabajo como la del ejemplo anterior, una única base de datos debería incluir lo siguiente:

• La capacidad de mantener altas tasas de inserción, fácilmente de cientos de miles de escrituras por segundo.

  
  

• Soporte para insertar datos tardíos o desordenados, así como modificar datos existentes

  
  

• Suficiente flexibilidad para procesar de manera eficiente consultas tanto superficiales y amplias como profundas y restringidas en un gran conjunto de datos.

  
  

• Un mecanismo de compresión capaz de reducir considerablemente el tamaño de las bases de datos para mejorar la eficiencia del almacenamiento.

  
  

Esto es lo que pretendíamos lograr al agregar compresión de columnas a TimescaleDB (y, por lo tanto, a PostgreSQL).

Convertir PostgreSQL en una tienda híbrida de filas y columnas

Como mencionamos en una sección anterior, creamos TimescaleDB para expandir PostgreSQL con más rendimiento y escalabilidad, haciéndolo adecuado para cargas de trabajo exigentes como datos de series de tiempo. TimescaleDB se implementa como una extensión de PostgreSQL: al hacerlo, hereda todo lo bueno de PostgreSQL, como SQL completo, gran flexibilidad de modelos de datos y consultas, confiabilidad probada en batalla, una apasionada base de desarrolladores y usuarios, y uno de los ecosistemas de bases de datos más grandes. alrededor.

En teoría, esto significa que TimescaleDB también está bloqueado en el formato de almacenamiento orientado a filas de PostgreSQL, con su modesta compresibilidad. En realidad, no hay nada que un poco de ingeniería no pueda solucionar.

Dos observaciones. Primero, la mayoría de las cargas de trabajo grandes de PostgreSQL tienen una estructura similar a una serie de tiempo , es decir, tienen muchos anexos (a diferencia de muchas actualizaciones) con una clave principal poco secuencial, como una marca de tiempo o un ID de evento en serie. En segundo lugar, dichos conjuntos de datos se consultan periódicamente mediante escaneos o resúmenes, no solo consultas puntuales. Con esas observaciones en la mano, diseñamos una nueva característica de almacenamiento en columnas para TimescaleDB (que cubriremos en detalle en la siguiente sección) que nos permite alcanzar niveles incomparables de compresibilidad.

De hecho, no es necesario aplicar esta transformación de fila a columna a toda la base de datos. Como usuario de Timescale, puede transformar sus tablas de PostgreSQL en almacenes híbridos de filas y columnas, seleccionando exactamente qué datos comprimir en forma de columnas. a través de nuestra sencilla API y beneficiarse de ambas arquitecturas de almacenamiento según lo requiera su aplicación.

Ilustremos cómo funciona esto en la práctica con un ejemplo. Imagine un sistema de monitoreo de temperatura que recopile lecturas cada segundo de múltiples dispositivos y almacene datos como marca de tiempo, ID del dispositivo, código de estado y temperatura.

Para acceder de manera eficiente a los datos de temperatura más recientes, particularmente para consultas operativas en las que es posible que desee analizar las últimas lecturas de diferentes dispositivos, puede mantener los datos más recientes (por ejemplo, la última semana) en la estructura PostgreSQL tradicional sin comprimir y orientada a filas. . Esto admite altas tasas de ingesta y también es excelente para consultas puntuales sobre datos recientes:

 -- Find the most recent data from a specific device SELECT * FROM temperature_data WHERE device_id = 'A' ORDER BY timestamp DESC LIMIT 1; -- Find all devices in the past hour that are above a temperature threshold SELECT DISTINCT device_id, MAX(temperature) FROM temperature WHERE timestamp > NOW() - INTERVAL '1 hour'   AND temperature > 40.0;

Pero una vez que estos datos tienen unos días, las consultas superficiales y amplias como la anterior ya no se ejecutan con frecuencia: en cambio, las consultas analíticas profundas y específicas son más comunes. Por lo tanto, para mejorar la eficiencia del almacenamiento y el rendimiento de las consultas para este tipo de consultas, puede optar por transformar automáticamente todos los datos de más de una semana en un formato de columnas altamente comprimido. Para hacerlo en Timescale, definiría una política de compresión como esta:

 -- Add a compression policy to compress temperature data older than 1 week SELECT add_compression_policy('temperature_data', INTERVAL '7 days');

Una vez que sus datos estén comprimidos, ejecutar consultas analíticas profundas y específicas sobre los datos de temperatura (ya sea en un dispositivo específico o en muchos dispositivos) mostraría un rendimiento de consulta óptimo.

 -- Find daily max temperature for a specific device across past year SELECT time_bucket('1 day', timestamp) AS day, MAX(temperature) FROM temperature_data WHERE timestamp > NOW() - INTERVAL '1 year'   AND device_id = 'A' ORDER BY day; -- Find monthly average temperatures across all devices SELECT device_id, time_bucket('1 month', timestamp) AS month, AVG(temperature) FROM temperature_data WHERE timestamp < NOW() - INTERVAL '2 weeks' GROUP BY device_id, month ORDER BY month;

¿Cómo representamos el “cambio” de un formato de fila a uno de columna? Las hipertablas de Timescale sirven para dividir datos en "fragmentos" basados en una clave de partición, como una marca de tiempo u otra columna de ID de serie. Luego, cada fragmento almacena los registros correspondientes a un cierto rango de marcas de tiempo u otros valores para esa clave de partición. En el ejemplo anterior, los datos de temperatura se dividirían por semana de modo que el último fragmento permanezca en formato de fila y todas las semanas anteriores se conviertan a formato de columnas.

Este motor de almacenamiento híbrido de filas y columnas es una herramienta increíblemente poderosa para optimizar el rendimiento de las consultas en grandes bases de datos PostgreSQL y, al mismo tiempo, reducir drásticamente el espacio de almacenamiento. Como veremos más adelante en este artículo, al transformar los datos a un formato de columnas y aplicar mecanismos de compresión especializados, no solo podemos acelerar sus consultas analíticas, sino que también logramos tasas de compresión de hasta el 98 %. ¡Imagínese lo que esto le hace a su factura de almacenamiento!

Detrás de escena: desde datos de filas hasta matrices de columnas comprimidas

Antes de entrar en detalles sobre el rendimiento de las consultas y el ahorro de almacenamiento, primero cubramos cómo funciona este mecanismo: cómo se realiza realmente la transformación de filas a columnas y cómo se aplica la compresión a los datos de las columnas.

Cuando la política de compresión entra en acción, esencialmente transforma lo que tradicionalmente eran numerosos registros individuales en la hipertabla original de PostgreSQL (imagínese 1000 filas densamente empaquetadas) en una estructura de filas singular y más compacta. Dentro de esta forma compactada, cada atributo o columna ya no almacena entradas singulares de cada fila. En cambio, encapsula una secuencia continua y ordenada de todos los valores correspondientes de estas 1000 filas. Nos referiremos a estas 1000 filas como un lote .

Para ilustrarlo, imaginemos una tabla como esta:

 | Timestamp | Device ID | Status Code | Temperature | |-----------|-----------|-------------|-------------| | 12:00:01 | A | 0 | 70.11 | | 12:00:01 | B | 0 | 69.70 | | 12:00:02 | A | 0 | 70.12 | | 12:00:02 | B | 0 | 69.69 | | 12:00:03 | A | 0 | 70.14 | | 12:00:03 | B | 4 | 69.70 |

Para preparar estos datos para la compresión, Timescale primero transformaría estos datos tabulares en un almacén de columnas. Dado un lote de datos (~1000 filas), los datos de cada columna se agregan en una matriz, y cada elemento de la matriz corresponde al valor de una de las filas originales. El proceso da como resultado una sola fila, y cada columna almacena una matriz de valores de ese lote.

 | Timestamp | Device ID | Status Code | Temperature | |------------------------------|--------------------|--------------------|-------------------------------| | [12:00:01, 12:00:01, 12...] | [A, B, A, B, A, B] | [0, 0, 0, 0, 0, 4] | [70.11, 69.70, 70.12, 69....] |

Incluso antes de aplicar algoritmos de compresión, este formato ahorra almacenamiento inmediatamente al reducir en gran medida la sobrecarga interna por fila de Timescale. PostgreSQL normalmente agrega ~27 bytes de sobrecarga por fila (por ejemplo, para control de concurrencia multiversión o MVCC). Entonces, incluso sin ninguna compresión, si nuestro esquema anterior es, digamos, 32 bytes, entonces las 1000 filas de datos de un lote que anteriormente ocupaban [1000 * (32 + 27)] ~= 59 kilobytes ahora ocupan [1000 * 32 + 27 ] ~= 32 kilobytes en este formato.

[ Aparte : esta noción de "agrupar" una tabla más grande en lotes más pequeños y luego almacenar las columnas de cada lote de forma contigua (en lugar de las de toda la tabla) es en realidad un enfoque similar a los "grupos de filas" en el formato de archivo Apache Parquet. ¡Aunque sólo nos dimos cuenta de esa similitud después del hecho!]

Pero la gran ventaja de esta transformación es que ahora, dado un formato en el que datos similares (marcas de tiempo, ID de dispositivos, lecturas de temperatura, etc.) se almacenan de forma contigua, podemos emplear algoritmos de compresión de tipo específico para que cada matriz se comprima por separado. . Así es como Timescale logra tasas de compresión impresionantes.

Timescale emplea automáticamente los siguientes algoritmos de compresión. Todos estos algoritmos son " sin pérdidas ”, por lo que no desperdiciamos precisión ni introducimos imprecisiones a través de nuestra compresión; cualquier descompresión resultante reconstruye perfectamente los valores originales.

• Compresión de gorila para flotadores

  
  

• Delta de delta + Simple-8b con codificación de longitud de ejecución compresión para marcas de tiempo y otros tipos similares a números enteros

  
  

• Compresión de diccionario de filas completas para columnas con algunos valores repetidos (+ compresión LZ en la parte superior)

  
  

• Compresión de matriz basada en LZ para todos los demás tipos

  
  

Ampliamos Gorilla y Simple-8b para manejar la descompresión de datos en orden inverso, lo que nos permite acelerar las consultas que utilizan escaneos hacia atrás.

Hemos descubierto que esta compresión de tipo específico es bastante poderosa: además de una mayor compresibilidad, algunas de las técnicas como Gorilla y delta-of-delta pueden ser hasta 40 veces más rápidas que la compresión basada en LZ durante la decodificación, lo que lleva a un rendimiento de consulta mucho mejor. .

Al descomprimir datos, Timescale puede operar en estos lotes comprimidos individuales, descomprimiéndolos lote por lote y solo en las columnas solicitadas. Entonces, si el motor de consultas puede determinar que solo es necesario procesar 20 lotes (correspondientes a 20 000 filas de datos originales) a partir de un fragmento de tabla que originalmente incluía un millón de filas de datos, entonces la consulta se puede ejecutar mucho más rápido, ya que está leyendo y descomprimiendo. muchos menos datos. Veamos cómo lo hace.

Consulta eficiente de datos comprimidos

El formato anterior basado en matrices presenta un desafío: concretamente, ¿qué filas debe buscar y descomprimir la base de datos para resolver una consulta?

Tomemos nuevamente nuestro ejemplo de datos de temperatura. Varios tipos naturales de consultas surgen una y otra vez: seleccionar y ordenar datos por rangos de tiempo o seleccionar datos en función de su ID de dispositivo (ya sea en la cláusula WHERE o mediante GROUP BY). ¿Cómo podríamos respaldar eficientemente tales consultas?

Ahora, si necesitamos datos del último día, la consulta debe navegar a través de datos de marca de tiempo, que ahora forman parte de una matriz comprimida. Entonces, ¿la base de datos debería descomprimir fragmentos enteros (o incluso toda la hipertabla) para localizar los datos del día reciente?

O incluso si pudiéramos identificar los "lotes" individuales que están agrupados en una matriz comprimida (descrita anteriormente), ¿los datos de diferentes dispositivos están intercalados, por lo que necesitamos descomprimir toda la matriz para encontrar si incluye datos sobre un dispositivo específico? Si bien este enfoque más simple aún podría generar una buena compresibilidad, no sería tan eficiente desde el punto de vista del rendimiento de las consultas.

Para resolver el desafío de localizar y descomprimir datos de manera eficiente para consultas específicas en su formato de columnas, Timescale introduce el concepto de columnas "segmentar por" y "ordenar por" .

Agrupar datos comúnmente consultados por segmentby columna

Recuerde que los datos en Timescale se convierten inicialmente a formato de columnas comprimidas, fragmento por fragmento. Para mejorar la eficiencia de las consultas que filtran según una columna específica (por ejemplo, consultas frecuentes por device_id ), tiene la opción de definir esta columna en particular como " comprimir_segmentby " columna. Este enfoque es muy beneficioso para organizar los datos comprimidos.

Estas columnas segmentby se utilizan para particionar lógicamente los datos dentro de cada fragmento comprimido. En lugar de crear una matriz comprimida de valores arbitrarios como se muestra arriba, el motor de compresión primero agrupa todos los valores que tienen la misma clave segmentby .

Por lo tanto, 1000 filas de datos sobre ID_dispositivo A están respaldadas densamente antes de almacenarse en una sola fila comprimida, 1000 filas sobre ID_dispositivo B, y así sucesivamente. Por lo tanto, si se elige device_id como columna segmentby , cada fila comprimida incluye lotes de datos en columnas comprimidas sobre un ID de dispositivo específico, que se almacena sin comprimir en esa fila. Timescale además crea un índice sobre estos valores segmentados dentro del fragmento comprimido.

 | Device ID | Timestamp | Status Code | Temperature | |-----------|--------------------------------|-------------|-----------------------| | A | [12:00:01, 12:00:02, 12:00:03] | [0, 0, 0] | [70.11, 70.12, 70.14] | | B | [12:00:01, 12:00:02, 12:00:03] | [0, 0, 4] | [69.70, 69.69, 69.70] |

Este almacenamiento contiguo de datos mejora en gran medida la eficiencia de las consultas filtradas por la columna segmentby . Cuando se ejecuta una consulta filtrada por device_id donde device_id es la columna segmentby , Timescale puede seleccionar rápidamente (a través de un índice) todas las filas comprimidas en el fragmento que tienen los ID de dispositivo especificados y omite rápidamente los datos (y evita descomprimir). ) datos no relacionados con los dispositivos solicitados.

Por ejemplo, en esta consulta, Timescale localizará y procesará eficientemente solo aquellas filas comprimidas que contengan datos para el dispositivo_id A:

 SELECT AVG(temperature) FROM sensor_data WHERE device_id = 'A'   AND time >= '2023-01-01'   AND time < '2023-02-01';

Además, las hipertablas de escala de tiempo almacenan metadatos asociados con cada fragmento que especifica el rango de valores que cubre el fragmento. Entonces, si una hipertabla tiene una marca de tiempo dividida por semana, cuando el planificador de consultas ejecuta la consulta anterior, sabe que solo debe procesar esos 4 o 5 fragmentos que cubren el mes de enero, lo que mejora aún más el rendimiento de la consulta.

Definición de columnas segmentby

Puede especificar qué columnas usar para segmentby cuando habilita por primera vez la compresión de una hipertabla. La elección de qué columna utilizar debe basarse en qué columna o columnas se utilizan con frecuencia en sus consultas. De hecho, puede usar varias columnas para segmentar por: por ejemplo, en lugar de agrupar lotes por ID_dispositivo, puede (digamos) agrupar aquellos lotes que tienen el mismo ID_inquilino y ID_dispositivo juntos.

Aún así, tenga cuidado de no exagerar la selectividad: definir demasiadas columnas segmentby disminuirá la eficiencia de la compresión, ya que cada columna segmentby adicional divide efectivamente los datos en lotes cada vez más pequeños.

Si ya no puede crear lotes de datos de 1000 registros, sino que solo tiene cinco registros que tienen las claves segmentby especificadas dentro de un fragmento específico, ¡no se comprimirá bien en absoluto!

Pero una vez que haya identificado por qué columnas desea segmentar, son fáciles de configurar al habilitar la compresión en su hipertabla:

 ALTER TABLE temperature_data SET (   timescaledb.compress,   timescaledb.compress_segmentby = 'device_id' );

Ajuste avanzado mediante orderby

TimescaleDB mejora el rendimiento de las consultas sobre datos comprimidos mediante el ordenamiento estratégico de los datos dentro de cada fragmento, dictado por el parámetro compress_orderby . Si bien la configuración predeterminada de ordenar por marca de tiempo (la clave de partición típica en datos de series temporales) es adecuada para la mayoría de los escenarios, comprender esta optimización puede resultar valioso. Siga leyendo para obtener una perspectiva técnica aún más profunda.

Considere nuevamente el ejemplo de fragmentos semanales y una consulta que solo solicita datos sobre un solo día. En una tabla normal con un índice de marca de tiempo, la consulta podría recorrer este índice de manera eficiente para encontrar los datos del día.

Sin embargo, la situación es diferente con los datos comprimidos: las marcas de tiempo se comprimen y no se puede acceder a ellas sin descomprimir lotes completos. Crear un índice para cada marca de tiempo individual sería contraproducente, ya que podría anular los beneficios de la compresión al volverse excesivamente grande.

Timescale soluciona esto básicamente "clasificando" los datos que se van a agrupar según su marca de tiempo. Luego registra metadatos sobre las marcas de tiempo mínimas y máximas de cada lote. Cuando se ejecuta una consulta, estos metadatos permiten que el motor de consultas identifique rápidamente qué filas comprimidas (lotes) son relevantes para el rango de tiempo de la consulta, lo que reduce la necesidad de una descompresión completa.

Esta metodología funciona muy bien con el uso de columnas segmentadas. Durante el proceso de compresión, los datos primero se agrupan por la columna segmentby, luego se ordenan según el parámetro orderby y finalmente se dividen en “minilotes” más pequeños, ordenados por marca de tiempo, cada uno de los cuales contiene hasta 1000 filas.

La combinación de la segmentación y el ordenamiento de TimescaleDB mejora significativamente el rendimiento de las consultas analíticas y de series temporales comunes. Esta optimización tanto en el tiempo (mediante orderby ) como en el espacio (mediante segmentby ) garantiza que TimescaleDB administre y consulte de manera efectiva datos de series temporales a gran escala, ofreciendo un equilibrio optimizado entre compresión y accesibilidad.

La evolución de la compresión de la escala de tiempo

La primera versión de nuestro diseño de compresión se lanzó en 2019 con TimescaleDB 1.5 . Muchos lanzamientos después, la compresión Timescale ha avanzado mucho.

Una de las principales limitaciones de nuestra versión inicial fue que no permitimos modificaciones adicionales de los datos (por ejemplo, INSERTAR, ACTUALIZAR, ELIMINAR) una vez que los datos se comprimieron sin descomprimir primero manualmente todo el fragmento de hipertabla en el que residían.

Dado que estábamos optimizando para casos de uso con uso intensivo de datos basados en datos analíticos y de series de tiempo, que son principalmente con muchas inserciones y no con muchas actualizaciones, esto fue una limitación mucho menor de lo que habría sido en un caso de uso de OLTP tradicional. donde los datos se actualizan con frecuencia (por ejemplo, una tabla de información del cliente). Sin embargo, como comentamos en este artículo , hay situaciones en las que es necesario el reabastecimiento, y esto complicó significativamente las canalizaciones de desarrolladores que usaban TimescaleDB.

Otra limitación de nuestra versión de compresión inicial fue que no permitíamos modificaciones de esquema en las tablas, incluidos los datos comprimidos. Esto significaba que los desarrolladores no podían evolucionar su estructura de datos sin descomprimir la tabla completa. como agregar nuevas columnas para adaptarse a nuevas métricas o nuevos dispositivos .

Hoy, todas estas limitaciones han desaparecido. Timescale ahora le permite realizar operaciones completas de lenguaje de manipulación de datos (DML) y lenguaje de definición de datos (DDL) sobre datos comprimidos:

• Puede INSERTAR datos en fragmentos comprimidos (con excelente rendimiento).

• Puede realizar ACTUALIZACIONES, UPSERT y ELIMINACIONES.

  
  

• Puede agregar columnas, incluso con valores predeterminados.

  
  

• Puede cambiar el nombre y eliminar columnas.

  
  

Para automatizar la modificación de datos sobre datos comprimidos (haciéndola más sencilla para nuestros usuarios), cambiamos nuestro enfoque de compresión introduciendo un "área de preparación", esencialmente, un fragmento superpuesto que permanece sin comprimir y en el que realizamos las operaciones "sobre datos sin comprimir" en la capucha.

Como usuario, no tienes que hacer nada manualmente: puedes modificar tus datos directamente mientras nuestro motor se encarga de todo automáticamente bajo las sábanas. La capacidad de realizar cambios en los datos comprimidos hace que el motor de almacenamiento híbrido de filas y columnas de Timescale sea mucho más flexible.

Este diseño a través del área de preparación hace que las INSERTACIONES sean tan rápidas como la inserción en fragmentos sin comprimir, ya que esto es realmente lo que está sucediendo (cuando inserta en un fragmento comprimido, ahora está escribiendo en el área de preparación). También nos permitió admitir ACTUALIZAR, UPSERT y DELETE directamente: cuando es necesario modificar un valor, el motor mueve una porción relevante de datos comprimidos al área de preparación, los descomprime, realiza la modificación y (asincrónicamente) los mueve nuevamente. a la tabla principal en su forma comprimida.

(Esta región de datos normalmente opera en la escala de “minilotes” comprimidos de hasta 1000 valores que componen una “fila” en el almacenamiento PostgreSQL subyacente para minimizar la cantidad de datos que deben descomprimirse para admitir modificaciones).

Esta "área de preparación" todavía tiene una semántica transaccional regular y sus consultas ven estos valores tan pronto como se insertan en ella. En otras palabras, el planificador de consultas es lo suficientemente inteligente como para comprender cómo realizar consultas correctamente en estos fragmentos de "preparación" basados en filas y el almacenamiento en columnas normal.

El resultado final: consultas más rápidas, menos espacio de almacenamiento para bases de datos PostgreSQL grandes

En este punto, la siguiente pregunta lógica es: ¿cuál es el resultado final? ¿Cómo afecta la compresión al rendimiento de las consultas y cuánto tamaño de disco puedo ahorrar al usarla?

Rendimiento de las consultas antes y después de la compresión

Como hemos estado comentando en este artículo, los almacenes de columnas generalmente no funcionan muy bien para consultas que recuperan filas individuales, pero tienden a funcionar mucho mejor para consultas analíticas que buscan valores agregados. Esto es precisamente lo que vemos en Timescale: las consultas profundas y restringidas que involucran promedios ven mejoras significativas en el rendimiento cuando se usa la compresión.

Ilustremos esto ejecutando un par de consultas sobre el Conjunto de datos de taxis de Nueva York , uno de los conjuntos de datos de ejemplo que proporcionamos en Timescale. Este conjunto de datos contiene información sobre viajes en taxi, incluidos horarios de recogida y devolución, ubicaciones, distancias, tarifas y más.

Considere la siguiente consulta, que solicita el importe de la tarifa más alta de un subconjunto del conjunto de datos de taxis dentro de un período de tiempo específico:

 SELECT max(fare_amount) FROM demo.yellow_compressed_ht WHERE   tpep_pickup_datetime >= '2019-09-01' AND   tpep_pickup_datetime <= '2019-12-01';

Cuando se ejecuta con el conjunto de datos sin comprimir, el tiempo de ejecución de la consulta es de 4,7 segundos. Estamos utilizando un servicio de prueba pequeño y no optimizado y consultamos muchos millones de filas, por lo que este rendimiento no es el mejor. Pero después de comprimir los datos, el tiempo de respuesta se reduce a 77,074 milisegundos:

Compartamos otro ejemplo. Esta consulta cuenta el número de viajes con un código de tarifa específico dentro de un período de tiempo determinado:

 SELECT COUNT(*) FROM demo.yellow_compressed_ht WHERE   tpep_pickup_datetime >= '2019-09-01' AND   tpep_pickup_datetime <= '2019-12-01' AND   "RatecodeID" = 99;

Cuando se ejecuta con los datos sin comprimir, esta consulta tardaría 1,6 segundos en completarse. La misma consulta que se ejecuta en datos comprimidos finaliza en solo 18,953 milisegundos. ¡Una vez más, vemos una mejora inmediata! Estos son sólo ejemplos rápidos, pero ilustran cuán poderosa puede ser la compresión para acelerar sus consultas.

Cómo la compresión de Timescale reduce el tamaño de almacenamiento de PostgreSQL: ejemplos del mundo real

No olvidemos lo que nos trajo aquí en primer lugar: necesitábamos una táctica que nos permitiera reducir el tamaño de nuestras grandes bases de datos PostgreSQL para poder escalar aún más PostgreSQL. Para mostrar cuán efectiva puede ser la compresión de Timescale para esta tarea, la siguiente tabla incluye algunos ejemplos reales de tasas de compresión observadas entre los clientes de Timescale .

Estos ahorros de almacenamiento se traducen directamente en ahorros de dinero: La plataforma Timescale utiliza precios basados en el uso para el almacenamiento , por lo que si su almacenamiento se reduce, su factura también se reduce proporcionalmente.

La tasa de compresión que finalmente logrará depende de varios factores, incluido el tipo de datos y los patrones de acceso. Pero como puede ver, la compresión de escala de tiempo puede ser extremadamente eficiente. Incluso lo utilizamos mucho internamente para potenciar nuestro producto Insights de cara al cliente con un 96 % de compresión. .

Nuestro equipo puede ayudarle a ajustar la compresión para ahorrarle la mayor cantidad de dinero posible. así que no dudes en comunicarte .

"Con la compresión, hemos visto en promedio una reducción del 97 por ciento [en el tamaño del disco]".

(Michael Gagliardo, Industrial)

“¡Hemos descubierto que la relación de compresión de Timescale es absolutamente fenomenal! Actualmente tenemos una relación de compresión de más de 26, lo que reduce drásticamente el espacio en disco necesario para almacenar todos nuestros datos”.

(Nicolás Quintín, Octava)

"La compresión de Timescale fue tan buena como se anuncia, lo que nos permitió ahorrar un +90 % de espacio [en disco] en nuestra hipertabla subyacente".

(Paolo Bergantino, Grupo METER)

Compresión y almacenamiento por niveles: ciclo de vida del almacenamiento a escala temporal

Por último, no podríamos concluir este artículo sin mencionar el almacenamiento por niveles de Timescale. que acabamos de lanzar en disponibilidad general .

Además de la compresión, ahora tiene otra herramienta que le ayudará a escalar sus bases de datos PostgreSQL aún más en la plataforma Timescale: puede agrupar sus datos más antiguos a los que se accede con poca frecuencia en un nivel de almacenamiento de objetos de bajo costo y al mismo tiempo poder acceder a ellos a través del estándar. SQL.

Este nivel de almacenamiento de bajo costo tiene un precio fijo de $0,021 por GB/mes para datos (más barato que Amazon S3), lo que le permite mantener muchos TB en su base de datos PostgreSQL por una fracción del costo.

Así es como funciona nuestro backend de almacenamiento por niveles en la plataforma Timescale y cómo el nivel de almacenamiento bajo juega junto con la compresión:

• Sus datos más recientes se escriben en un nivel de almacenamiento de alto rendimiento optimizado para consultas rápidas e ingestas elevadas. En este nivel, puede habilitar la compresión de columnas de escala de tiempo para reducir el tamaño de su base de datos y acelerar sus consultas analíticas, como hemos estado comentando en este artículo. Por ejemplo, puede definir una política de compresión que comprima sus datos después de 1 semana.

  
  

• Una vez que su aplicación ya no acceda con frecuencia a esos datos, puede organizarlos automáticamente en un nivel de almacenamiento de objetos de menor costo configurando una política de niveles. Los datos en el nivel de almacenamiento de bajo costo siguen siendo totalmente consultables dentro de su base de datos y no hay límite para la cantidad de datos que puede almacenar: hasta cientos de TB o más. Por ejemplo, puede definir una política de niveles que traslade todos sus datos con más de seis meses al nivel de almacenamiento de bajo costo.

  
  

• Una vez que ya no tenga que conservar estos datos en su base de datos, puede eliminarlos mediante una política de retención. Por ejemplo, puedes eliminar todos los datos después de cinco años.

Quédese con PostgreSQL

Le dimos a Postgres un mecanismo eficaz de compresión de bases de datos agregando capacidades de compresión en columnas. Esta es una característica esencial para escalar las bases de datos PostgreSQL en el mundo actual con uso intensivo de datos: la compresión permite grandes ahorros en el uso del disco (almacenar más datos por menos dinero) y mejoras de rendimiento (ejecutar consultas analíticas en grandes volúmenes en milisegundos).

El diseño de compresión de Timescale logra tasas de compresión impresionantes al combinar los mejores algoritmos de compresión de su clase junto con un método novedoso para crear almacenamiento híbrido de filas/columnas dentro de PostgreSQL. Esta capacidad hace que la huella de almacenamiento de Timescale (y por lo tanto de PostgreSQL) esté a la par con las bases de datos en columnas más limitadas y personalizadas.

Pero a diferencia de muchos motores de columnas, Timescale admite la semántica transaccional ACID y soporte directo para modificaciones (INSERT, UPDATE, UPSERT, DELETE) en datos en columnas comprimidos. Debido a que el antiguo modelo de “una base de datos para cargas de trabajo transaccionales, otra para cargas analíticas” está obsoleto, muchas aplicaciones modernas ejecutan cargas de trabajo que se ajustan a ambos patrones. Entonces, ¿por qué mantener dos bases de datos separadas cuando puedes hacerlo todo en PostgreSQL?

Timescale le permite comenzar con PostgreSQL, escalar con PostgreSQL y permanecer con PostgreSQL.

Crea una cuenta nueva y pruebe Timescale hoy: solo le llevará un par de segundos y no se requiere tarjeta de crédito.

- Escrito por Carlota Soto y Mike Freedman .