Não há realmente uma 'melhor maneira' de armazenar dados de séries temporais e, honestamente, depende de vários fatores. No entanto, vou me concentrar principalmente em dois fatores, sendo eles:

(1) Quão sério é este projeto que merece seu esforço para otimizar o esquema?

(2) Como serão seus padrões de acesso de consulta ?

Com essas questões em mente, vamos discutir algumas opções de esquema.

mesa plana

A opção de usar uma mesa plana tem muito mais a ver com a questão (1) , onde se não for um projeto sério ou de grande escala, você achará muito mais fácil não pensar muito no esquema, e basta usar uma mesa plana, como:

CREATE flat_table(
  trip_id integer,
  tstamp timestamptz,
  speed float,
  distance float,
  temperature float,
  ,...);

Não há muitos casos em que eu recomendaria este curso, apenas se for um projeto pequeno que não justifique muito do seu tempo.

Dimensões e Fatos

Portanto, se você superou o obstáculo da pergunta (1) e deseja um esquema com mais desempenho, essa é uma das primeiras opções a serem consideradas. Inclui alguma normailização básica, mas extraindo as quantidades 'dimensionais' das quantidades 'factuais' medidas.

Essencialmente, você vai querer uma tabela para registrar informações sobre as viagens,

CREATE trips(
  trip_id integer,
  other_info text);

e uma tabela para registrar timestamps,

CREATE tstamps(
  tstamp_id integer,
  tstamp timestamptz);

e, finalmente, todos os seus fatos medidos, com referências de chave estrangeira às tabelas de dimensão (ou seja , meas_facts(trip_id)referências trips(trip_id)e meas_facts(tstamp_id)referências tstamps(tstamp_id))

CREATE meas_facts(
  trip_id integer,
  tstamp_id integer,
  speed float,
  distance float,
  temperature float,
  ,...);

Isso pode não parecer muito útil a princípio, mas se você tiver, por exemplo, milhares de viagens simultâneas, todas elas podem estar fazendo medições uma vez por segundo, no segundo. Nesse caso, você teria que registrar novamente o carimbo de data/hora para cada viagem, em vez de usar apenas uma única entrada na tstampstabela.

Caso de uso: Este caso será bom se houver muitas viagens simultâneas para as quais você está registrando dados e você não se importa em acessar todos os tipos de medição juntos.

Como o Postgres lê por linhas, sempre que você quiser, por exemplo, as speedmedições em um determinado intervalo de tempo, você deve ler toda a linha da meas_factstabela, o que definitivamente atrasará uma consulta, embora se o conjunto de dados com o qual você está trabalhando for não muito grande, então você nem notaria a diferença.

Dividindo seus fatos medidos

Para estender um pouco mais a última seção, você pode dividir suas medidas em tabelas separadas, onde, por exemplo, mostrarei as tabelas de velocidade e distância:

CREATE speed_facts(
  trip_id integer,
  tstamp_id integer,
  speed float);

e

CREATE distance_facts(
  trip_id integer,
  tstamp_id integer,
  distance float);

Claro, você pode ver como isso pode ser estendido para as outras medições.

Caso de uso: portanto, isso não proporcionará uma velocidade tremenda para uma consulta, talvez apenas um aumento linear na velocidade quando você estiver consultando sobre um tipo de medição. Isso ocorre porque, quando você deseja pesquisar informações sobre velocidade, precisa apenas ler as linhas da speed_factstabela, em vez de todas as informações extras e desnecessárias que estariam presentes em uma linha da meas_factstabela.

Portanto, se você precisar ler grandes quantidades de dados sobre apenas um tipo de medição, poderá obter algum benefício. Com o caso proposto de 10 horas de dados em intervalos de um segundo, você leria apenas 36.000 linhas, portanto, nunca encontraria um benefício significativo em fazer isso. No entanto, se você estivesse olhando os dados de medição de velocidade para 5.000 viagens que duraram cerca de 10 horas, agora você está olhando para a leitura de 180 milhões de linhas. Um aumento linear na velocidade para tal consulta pode trazer algum benefício, desde que você só precise acessar um ou dois dos tipos de medição por vez.

Arrays/HStore/ & TOAST

