Thomas's questions

Digamos que eu tenha uma tabela com um registro de data e hora ( ts) e um valor ( a).

Com alguns dados de amostra:

ts, a
1, 10
2, 30
3, 10
4, 20
5, 40
6, 10
7, 20
8, 30
etc

Gostaria de agrupar linhas, na ordem ts, somando ae gerando uma linha onde a soma de aseja > 50.

Neste caso, a saída deve ser dois registros de data e hora (primeira e última linha):

1, 3  -- 10 + 30 + 10
4, 5  -- 20 + 40
6, 8  -- 10 + 20 + 30

Mas não tenho a mínima ideia de como proceder. Todas as tentativas que fiz com o Copilot produziram código complexo que nunca termina.

Tenho uma tabela na qual tenho consultas que retornam dados de um intervalo de tempo. As linhas são esparsas e os eventos podem ter dias de intervalo.

Estou procurando uma maneira de obter os dados do intervalo + adicionar a linha antes do início do intervalo (que pode ser em qualquer carimbo de data / hora menor que o início).

Por exemplo:

2020-01-01 xxxxxxx
2020-01-12 xxxxxxx
2020-02-10 xxxxxxx
2020-02-11 xxxxxxx
2020-02-15 xxxxxxx

Se eu consultar de 2020-02-08para 2020-02-14, obterei:

2020-02-10 xxxxxxx
2020-02-11 xxxxxxx

Mas gostaria de obter:

2020-01-12 xxxxxxx
2020-02-10 xxxxxxx
2020-02-11 xxxxxxx

Como isso pode ser feito?

Uma imagem resume tudo:

A função faz parte do esquema público, mas não foi encontrada; acontece se eu colocar 'público'. ou não antes do nome da função.

Estou usando o DataGrip e o autocomplete está ciente da função:

Como posso chamar essa função?

Tenho o seguinte objeto:

CREATE TABLE IF NOT EXISTS {schemaName}.{tableName}
(
    id                   VARCHAR      NOT NULL,
    ticker               VARCHAR(16)  NOT NULL,
    interval             VARCHAR(3)   NOT NULL,
    ts                   TIMESTAMP    NOT NULL,
    ...
    UNIQUE (ticker, interval, ts)
);

Eu gostaria de fazer uma consulta que retornaria linhas como esta:

SELECT * FROM analysis
WHERE ticker = 'BTCUSDT' AND ts BETWEEN '2020-1-1' AND '2020-1-30'
ORDER BY ts;

mas, para cada linha, adiciona uma coluna que contém o timestamp da próxima linha (classificada por ts).

Como tenho apenas conhecimento básico de SQL, usei o ChatGPT para gerar uma solução, mas é incrivelmente lento (em minutos x milissegundos)

SELECT t1.ts as ts_start, t2.ts as ts_end, t1.ticker, t1.interval, ...
FROM analysis t1
JOIN analysis t2
ON t1.ticker = t2.ticker AND t1.interval = t2.interval AND t1.ts < t2.ts
WHERE t1.ticker = 'BTCUSDT' AND t1.ts BETWEEN '2020-1-1' AND '2020-1-30'
ORDER BY t1.ts;

Qual seria a melhor maneira de fazer isso acontecer?

Estou tentando descobrir a maneira mais rápida de:

• INSERIR uma linha se ela não existir
• SUBSTITUIR (todas as colunas de uma vez) a linha se existir

Eu pensei em DELETE+ INSERT, mas isso parece 2 scans e se eu fizer um INSERT+ ON CONFLICT, recebo uma consulta enorme onde tudo tem que ser duplicado.

Existe um método geralmente reconhecido para fazer isso?

Isso NÃO compila:

SELECT add_compression_policy(
    hypertable => 'exchange.candles',
    compress_after => INTERVAL ((SELECT now()::DATE::timestamp - (SELECT min(last_ts) FROM exchange.capture_tracker))),
    if_not_exists => TRUE);

A questão parece ser a SELECTseguinte INTERVAL.

