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!