Você provavelmente não precisa se preocupar com essa parte, mas conheço casos em que isso importa. Se você precisa acessar ENORMES quantidades de dados de séries temporais e sabe que precisa acessar tudo isso em um bloco enorme, pode usar uma estrutura que fará uso das tabelas TOAST , que basicamente armazena seus dados em formatos maiores e compactados segmentos. Isso leva a um acesso mais rápido aos dados, desde que seu objetivo seja acessar todos os dados.

Um exemplo de implementação poderia ser

CREATE uber_table(
  trip_id integer,
  tstart timestamptz,
  speed float[],
  distance float[],
  temperature float[],
  ,...);

Nessa tabela, tstartarmazenaria o carimbo de data/hora da primeira entrada na matriz e cada entrada subseqüente seria o valor de uma leitura para o próximo segundo. Isso requer que você gerencie o carimbo de data/hora relevante para cada valor de matriz em um software aplicativo.

Outra possibilidade é

CREATE uber_table(
  trip_id integer,
  speed hstore,
  distance hstore,
  temperature hstore,
  ,...);

onde você adiciona seus valores de medição como pares (chave, valor) de (carimbo de data/hora, medição).

Caso de uso: provavelmente é melhor deixar essa implementação para alguém que esteja mais familiarizado com o PostgreSQL e somente se você tiver certeza de que seus padrões de acesso precisam ser padrões de acesso em massa.

Conclusões?

Uau, isso ficou muito mais longo do que eu esperava, desculpe. :)

Essencialmente, há várias opções, mas você provavelmente obterá o maior retorno possível usando a segunda ou a terceira, pois elas se encaixam no caso mais geral.

PS: Sua pergunta inicial implicava que você carregaria seus dados em massa depois que todos fossem coletados. Se você estiver transmitindo os dados para sua instância do PostgreSQL, precisará fazer algum trabalho adicional para lidar com a ingestão de dados e a carga de trabalho da consulta, mas deixaremos isso para outro momento. ;)

É 2019 e esta pergunta merece uma resposta atualizada.

• Se a abordagem é a melhor ou não, é algo que vou deixar para você comparar e testar, mas aqui está uma abordagem.
• Use uma extensão de banco de dados chamada timescaledb
• Esta é uma extensão instalada no PostgreSQL padrão e lida com vários problemas encontrados ao armazenar séries temporais razoavelmente bem

Tomando seu exemplo, primeiro crie uma tabela simples no PostgreSQL

Passo 1

CREATE TABLE IF NOT EXISTS trip (
    ts TIMESTAMPTZ NOT NULL PRIMARY KEY,
    speed REAL NOT NULL,
    distance REAL NOT NULL,
    temperature REAL NOT NULL
) 

Passo 2

• Transforme isso no que é chamado de hipertabela no mundo do timescaledb.
• Em palavras simples, é uma grande tabela que é continuamente dividida em tabelas menores de algum intervalo de tempo, digamos um dia em que cada minitabela é chamada de bloco

• Esta minitabela não é óbvia quando você executa consultas, embora você possa incluí-la ou excluí-la em suas consultas

  SELECT create_hypertable('trip', 'ts', chunk_time_interval => intervalo '1 hora', if_not_exists => TRUE);

• O que fizemos acima foi pegar nossa tabela de trip, dividi-la em tabelas de mini chunks a cada hora com base na coluna 'ts'. Se você adicionar um timestamp de 10:00 a 10:59, eles serão adicionados a 1 bloco, mas 11:00 serão inseridos em um novo bloco e isso continuará infinitamente.

• Se você não deseja armazenar dados infinitamente, também pode DROP blocos com mais de 3 meses usando

  SELECT drop_chunks(intervalo '3 meses', 'viagem');

• Você também pode obter uma lista de todos os blocos criados até a data usando uma consulta como

  SELECT chunk_table, table_bytes, index_bytes, total_bytes FROM chunk_relation_size('trip');

• Isso lhe dará uma lista de todas as mini-tabelas criadas até a data e você poderá executar uma consulta na última mini-tabela se quiser desta lista

• Você pode otimizar suas consultas para incluir, excluir blocos ou operar apenas nos últimos N blocos e assim por diante