Mas isso compila:

WITH delay AS (SELECT now()::DATE::timestamp - (SELECT min(last_ts) FROM exchange.capture_tracker) myinterval)
SELECT add_compression_policy(
    hypertable => 'exchange.candles',
    compress_after => myinterval,
    if_not_exists => TRUE) FROM delay;

Meu colega e eu não consigo entender o porquê.
Alguém pode explicar?

Tenho uma tabela com esta estrutura:

ticker         VARCHAR                     NOT NULL,
interval       VARCHAR                     NOT NULL,
ts             TIMESTAMP WITHOUT TIME ZONE NOT NULL,
price          FLOAT8                      NOT NULL,
UNIQUE (ticker, interval, ts)

Existem 40 tickers (que eventualmente serão estendidos para cerca de 130) e 8 intervalos. Novas linhas (40 * 8) são adicionadas a cada 10 segundos como uma cópia em massa, o que representa 115 mil linhas/hora. Eles são escritos uma vez e nunca modificados.

As operações de leitura são sempre feitas para intervalos de tempo bastante grandes (vários dias) e solicitam um ticker e 3 intervalos para ele, usando isto:

SELECT * FROM exchange.{tableName}
WHERE ticker = '{ticker}' AND \"interval\" IN ({intervalsText})
AND ts >= '{fromTime.Format}' AND ts < '{toTime.Format}'
ORDER BY ts

Minha pergunta aqui é se seria benéfico agrupar todos os intervalos em uma única linha por ticker. Assim:

ticker           VARCHAR                     NOT NULL,
ts               TIMESTAMP WITHOUT TIME ZONE NOT NULL,
price_interval0  FLOAT8                      NOT NULL,
price_interval1  FLOAT8                      NOT NULL,
...
price_interval7  FLOAT8                      NOT NULL,
UNIQUE (ticker, ts)

Isso significa 8x menos linhas na tabela, um índice menor, mas cada consulta pode precisar carregar a linha inteira para retornar apenas 3 valores e descartar 5.

Eu não sei como o Postgres organiza os dados internamente e se uma linha inteira seria recuperada de uma só vez (que é minha suposição) e então partes dela seriam extraídas, etc ...

Qualquer conselho seria muito apreciado.

Eu tenho isso nos logs de uma instância do postgres:

Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 06:20:02.277 UTC [78602] FATAL:  password authentication failed for user "postgres"
2021-11-20 06:20:02.277 UTC [78602] DETAIL:  Role "postgres" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 06:41:05.211 UTC [79412] FATAL:  password authentication failed for user "postgres"
2021-11-20 06:41:05.211 UTC [79412] DETAIL:  Role "postgres" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 06:41:45.448 UTC [79517] FATAL:  password authentication failed for user "pgsql"
2021-11-20 06:41:45.448 UTC [79517] DETAIL:  Role "pgsql" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 07:01:53.432 UTC [80321] FATAL:  password authentication failed for user "postgres"
2021-11-20 07:01:53.432 UTC [80321] DETAIL:  Role "postgres" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 07:22:44.231 UTC [81197] FATAL:  password authentication failed for user "postgres"
2021-11-20 07:22:44.231 UTC [81197] DETAIL:  Role "postgres" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 07:25:30.380 UTC [81317] FATAL:  password authentication failed for user "pgsql"
2021-11-20 07:25:30.380 UTC [81317] DETAIL:  Role "pgsql" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 07:43:48.611 UTC [82115] FATAL:  password authentication failed for user "postgres"
2021-11-20 07:43:48.611 UTC [82115] DETAIL:  Role "postgres" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 07:47:12.673 UTC [82233] FATAL:  password authentication failed for user "pgsql"
2021-11-20 07:47:12.673 UTC [82233] DETAIL:  Role "pgsql" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 08:04:33.369 UTC [82925] FATAL:  password authentication failed for user "postgres"
2021-11-20 08:04:33.369 UTC [82925] DETAIL:  Role "postgres" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 08:09:09.023 UTC [83149] FATAL:  password authentication failed for user "pgsql"
2021-11-20 08:09:09.023 UTC [83149] DETAIL:  Role "pgsql" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 08:25:46.146 UTC [83736] FATAL:  password authentication failed for user "postgres"
2021-11-20 08:25:46.146 UTC [83736] DETAIL:  Role "postgres" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
2021-11-20 08:30:54.629 UTC [83963] FATAL:  password authentication failed for user "pgadmin"
2021-11-20 08:30:54.629 UTC [83963] DETAIL:  Role "pgadmin" does not exist.
Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"

