Estou projetando uma tabela Postgres para armazenar grandes quantidades de dados de séries temporais e estou tentando descobrir a melhor maneira de estruturar as colunas. Eu olhei respostas como esta , mas como já tem quase 10 anos, queria ver se há alguma coisa nova que eu deveria estar ciente.
Os dados da série temporal vêm de muitas fontes (é a isso que src_idos exemplos se referem). Cada fonte terá um ponto de dados por minuto e cada ponto de dados terá muitas medições diferentes. As medições representam coisas como temperatura, umidade, etc. para aquele minuto específico. Eu os abstraí para serem apenas "medida A", "medida B" e assim por diante para os exemplos. Existem atualmente 20 tipos de medição que precisam ser suportados, e mais serão adicionados no futuro.
A quantidade de dados está na casa dos bilhões de linhas. A grande maioria das gravações adicionará novas linhas para o minuto atual. As consultas de leitura típicas serão para uma fonte, janela de tempo e tipo de medição específicos. Também estou planejando particionar qualquer tabela que eu escolher, talvez em partições de um mês.
Opção 1) Mesa plana
Eu poderia implementar uma mesa plana simples. Uma desvantagem é que, à medida que adiciono mais tipos de medidas ao longo do tempo, terei que atualizar a tabela com novas colunas. Também está começando com 23 colunas, o que parece que estou seguindo o caminho errado.
TABLE data_points (id, src_id, timestamp , measurement_a, measurement_b, ...)
(1 , 1 , 2024-01-01 00:00:00, 100 , 6.8 , ...)
(2 , 2 , 2024-01-01 00:00:00, 55 , 0.1 , ...)
Opção 2) Pares de valores-chave
Isso reduz o número de colunas para um valor definido, portanto não terei que atualizar a tabela com novas colunas à medida que novas medidas forem adicionadas. No entanto, haverá muito mais linhas (20x para começar, já que estou começando com 20 tipos de medição).
TABLE data_points (id, src_id, timestamp , meas_type, meas_value)
(1 , 1 , 2024-01-01 00:00:00, A , 100 )
(2 , 1 , 2024-01-01 00:00:00, B , 6.8 )
...
(3 , 2 , 2024-01-01 00:00:00, A , 55 )
(4 , 2 , 2024-01-01 00:00:00, B , 0.1 )
...
Opção 3) Duas tabelas
Eu poderia ter uma tabela armazenando o src_id e o carimbo de data/hora, e a outra tabela armazenando os dados de medição. Isso é semelhante aos pares de valores-chave, apenas divididos em duas tabelas para que não precise repetir tanto as colunas src_ide .timestamp
Isso pode tornar o particionamento um pouco mais complicado. Além disso, todas as leituras teriam que fazer uma junção, e eu me preocupo mais com o desempenho do que com o tamanho geral do banco de dados, então talvez não valha a pena a sobrecarga?
TABLE data_point_times (id, src_id, timestamp )
(1 , 1 , 2024-01-01 00:00:00)
(2 , 1 , 2024-01-01 00:00:00)
TABLE data_point_values (id, data_point_time_id, meas_type, meas_value)
(1 , 1 , A , 100 )
(2 , 1 , B , 6.8 )
...
(3 , 2 , A , 55 )
(4 , 2 , B , 0.1 )
...
Opção 4) jsonb
Eu poderia obter "o melhor dos dois mundos" usando jsonb; um número estático de colunas com menos linhas. Mas talvez isso tenha desvantagens das quais não conheço?
TABLE data_points (id, src_id, timestamp , data )
(1 , 1 , 2024-01-01 00:00:00, {"measurement_a": 100, "measurement_b": 6.8, ... })
(2 , 2 , 2024-01-01 00:00:00, {"measurement_a": 55 , "measurement_b": 0.1, ... })
Qualquer ajuda é muito apreciada!
Eu recomendo a opção 1.
O problema com a opção 2 é que cada linha da tabela no PostgreSQL tem uma sobrecarga de pelo menos 23 bytes além dos dados, então as tabelas ficarão muito maiores.
A opção 3 é possivelmente pior que a opção 2.
src_idetimestampé pequena, então você não se beneficiará muito com a opção 2, no que diz respeito ao espaço de armazenamento.A opção 4 é uma solução que você pode considerar se tiver centenas de medições.
Usarei termos MQTT: "tópico" representa o identificador exclusivo do sensor (por exemplo, src_id, medição_id), "timestamp" obviamente e "valor" é a medição em si.
Como de costume com séries temporais, presumo que você desejará representar graficamente seus dados, fazer relatórios e calcular agregados para o valor de um sensor durante um período de tempo. A melhor maneira (na verdade, a única maneira) de tornar isso rápido é ter uma boa localidade de referência, ou seja, organizar a tabela no disco conforme ordenada por (tópico, carimbo de data/hora).
Quando isso for feito, recuperando linhas WHERE topic=... AND timestamp BETWEEN ... AND ... ORDER BY timestamp
Requer apenas leituras sequenciais, sem leituras aleatórias em todos os lugares para capturar cada linha
Não requer classificação porque as linhas já estão na ordem solicitada
Opção 1: Mesa plana seria um pesadelo de manutenção, especialmente se você adicionar novas fontes com tipos de medição diferentes das demais.
Opção 2: pares de valores-chave é a maneira clássica de fazer isso, é assim que todo mundo faz, principalmente porque funciona.
O Postgres não implementa tabelas clusterizadas automáticas, mas possui varreduras apenas de índice, então a solução do postgres é usar um índice de cobertura em (tópico, carimbo de data / hora, valor) que será organizado na ordem adequada e atenderá aos requisitos. As consultas descritas acima serão varreduras rápidas apenas de índice e a tabela em si provavelmente nunca será lida.
Opção 3: duas tabelas não permitem o agrupamento dos dados por (tópico, carimbo de data/hora), portanto, serão necessárias leituras aleatórias ao buscar intervalos de carimbo de data/hora, será lento.
Opção 4: jsonb talvez possa ser útil. Se o espírito é empacotar as linhas em jsonb para economizar espaço, você também pode usar hstore. Para percorrer todo o caminho você deve usar as chaves mais curtas possíveis, armazenadas em uma tabela separada para que "measurement_a" se torne algo como "1". Isso não pode ser aplicado com uma chave estrangeira. As desvantagens são que o jsonb é um pouco mais lento e precisa ler os dados de todas as medições, mesmo se você precisar de apenas uma, mas o tamanho total da tabela será menor. Eu diria "meh" neste caso.
Observe que você não precisa de uma chave primária separada: (tópico, carimbo de data/hora) é único e não nulo, portanto pode ser uma chave primária útil. A menos que você queira permitir duas medições diferentes com o mesmo (tópico, carimbo de data / hora), mas isso seria um pouco surpreendente.
Então, eu escolheria a opção 2 com índice de cobertura.
MySQL/InnoDB ou outro banco de dados que suporte tabelas organizadas por índice reduziria pela metade o requisito de armazenamento, pois não precisa armazenar a tabela e o índice, porque o índice é a tabela.
Eu também recomendaria tentar um banco de dados especializado em séries temporais. Existem vários. O que estou usando é o Clickhouse, então não vou comentar outros.
A ideia é que, se você descartar recursos que realmente não precisa no contexto de séries temporais, como atualizações, transações, etc., poderá obter uma sobrecarga muito menor.
Desvantagens:
Vantagens:
A tabela organizada por índice armazena linhas na ordem correta, o que resolve o problema de localidade de referência
O armazenamento colunar coloca todos os valores de uma coluna no mesmo lugar, o que permite uma boa compactação de dados.
Por exemplo, os tópicos são armazenados em ordem, o que resulta em longas sequências do mesmo valor, que são compactadas até quase nada. Os carimbos de data e hora aumentam monótonamente, então a compactação delta funciona bem. Se os valores não forem barulhentos, você também obterá longas sequências constantes. Em meus dados de série temporal, obtenho compactação de cerca de 11x, ou seja, cerca de 1 byte por linha de armazenamento. Assim, mais parte da tabela cabe no cache.
Uma consulta como "max(valor) para cada dia" em um tópico com 200 milhões de linhas na tabela de 2,2 bilhões de linhas leva 1,5 segundos. Isto é muito útil para plotagem. Se for muito lento, também suporta agregação automática de visualizações materializadas.
Depende do que você planeja fazer com os dados.
20 medições não são muitas colunas para ser honesto. 1 leitura por minuto não é muito, e bilhões de linhas em uma tabela não são algo para se temer mais do que centenas de linhas (vindos de alguém que trabalhou com tabelas tão grandes). Na verdade, 1 leitura por minuto equivale a apenas 500.000 linhas por ano por dispositivo. Então você teria que ter pelo menos 2.000 dispositivos rodando simultaneamente para atingir os bilhões de linhas que você mencionou, dentro de um ano.
Opção 1
Em qualquer caso, uma tabela " plana " regular pode lidar perfeitamente com linhas dessa ordem de magnitude, até mesmo trilhões de linhas.
Se seus casos de uso envolverem uma combinação de manipulação, cálculo ou agregação de algumas das medidas que você planeja armazenar, eu preferiria a Opção 1, pois ela lhe dará mais flexibilidade para consultar as medidas em seus tipos de dados nativos. Também permite que o sistema de banco de dados crie estatísticas apropriadas com base nos dados dessas colunas individualmente e em seus tipos, o que ajuda o planejador de consultas a tomar melhores decisões na hora de elaborar um plano de consulta eficiente para executar essas consultas.
Existem várias maneiras com o mínimo de complicações para lidar com a alteração do esquema da tabela à medida que, por exemplo, novas medidas são adicionadas.
Opção 4
Se você planeja usar o banco de dados apenas para armazenar os dados como estão e recuperá-los como estão, apenas para exibi-los em outro lugar, essa também pode ser uma boa opção. A vantagem que ela tem sobre a Opção 1 é que você não precisa realmente gerenciar nenhuma alteração de esquema à medida que mais medidas são adicionadas, etc. (Embora seja realmente muito simples de gerenciar na Opção 1, então, na IMO, isso não é realmente uma preocupação. )
Pode haver uma sobrecarga insignificante de boxe e unboxing do JSON, mas dependendo de quantas linhas você recupera por vez e como você as consome, isso provavelmente não importaria.
opção 2
Ao contrário do que o bobflux recomendou, eu não tentaria armazenar pares de valores-chave em um RDBMS, que não é um armazenamento de valores-chave. Isso seria desnormalizado e teria desvantagens, como estatísticas inadequadas que podem afetar o desempenho e tornar mais difícil a consulta caso você precise manipular, calcular ou agregar os dados.
Não vejo necessidade de considerar essa opção, mas se você quiser seguir esse caminho, mude para um sistema de banco de dados de armazenamento de valor-chave especificamente, pelo menos, para que possa se beneficiar de um mecanismo de banco de dados projetado para lidar com essa estrutura.