Cargando un billón de datos meteorológicos en TimescaleDB

Construyendo un data warehouse meteorológico, parte 1: cargar un billón de filas de datos meteorológicos en TimescaleDB

El significado de lo que estamos haciendo

Por qué construir un data warehouse meteorológico

• Surgió la idea de reunir y analizar datos meteorológicos históricos de todo el mundo para estudiar señales del cambio climático
• Si existiera un data warehouse meteorológico a gran escala, sería posible identificar por región si Yakarta realmente se ha vuelto más cálida o si las tormentas se han intensificado, o si Chile en general se está calentando o se ha vuelto más nublado
• Esto permitiría determinar qué regiones del planeta han experimentado más cambios climáticos y qué tipo de cambios han ocurrido
• Para hacer este análisis a escala global, es necesario acelerar las consultas del data warehouse, y la cantidad de datos es enorme
• El primer paso es cargar los datos en PostgreSQL. Usar TimescaleDB para acelerar las consultas de series de tiempo y PostGIS para acelerar las consultas geoespaciales parece prometedor

Introducción a los datos

• No se usan datos de observación reales, sino datos del producto de reanálisis climático ERA5
• ERA5 es el resultado de ejecutar un modelo climático restringido por datos de observación; donde hay muchas observaciones, se parece a ellas, y donde no las hay, mantiene coherencia física y coincide con las estadísticas climáticas
• ERA5 ofrece datos horarios de todo el planeta desde 1940 con una resolución de 0.25 grados. Cada variable, como temperatura, precipitación, nubosidad o velocidad del viento, tiene más de 750 millones de filas
• Insertar rápidamente estos datos en una base de datos relacional no es fácil

Métodos de inserción de datos

Sentencias insert de una sola fila

• Es el método más simple, pero extremadamente lento. A 3,000 inserciones por segundo, cargar todos los datos tomaría unos 8 años
• Hay mucha sobrecarga por análisis sintáctico, validación de tablas/columnas, plan de ejecución, bloqueo de tabla, escritura en buffers, escritura en disco, commit, etc.

insert de múltiples valores

• Inserta varias filas con una sola sentencia insert. Reduce la sobrecarga de red, análisis sintáctico y plan de ejecución
• psycopg3 fue el más rápido, con 25,000 a 30,000 inserciones por segundo
• Aun así, cargar todos los datos tomaría alrededor de 10 meses

Sentencia copy

• Método optimizado para carga masiva de datos. Lee directamente desde archivos CSV o binarios y optimiza el análisis, la planificación y el uso de WAL
• Si ya existe un CSV, se puede usar fácilmente la sentencia copy
• copy de psycopg3 puede insertar más de 100,000 filas por segundo. Incluso con la sobrecarga, es posible cargar todos los datos en menos de 3 meses
• Al hacer inserciones rápidas durante mucho tiempo con copy, hay que tener cuidado con los cuellos de botella

copy en paralelo

• Ejecutar varios trabajos copy en paralelo mejora la velocidad
• Las inserciones en una sola tabla no se benefician mucho del paralelismo, así que con más de 16 workers ya no hay mejora de rendimiento

Uso de herramientas externas

• Benchmark de pg_bulkload y timescaledb-parellel-copy
• pg_bulkload es rápido, pero no es seguro porque por defecto omite WAL
• timescaledb-parallel-copy permite insertar de forma segura más de 300,000 filas por segundo usando múltiples workers

Ajuste de configuración de PostgreSQL

• Desactivar fsync y full_page_writes evita escrituras en disco y puede acelerar más el proceso, pero es riesgoso
• Las tablas unlogged tampoco usan WAL, así que son rápidas, pero se truncan en caso de crash. Las hypertables no pueden ser unlogged

¿Cuál es el mejor método?

• Lo mejor es hacer copy directamente a la hypertable con psycopg3. Si se trabaja con archivos CSV, usar timescaledb-parallel-copy
• Un paralelismo de 12 a 16 workers parece adecuado
• Si se desactivan ciertas protecciones, se puede llegar hasta 460,000 filas por segundo, pero es riesgoso
• Con una mejora de hardware, es posible alcanzar velocidades aún mayores
• ClickHouse podría ser más rápido, pero se eligió TimescaleDB porque se quería aprender PostgreSQL
• A 460,000 filas por segundo, es posible cargar todos los datos en unos 20 días

La opinión de GN⁺

• Resulta interesante el intento de meter datos ERA5 en una base de datos relacional para analizarlos. Hasta ahora lo común era analizar directamente datos NetCDF con xarray o dask, pero al construir un data warehouse se podrían hacer consultas más complejas.
• Impresiona que, aun con hardware de hace 5 años, el autor pueda cargar 460,000 filas por segundo. Con hardware moderno, quizá se podría llegar a un millón por segundo. Aun así, desactivar fsync y full_page_writes puede afectar la integridad de la base de datos, así que hay que tener cuidado.
• Parece que el procesamiento paralelo de PostgreSQL no ayuda demasiado en una sola tabla. Si se combinara el paralelismo con particionamiento, podría lograrse un rendimiento mayor. También valdría la pena considerar soluciones de escalado horizontal para Postgres como Citus.
• Es interesante que los datos ERA5 puedan usarse para analizar el cambio climático. Eso permitiría estudiar climas históricos en regiones con pocos datos de observación. Pero ERA5 sigue siendo, al fin y al cabo, el resultado de un modelo. Aunque esté ajustado con datos observados, hay que tener en cuenta que existe incertidumbre.
• Como plataforma de análisis, lo habitual sería usar data warehouses en la nube como Snowflake o BigQuery. Pero aprender manejando hardware propio, como hace el autor, también tiene mucho valor. Sobre todo porque los datos climáticos tienen un volumen tan grande que moverlos a la nube no es sencillo. Queda expectativa por ver los resultados reales del análisis.

[jangsc0000]

¿La opinión de GN+ está en lenguaje formal..?

[GN⁺]

Comentarios de Hacker News

En resumen, lo siguiente:

• Al analizar datos geoespaciales, es importante entender los sistemas de coordenadas (CRS) y las proyecciones cartográficas. Para trabajos geoespaciales a gran escala, Google BigQuery es de lo mejor.

• Hay que comprobar mediante experimentos si una base de datos relacional es adecuada para datos meteorológicos en cuadrícula.

• La razón por la que Hypertable en Timescale puede ser lento podría deberse al índice de la columna timestamp que se crea por defecto. Conviene omitir la creación del índice con la opción create_default_indexes=>false o crear el índice después de cargar los datos.

• Falta análisis sobre qué ventajas aporta mover los datos meteorológicos a un RDBMS. Con serverless + almacenamiento de objetos también se pueden obtener tiempos de respuesta muy rápidos.

• La mayoría de los conjuntos de datos meteorológicos/climáticos, como ERA5, están compuestos por una cuadrícula regular de latitud y longitud, por lo que no es buena idea destruir completamente esa estructura. Es mejor aprovechar versiones optimizadas para la nube como ARCO-ERA5.

• En PostgreSQL, desactivar WAL y ejecutar periódicamente el comando VACUUM FREEZE puede mejorar aún más el rendimiento al cargar grandes volúmenes de datos.

• Si no se puede usar COPY, también es una buena opción codificar las filas como cadenas JSON, enviarlas como un único parámetro de consulta y usar json_to_recordset.