Início / dba / Perguntas / 311171
Accepted
Thomas
Asked: 2022-04-21 12:15:52 +0800 CST

Mais colunas versus mais linhas para otimizar o armazenamento e o desempenho?

  • 772

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.

database-design postgresql

1 respostas

  1. Best Answer

    Com o tempo, isso vai ser um monte de linhas!

    Fundamentos

    Sim, armazenar 8 float8em uma única linha superará 8 linhas com 1 float8cada uma por um tiro longo, em armazenamento e desempenho.
    Mas você pode fazer mais...

    Design de mesa

    Para otimizar o armazenamento e o desempenho :

    CREATE TABLE ticker (
  ticker_id smallint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, ticker text NOT NULL UNIQUE   
);

CREATE TABLE tbl (
  the_date        date     NOT NULL  -- columns in this order!
, timeslot        smallint NOT NULL
, ticker_id       smallint NOT NULL REFERENCES ticker
, price_interval0 int      NOT NULL
, price_interval1 int      NOT NULL
 ...
, price_interval7 int      NOT NULL
CONSTRAINT tbl_pkey PRIMARY KEY (ticker_id, the_date, timeslot);  -- columns in this order!
);

    db<>fiddle aqui - incluindo todos

    Explicação e auxiliares

    Uma entrada a cada 10 segundos chega a 6*60*24 = 8640 intervalos de tempo distintos por dia. A smallintcom seu intervalo de -2^15 a 2^15 pode facilmente manter isso.

    É claro que não armazenamos o nome completo do ticker todas as vezes. Uma coluna FK smallint cobre facilmente 40 - 130 tickers distintos e faz referência a uma tickertabela. Normalmente melhor para armazenamento e desempenho:

    O dia como date(4 bytes), um intervalo de tempo smallint(2 bytes) e um smallintpara o ID do ticker, dispostos nesta sequência ocupam 8 bytes sem preenchimento de alinhamento!
    Infelizmente, não podemos otimizar o índice PK perfeitamente ao mesmo tempo e incorrer em 8 bytes de preenchimento de alinhamento. A única mancha na otimização de armazenamento.

    Por conveniência, você pode adicionar um VIEWpara obter dados bonitos:

    CREATE VIEW tbl_pretty AS
SELECT ti.ticker, the_date + interval '10 sec' * timeslot AS ts, price_interval0, price_interval1
--   , price_interval2, ...
FROM   tbl
JOIN   ticker ti USING (ticker_id);

    Como você pode ver, essa expressão produz seu carimbo de data/hora original:

    the_date + interval '10 sec' * timeslot

    A conversão reversa será usada na consulta abaixo:

    trunc(EXTRACT(EPOCH FROM time '12:34:56'))::int / 10)

    Valores monetários como um "preço" não devem ser armazenados como número de ponto flutuante. Isso é uma arma de pé carregada. Usar numeric. Ou, como estamos otimizando para armazenamento e desempenho, uma integerrepresentação de Cents normalmente funciona melhor. E isso é apenas 4 bytes em vez de 8 bytes para float8. ( numericdepende do comprimento real, normalmente maior). Ver:

    Armazenar

    Isso vai ocupar:

    • (24(cabeçalho da tupla) + 4(identificador do item) + 4 + 2 + 2 + 4*8 + 4) = 72 bytes por linha da tabela - sem preenchimento
      (Sua ideia original para a linha composta ocuparia (24 + 4 + (min. 8) + 8 + 8*8) = 108 bytes ou mais por linha.)
    • (8(cabeçalho de índice) + 2 + 2(preenchimento) + 4 + 2 + 6(preenchimento)) = 24 bytes por entrada de índice PK

    Além de sobrecarga mínima por página de dados de 8kb e nenhuma sobrecarga para tuplas mortas (nunca atualizadas).

    Detalhes:

    O índice PK seria menor (16 em vez de 24 bytes por tupla) se pudéssemos fazê-lo em (the_date, timeslot, ticker_id). Mas precisamos dele para (ticker_id, the_date, timeslot)dar suporte à sua consulta de maneira ideal. Igualdade antes do intervalo . Ver:

    Consulta

    Sua consulta se torna:

    SELECT price_interval3, price_interval7  -- just the intervals you need
FROM   tbl
WHERE  ticker_id = (SELECT ticker_id FROM ticker WHERE ticker = 'ticker_3')
AND    (the_date, timeslot) >= (date '2022-04-20', trunc(EXTRACT(EPOCH FROM time '00:00:00'))::int / 10)
AND    (the_date, timeslot) <  (date '2022-04-20', trunc(EXTRACT(EPOCH FROM time '00:01:00'))::int / 10)
ORDER  BY the_date, timeslot;

    Ou curta:

    SELECT *
FROM   tbl
WHERE  ticker_id = 3
AND    (the_date, timeslot) >= ('2022-04-20', 0)
AND    (the_date, timeslot) <  ('2022-04-20', 6)
ORDER  BY the_date, timeslot;

    Observe o uso da comparação de valores ROW! Ver:

    atuação

    Isso é perfeitamente suportado pelo índice PK no (ticker_id, the_date, timeslot). Nenhum outro índice necessário. Você recebe um plano como:

    Index Scan using tbl_pkey on tbl  (cost=0.27..8.29 rows=1 width=16)
  Index Cond: ((ticker_id = 3) AND (ROW(the_date, timeslot) >= ROW('2022-04-20'::date, 0)) AND (ROW(the_date, timeslot) < ROW('2022-04-20'::date, 6)))
    • 2

relate perguntas