Carregando 1 trilhão de dados meteorológicos no TimescaleDB

Construindo um data warehouse de clima, parte 1: carregando 1 trilhão de linhas de dados meteorológicos no TimescaleDB

O que significa o que estamos fazendo

Por que construir um data warehouse de clima

• Pensamos que seria bom reunir e analisar dados meteorológicos históricos do mundo inteiro para estudar sinais de mudança climática
• Com um data warehouse meteorológico em grande escala, é possível identificar por região se Jacarta realmente ficou mais quente, se as tempestades se intensificaram, ou se o Chile está ficando mais quente no geral ou mais nublado
• Isso permite descobrir quais regiões do planeta passaram pelas maiores mudanças climáticas e que tipos de mudança ocorreram
• Para fazer esse tipo de análise em escala global, é preciso acelerar as consultas no data warehouse, e o volume de dados é enorme
• O primeiro passo é carregar os dados no PostgreSQL. Usar TimescaleDB para acelerar consultas de séries temporais e PostGIS para acelerar consultas geoespaciais parece promissor

Introdução aos dados

• Foram usados dados do produto de reanálise climática ERA5, e não dados de observação direta
• O ERA5 é o resultado da execução de um modelo climático restringido por dados observados; onde há muitas observações, ele se parece com os dados observados, e onde não há observações, mantém consistência física e concorda com as estatísticas climáticas
• O ERA5 fornece dados horários de todo o planeta desde 1940, com resolução de 0,25 grau. Há mais de 750 milhões de linhas de dados por variável, como temperatura, precipitação, nebulosidade e velocidade do vento
• Inserir esses dados rapidamente em um banco de dados relacional não é algo simples

Métodos de inserção de dados

Comando insert de linha única

• É o método mais simples, mas extremamente lento. Com 3.000 inserções por segundo, levaria cerca de 8 anos para carregar todos os dados
• O overhead é grande: análise de sintaxe, validação de tabela/coluna, plano de execução, bloqueio de tabela, escrita em buffer, escrita em disco, commit etc.

insert com múltiplos valores

• Insere várias linhas com um único comando insert. Reduz o overhead de rede, análise de sintaxe e plano de execução
• O psycopg3 foi o mais rápido, com 25.000 a 30.000 inserções por segundo
• Ainda assim, levaria cerca de 10 meses para carregar todos os dados

Comando copy

• Método otimizado para carga em massa. Lê diretamente de arquivos CSV ou binários e otimiza análise, planejamento e uso de WAL
• Se você já tiver um CSV, pode usar o comando copy de forma simples
• O copy do psycopg3 consegue inserir mais de 100 mil linhas por segundo. Mesmo incluindo o overhead, é possível carregar tudo em menos de 3 meses
• Em inserções longas e rápidas com copy, é preciso tomar cuidado com gargalos

copy em paralelo

• Executa vários trabalhos de copy em paralelo para aumentar a velocidade
• Inserções em uma única tabela não se beneficiam tanto da paralelização, então acima de 16 workers não há ganho de desempenho

Uso de ferramentas externas

• Benchmarks com pg_bulkload e timescaledb-parellel-copy
• O pg_bulkload é rápido, mas por padrão ignora o WAL, então não é seguro
• O timescaledb-parallel-copy permite inserção segura com múltiplos workers, alcançando mais de 300 mil linhas por segundo

Ajuste de configuração do PostgreSQL

• Desativar fsync e full_page_writes pode evitar escritas em disco e acelerar mais, mas é arriscado
• Tabelas unlogged também não usam WAL e por isso são mais rápidas, mas são truncadas em caso de crash. hypertable não pode ser unlogged

Qual é o melhor método?

• Copiar diretamente para a hypertable com psycopg3 é a melhor opção. Para arquivos CSV, use timescaledb-parallel-copy
• Na paralelização, entre 12 e 16 workers é o ideal
• Desativando as proteções, é possível chegar a 460 mil linhas por segundo, mas isso é arriscado
• Com upgrade de hardware, dá para ir ainda mais rápido
• O ClickHouse pode ser mais veloz, mas o autor escolheu o TimescaleDB porque queria aprender PostgreSQL
• Com 460 mil linhas por segundo, é possível carregar todos os dados em menos de 20 dias

Opinião do GN⁺

• A tentativa de colocar dados ERA5 em um banco relacional para análise é interessante. Até agora, o mais comum era analisar diretamente dados NetCDF com xarray ou dask, mas construir um data warehouse pode permitir consultas mais complexas.
• Impressiona que, mesmo com hardware de 5 anos atrás, o autor consiga inserir 460 mil linhas por segundo. Em hardware atual, talvez seja possível chegar a 1 milhão por segundo. Ainda assim, é preciso cautela ao desativar fsync e full_page_writes, porque isso pode comprometer a integridade do banco.
• Parece que o processamento paralelo do PostgreSQL não ajuda tanto em uma única tabela. Se combinar paralelização com particionamento, pode haver desempenho maior. Também vale considerar soluções de escalabilidade horizontal do Postgres, como o Citus.
• É interessante que os dados ERA5 possam ser usados para análise de mudanças climáticas. Isso pode permitir estudar o clima histórico de regiões com pouca observação direta. Mas o ERA5 continua sendo, em última instância, um produto de modelo. Embora seja ajustado com dados observados, é preciso levar em conta a incerteza.
• Como plataforma de análise, o mais comum é usar data warehouses em nuvem como Snowflake ou BigQuery. Mas aprender mexendo no próprio hardware, como fez o autor, também tem muito valor. Especialmente porque dados climáticos têm volume enorme, então não é simples migrá-los para a nuvem. Fica a expectativa pelos resultados reais da análise.

[jangsc0000]

A opinião do GN+ está em linguagem formal..?

[GN⁺]

Comentários do Hacker News

Em resumo, é o seguinte:

• Ao analisar dados geoespaciais, é importante entender sistemas de coordenadas (CRS) e projeções cartográficas. Para trabalhos geoespaciais em grande escala, o Google BigQuery é o melhor.

• Se um banco de dados relacional é adequado para dados meteorológicos em grade é algo que precisa ser verificado por meio de experimentos.

• O motivo de as Hypertables no Timescale serem lentas pode ser o índice da coluna timestamp criado por padrão. É melhor pular a criação do índice com a opção create_default_indexes=>false ou criar o índice depois de inserir os dados.

• Falta análise sobre quais vantagens existem em mover dados meteorológicos para um RDBMS. Também é possível obter tempos de resposta muito rápidos com serverless + armazenamento de objetos.

• A maioria dos conjuntos de dados meteorológicos/climáticos, como o ERA5, é composta por grades regulares de latitude/longitude, então não é uma boa ideia destruir completamente essa estrutura. É melhor aproveitar versões otimizadas para a nuvem, como o ARCO-ERA5.

• Desativar o WAL no PostgreSQL e executar periodicamente o comando VACUUM FREEZE também pode aumentar ainda mais o desempenho ao carregar grandes volumes de dados.

• Se não for possível usar COPY, também é um bom método codificar as linhas como strings JSON, enviá-las como um único parâmetro de consulta e usar json_to_recordset.