Início / dba / Perguntas / 291775
Accepted
sev
Asked: 2021-05-19 04:11:08 +0800 CST

Convertendo a tabela PostgreSQL para a hipertabela TimescaleDB

  • 772

Eu tenho uma tabela PostgreSQL que estou tentando converter em uma hipertabela TimescaleDB.

A tabela tem a seguinte aparência:

CREATE TABLE public.data
(
    event_time timestamp with time zone NOT NULL,
    pair_id integer NOT NULL,
    entry_id bigint NOT NULL,
    event_data int NOT NULL,
    CONSTRAINT con1 UNIQUE (pair_id, entry_id ),
    CONSTRAINT pair_id_fkey FOREIGN KEY (pair_id)
        REFERENCES public.pairs (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

Quando tento converter esta tabela em uma hipertabela TimescaleDB usando o seguinte comando:

SELECT create_hypertable(
        'data',
        'event_time',
        chunk_time_interval => INTERVAL '1 hour',
        migrate_data => TRUE
);

Recebo o Erro:ERROR: cannot create a unique index without the column "event_time" (used in partitioning)

Pergunta 1: A partir desta postagem https://stackoverflow.com/questions/55312365/how-to-convert-a-simple-postgresql-table-to-hypertable-or-timescale-db-table-usi meu entendimento é que isso é porque eu especifiquei uma restrição exclusiva (con1) que não contém a coluna pela qual estou particionando - event_time. Isso é correto?

Pergunta 2: Como devo alterar minha tabela ou hipertabela para poder converter isso? Adicionei alguns dados sobre como pretendo usar os dados e a estrutura dos dados abaixo.

Propriedades de dados e uso:

  • Pode haver várias entradas com o mesmo event_time - essas entradas teriam entry_id's que estão em sequência
    • Isso significa que, se eu tiver 2 entradas (event_time 2021-05-18::10:16, id 105, <some_data>) e (event_time 2021-05-18::10:16, id 107, <some_data>), então a entrada com id 106 também teria event_time 2021-05-18::10:16
  • O entry_id não é gerado por mim e uso a restrição exclusiva con1 para garantir que não estou inserindo dados duplicados
  • Vou consultar os dados principalmente em event_time, por exemplo, para criar gráficos e realizar outras análises
  • Neste ponto, o banco de dados contém cerca de 4,6 bilhões de linhas, mas deve conter muito mais em breve
  • Gostaria de aproveitar a velocidade e a boa compactação do TimescaleDB
  • Eu não me importo muito com o desempenho da inserção

Soluções que tenho considerado:

  • Empacote todos os eventos que têm o mesmo timestamp em uma matriz de alguma forma e mantenha-os em uma linha. Acho que isso teria desvantagens na compactação e forneceria menos flexibilidade na consulta dos dados. Além disso, eu provavelmente acabaria tendo que descompactar os dados em cada consulta.
  • Remova a restrição exclusiva con1 - como faço para garantir que não adicionei a mesma linha duas vezes?
  • Expanda a restrição exclusiva con1 para incluir event_time - isso não diminuiria de alguma forma o desempenho e, ao mesmo tempo, abriria para o erro em que insiro acidentalmente 2 linhas com entry_id e pair_id, mas event_time diferente? (Eu duvido que isso seja uma coisa provável de acontecer embora)
postgresql timescaledb

2 respostas

  1. Best Answer

    Algumas perguntas e respostas aqui:

    1. é a restrição exclusiva (con1) que é o problema, não a restrição de chave estrangeira, mas provavelmente foi um erro de digitação.
    2. Sim, é um problema porque a coluna de particionamento de tempo não está incluída, a primeira pergunta que tenho é, o entry_id parece que pode ser um proxy para o tempo do outro sistema, você pode usar pair_id, time como sua restrição exclusiva? Haverá vários registros com diferentes entry_ids, os mesmos pair_ids e os mesmos horários? Você provavelmente poderia executar uma consulta com um count(*) agrupado por pair_id, hora de descobrir isso ... Observe que uma restrição exclusiva de três colunas pode funcionar, é uma questão de como o tempo é atribuído, é atribuído por você em tempo de gravação ou pelo sistema upstream? Se for o sistema upstream, não me preocuparia muito com duplicatas.
    3. a coisa da matriz é um pouco semelhante à compactação, mas a compactação faz algoritmos diferentes e mais eficientes do que o simples empacotamento de matriz.
    4. descobrir uma restrição exclusiva que funcione, seja em pair_id, time ou pair_id, entry_id, time provavelmente será muito mais eficiente do que tentar reduzir seus dados ao conjunto exclusivo no momento da consulta. E uma restrição exclusiva de três colunas não terá um grande impacto no desempenho. A ordenação dessas colunas deve ser escolhida com base nos padrões de consulta, que tipos de cláusulas where você costuma ter? ou seja, no id do par ou algo assim, bem como o tempo? ou apenas pelo tempo?
    5. finalmente, dado o tamanho da sua tabela, eu recomendo criar a hipertabela sem migrar dados, então crie uma tabela separada, transforme-a em uma hipertabela e faça um pequeno trabalho para migrar os dados. O migrate_data = true é realmente destinado a tabelas muito menores, ter um trabalho externo para fazer isso significa que você também pode introduzir algum paralelismo que fará com que seja muito mais rápido.
    • 3

  2. meu entendimento é que isso ocorre porque eu especifiquei uma restrição exclusiva (pair_id_fkey) que não contém a coluna pela qual estou particionando - event_time. Isso é correto?

    Sim.

    Pergunta 2: Como devo alterar minha tabela ou hipertabela para poder converter isso? Adicionei alguns dados sobre como pretendo usar os dados e a estrutura dos dados abaixo.

    Acho que você pode manter a estrutura removendo as restrições. Você pode usar avg, lastou firstcombinado com algum time_bucket .

    Empacote todos os eventos que têm o mesmo timestamp em uma matriz de alguma forma e mantenha-os em uma linha.

    É o que a compressão de escala de tempo está fazendo nos bastidores, verifique isso .

    Remova a restrição exclusiva con1 - como faço para garantir que não adicionei a mesma linha duas vezes?

    Você provavelmente pode usar lastou firstpara evitar duplicatas como mencionei antes.

    Expanda a restrição exclusiva con1 para incluir event_time - isso não diminuiria de alguma forma o desempenho e, ao mesmo tempo, abriria para o erro em que insiro acidentalmente 2 linhas com entry_id e pair_id, mas event_time diferente? (Eu duvido que isso seja uma coisa provável de acontecer embora)

    É mais rápido manter o firstou lastda sua consulta e evitar a restrição. Mas se o desempenho não for um problema, acho que funcionará. Você também pode combinar time, entry_id, and pair_id, pois as restrições aceitam várias colunas.

    • 2

relate perguntas