birgersp's questions

Planejando algumas tabelas novas, estou tentando decidir se um índice deve ser "crescente" ou "descendente".

A tabela será bem grande (imagino aproximadamente 2.000 inserções por minuto, migrando inicialmente de uma tabela diferente com cerca de 1 bilhão de linhas).

Usarei a extensão timescaledb para isso (para particionamento por tempo).

É assim que a tabela pode ser criada:

create table "Sample"(
    "id" bigserial,
    "deviceId" int not null,
    "timestamp" timestamptz not null,
    "value" float8 not null
);
select create_hypertable('"Sample"', 'timestamp'); -- creates a desc index on "timestamp"

create index on "Sample"("deviceId", "timestamp"); -- should this be "desc"?

Estas são as duas consultas mais comuns que executaremos (deviceId e timestamps podem variar, é claro):

select "timestamp", "value"
from "Sample" where "deviceId"=123 and "timestamp"<'2024-01-01Z'
order by "timestamp" desc limit 1;

E

select "timestamp", "value"
from "Sample" where "deviceId"=123 and "timestamp" between '2024-01-01Z' and '2024-02-01Z'
order by "timestamp" asc;

Então, o que estou tentando entender é em que ordem o "carimbo de data e hora" deve estar? E porque?

Minha intuição (provavelmente errada) me diz que o índice com "timestamp" deveria estar em ordem crescente, pois preciso ordenar os dados por timestamp em ordem crescente .

Porém, os exemplos na documentação da escala de tempo sempre indexam as colunas de tempo em ordem decrescente. Eu não entendo muito bem o porquê.

Qual é a escolha ideal de índices aqui?

Eu tenho três tabelas, que crio com essas consultas:

create table parents(
    rowid serial primary key,
    display_name varchar,
    unique(display_name)
);

create table clients(
    rowid serial primary key,
    parent int,
    display_name varchar,
    foreign key (parent) references parents(rowid),
    unique(display_name, parent)
);

create table datapoints(
    rowid serial primary key,
    client int,
    val float8,
    foreign key (client) references clients(rowid)
);

Estou recebendo dados que contêm nome do pai, nome do cliente e algum valor. Cada vez que recebo esses dados, quero adicionar linhas à tabela de pontos de dados. Também quero adicionar linhas à tabela de clientes e pais, mas somente se os dados que recebi tiverem nomes não reconhecidos.

Por exemplo, eu poderia obter estes dados:

"pai1-cliente1-123.0"

O que eu gostaria de conseguir é processar esses dados (em uma consulta), desta forma:

Insira uma linha na tabela "pais", se necessário.

Insira uma linha na tabela "clientes" com o ID pai apropriado (obtido na etapa anterior?), se necessário.

Insira uma linha na tabela "datapoints" com o ID do cliente apropriado (obtido na etapa anterior?).

Como posso gerenciar isso com consultas? O programador em mim quer escrever:

• Insira nos pais, se necessário. Salve o id do pai apropriado em uma variável "current_parent"
• Insira nos clientes, se necessário, usando a variável "current_parent". Salve o id do cliente apropriado em uma variável "current_client".
• Insira em pontos de dados, usando a variável "current_client" do arquivo anterior. degrau.

Mas obviamente não é assim que o sql funciona, certo? E se, em vez dessas três tabelas "aninhadas" eu tiver 5 ou 10?(!)

Por favor ajude