Essa instância é usada para testes e contém dados sem valor, portanto, não é segura e possui uma porta aberta pública para facilitar o teste.

O que acho estranho, se for um ataque, é que os timestamps são muito espaçados: 6:20, 6:41, 7:01, 7:22, 7:25, 7:43, etc. solicitações em um curto período de tempo, mas há muita lacuna entre as conexões

O que significa a mensagem "conexão correspondida"?

para referência, quase toda minha experiência de banco de dados é com mongodb e este é meu primeiro projeto postgres.

Estou armazenando atualizações de dados de pools de criptomoedas de finanças descentralizadas.

A estrutura de dados que estou explorando é muito simples. Existem fichas:

type Token =
    {
        Address:    string     // primary key
        Decimals:   int
        Symbol:     string
    }

Esses dados NUNCA serão alterados, mas são referenciados em todos os lugares; cerca de 2k entradas

type LiquidityToken =
    {
        Address:        string    // primary key
        Symbol:         string
        Token0Address:  string    // matches the address from a token above
        Token1Address:  string    // matches the address from a token above
        Timestamp:      DateTime  // changes at every update
        Token0Amount:   decimal   // changes at every update
        Token1Amount:   decimal   // changes at every update
    }

isso é atualizado a cada 5 minutos, no entanto, SOMENTE Token0Amount e Token1Amount mudam e eu quero manter um histórico.

Eu tenho aproximadamente 500 desses objetos com uma nova atualização a cada 5 minutos, então 140k novas entradas por dia.

então a primeira coisa que eu queria fazer é fazer uma junção entre os campos Token0/1Address e o registro Token, mas como o Token apenas adiciona um int e uma string curta (até 32 caracteres, mas geralmente < 8 caracteres), eu queria saber é que isso está economizando muito (não sei quanto espaço a junção está ocupando).

então eu queria saber se vale a pena dividir o objeto em duas partes:

type LiquidityToken =
    {
        Address:        string    // primary key
        Symbol:         string
        Token0Address:  string    // matches the address from a token above
        Token1Address:  string    // matches the address from a token above
    }

e

type LiquidityTokenUpdate =
    {
        Address:        string    // matches the address of the liquidity token 
        Timestamp:      DateTime  // changes at every update
        Token0Amount:   decimal   // changes at every update
        Token1Amount:   decimal   // changes at every update
    }

e apenas replicar este objeto. Mas agora uma consulta pode puxar a atualização, que puxa o token de liquidez que deve puxar 2 objetos de token.

Como isso é para uma interface pública do GraphQL, pode haver alguns padrões de uso que aparecerão. Não posso prever um tipo específico de consultas porque isso é algo que quero tornar público para a comunidade assim que estiver pronto.

Eu entendo que o cache é meu amigo aqui e como os dados estáticos são pequenos cabe tudo na RAM, mas no geral faria sentido tentar economizar espaço (cada registro não é muito grande) e adicionar o custo das junções ou pesquisa no cache local, ou é melhor apenas salvar tudo a cada atualização, já que o disco é barato?

Por favor, coloque no contexto que isso não é algo que eu tenha experiência; Eu li sobre junções, normalização vs desnormalização de tabelas, etc, mas não tenho experiência prática com isso. Eu lidei com conjuntos de dados muito grandes, mas em um contexto não SQL e esta é minha primeira vez aqui, então espero que a pergunta forneça as informações necessárias: D