Estou projetando um banco de dados PostgreSQL que recebe leituras de muitas fontes de sensores. Eu pesquisei bastante sobre o design e estou procurando novas informações para me ajudar a sair da rotina aqui.

Para ser claro, não estou procurando ajudadescrevendo as fontes de dados ou quaisquer metadados relacionados. Estou especificamente tentando descobrir a melhor forma de armazenar valores de dados (eventualmente de vários tipos).

A estrutura básica dos dados recebidos é a seguinte:

Para cada dispositivo de registro de dados, existem vários canais.Para cada canal, o criador de logs lê os dados e os anexa a um registro com um registro de data e hora.Canais diferentes podem ter tipos de dados diferentes, mas geralmente um float4 é suficiente.Os usuários devem (por meio das funções do banco de dados) ser capazes de adicionar diferentes tipos de valor, mas essa preocupação é secundária.Registradores e canais também serão adicionados através de funções.

A característica distintiva desse layout de dados é que eu tenho muitos canais associando pontos de dados a um único registro com um carimbo de data e hora e número de índice.

Agora, para descrever o volume de dados e padrões de acesso comuns:

Os dados chegarão para cerca de 5 registradores, cada um com 48 canais, a cada minuto.O volume total de dados nesse caso será 345.600 leituras por dia, 126 milhões por ano, e esses dados precisam ser lidos continuamente pelos próximos 10 anos, pelo menos.Mais registradores e canais serão adicionados no futuro, possivelmente de tipos fisicamente diferentes de dispositivos, mas esperançosamente com uma representação de armazenamento semelhante.O acesso comum incluirá a consulta de tipos de canais semelhantes em todos os registradores e a junção nos registros de data e hora do registrador. Por exemplo, obtenha o canal1 do registrador1, o canal4 do registrador2 e faça uma associação externa completa em logger1.time = logger2.time.

Também devo mencionar que cada registro de data e hora do criador de logs é algo que está sujeito a alterações devido ao ajuste de tempo e será descrito em uma tabela diferente, mostrando a leitura da hora do servidor, a leitura da hora do logger, a latência da transmissão, a latência da transmissão, o ajuste do relógio e o valor ajustado do relógio resultante . Isso acontecerá para um conjunto de registros / registros de data e hora do logger, dependendo da recuperação. Esta é a minha motivação paraRecordTable abaixo, mas, caso contrário, não é motivo de grande preocupação, por enquanto, desde que eu possa fazer referência a uma linha (registrador, hora, registro) de algum lugar que altere os registros de data e hora dos dados associados.

Eu considerei algumas opções de esquema, a mais simples que se assemelha a uma abordagem híbrida de EAV, na qual a própria tabela descreve o atributo, uma vez que a maioria dos atributos será apenas um valor real chamado "valor". Aqui está um layout básico:

RecordTable          DataValueTable
----------           --------------
[PK] id        <--   [FK] record_id
[FK] logger_id       [FK] channel_id
record_number        value
logger_time    

Considerando quelogger_id, record_numberelogger_time são únicos, suponho que estou usando chaves substitutas aqui, mas espero que minha justificativa para economizar espaço seja significativa aqui. Também considerei adicionar um ID PK aoDataValueTable (em vez de o PK serrecord_id echannel_id) para fazer referência a valores de dados de outras tabelas, mas estou tentando resistir à necessidade de tornar esse modelo "flexível demais" por enquanto. No entanto, quero começar a receber dados em breve e não precisar alterar essa parte quando recursos adicionais ou dados de estrutura diferente precisarem ser adicionados posteriormente.

No começo, eu estava criando tabelas de registros para cada criador de logs e depois valorizando as tabelas para cada canal e descrevendo-as em outro lugar (em um só lugar), com vistas para conectá-las a todas, mas isso parecia "errado" porque eu repetia a mesma coisa para muitas vezes. Acho que estou tentando encontrar um meio termo entre muitas tabelas e muitas linhas, mas particionando os dados maiores (DataValueTable) parece estranho, porque eu provavelmente particionariachannel_id, para que cada partição tenha o mesmo valor para cada linha. Além disso, o particionamento nesse sentido exigiria um pouco de trabalho para redefinir as condições de verificação na tabela principal toda vez que um canal é adicionado. O particionamento por data é aplicável apenas aoRecordTable, o que não é realmente necessário, considerando o tamanho relativamente pequeno (7200 linhas por dia com os 5 registradores).

Eu também considerei usar o acima com índices parciais emchannel_id Desde aDataValueTable crescerá muito, mas o conjunto de IDs de canal permanecerá pequeno, mas não tenho certeza de que isso aumentará muito depois de muitos anos. Fiz alguns testes básicos com dados simulados e o desempenho é mais ou menos e quero que continue excepcional quando o volume de dados aumentar. Além disso, alguns expressam preocupação em aspirar e analisar uma tabela grande e lidar com um grande número de índices (até 250 neste caso).

Em uma nota lateral muito pequena, eu também acompanharei as alterações nesses dados e permiti anotações (por exemplo, um pássaro cagou no sensor, para que esses valores fossem ajustados / marcados etc.), portanto, lembre-se disso quando considerar o design aqui, mas é uma preocupação separada por enquanto.

Algumas informações sobre minha experiência / nível técnico, se ajudar a ver de onde venho: Sou um estudante de doutorado em CS e trabalho com dados / bancos de dados regularmente como parte de minha pesquisa. No entanto, minha experiência prática no design de um banco de dados robusto para clientes (isso faz parte de um negócio) com longevidade excepcional e representação flexível de dados é um tanto limitada. Acho que meu principal problema agora é considerar todos os ângulos de abordagem desse problema, em vez de me concentrar em resolvê-lo, e não vejo uma solução "certa" à minha frente.

Então, concluindo, acho que essas são minhas principais consultas para você: se você fez algo assim, o que funcionou para você? Quais são os benefícios / desvantagens que não estou vendo dos vários projetos que propus aqui? Como você pode projetar algo assim, considerando esses parâmetros e padrões de acesso?

Terei o maior prazer em fornecer esclarecimentos / detalhes quando necessário, e desde já agradeço por ser incrível.