🥇Antipadrões do PostgreSQL: Navegação no Registro

Hoje não haverá casos complexos e algoritmos sofisticados em SQL. Tudo será muito simples, ao nível do Capitão Óbvio - vamos lá visualizando o registro de eventos classificados por tempo.

Ou seja, há um sinal no banco de dados events, e ela tem um campo ts - exatamente o momento em que queremos exibir esses registros de forma ordenada:

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

É claro que não teremos uma dúzia de registros lá, então precisaremos de algum tipo de navegação na página.

#0. “Sou o pogromista da minha mãe”

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

Quase não é uma piada - é raro, mas é encontrado na natureza. Às vezes, depois de trabalhar com ORM, pode ser difícil mudar para o trabalho “direto” com SQL.

Mas vamos passar para problemas mais comuns e menos óbvios.

#1. DESVIO

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записей на странице, $1 - начало страницы

De onde veio o número 26? Este é o número aproximado de entradas para preencher uma tela. Mais precisamente, 25 registros exibidos, mais 1, sinalizando que há pelo menos algo mais na amostra e que faz sentido seguir em frente.
É claro que esse valor não pode ser “costurado” no corpo da requisição, mas sim passado através de um parâmetro. Mas, neste caso, o escalonador PostgreSQL não poderá confiar no conhecimento de que deve haver relativamente poucos registros - e escolherá facilmente um plano ineficaz.

E enquanto na interface do aplicativo a visualização do registro é implementada como uma alternância entre “páginas” visuais, ninguém percebe nada suspeito por muito tempo. Exatamente até o momento em que, na luta pela conveniência, UI/UX decide refazer a interface para “rolagem infinita” – ou seja, todas as entradas do registro são desenhadas em uma única lista que o usuário pode rolar para cima e para baixo.

E assim, durante o próximo teste, você será pego duplicação de registros no registro. Por que, porque a tabela tem um índice normal (ts), em que sua consulta se baseia?

Exatamente porque você não levou em conta isso ts não é uma chave única nesta tabela. Na verdade, e seus valores não são únicos, como qualquer “tempo” em condições reais - portanto, o mesmo registro em duas consultas adjacentes “salta” facilmente de página para página devido a uma ordem final diferente na estrutura de classificação do mesmo valor-chave.

Na verdade, há também um segundo problema escondido aqui, que é muito mais difícil de perceber - algumas entradas não serão mostradas de forma alguma! Afinal, os registros “duplicados” ocuparam o lugar de outra pessoa. Uma explicação detalhada com belas fotos pode ser encontrada leia aqui.

Expandindo o índice

Um desenvolvedor astuto entende que a chave do índice precisa ser única, e a maneira mais fácil é expandi-la com um campo obviamente único, para o qual o PK é perfeito:

CREATE UNIQUE INDEX ON events(ts DESC, id DESC);

E a solicitação sofre mutação:

SELECT
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

#2. Mudar para “cursores”

Algum tempo depois, um DBA chega até você e fica “satisfeito” com suas solicitações. eles carregam o servidor como o inferno com suas regras OFFSETe, em geral, é hora de mudar para navegação a partir do último valor mostrado. Sua consulta sofre mutação novamente:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- последние полученные на предыдущем шаге значения
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Você deu um suspiro de alívio até que chegou...

#3. Índices de limpeza

Porque um dia seu DBA leu artigo sobre como encontrar índices ineficazes e percebi que O carimbo de data/hora “não é o mais recente” não é bom. E voltei até você - agora com o pensamento de que esse índice ainda deveria voltar para (ts DESC).

Mas o que fazer com o problema inicial de “saltar” registros entre páginas?.. E tudo é simples - você precisa selecionar blocos com um número não fixo de registros!

Em geral, quem nos proíbe de ler não “exatamente 26”, mas “não menos que 26”? Por exemplo, para que no próximo bloco haja registros com significados obviamente diferentes ts - então não haverá problema em “saltar” registros entre blocos!

Veja como conseguir isso:

SELECT
  ...
WHERE
  ts < $1 AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

O que está acontecendo aqui?

Descemos 25 registros e obtemos o valor do “limite” ts.
Se ainda não houver nada, substitua o valor NULL por -infinity.
Subtraímos todo o segmento de valores entre o valor recebido ts e o parâmetro $1 passado da interface (o “último” valor renderizado anterior).
Se for retornado um bloco com menos de 26 registros, ele será o último.

Ou a mesma imagem:

Porque agora temos a amostra não tem nenhum “início” específico, então nada nos impede de “expandir” esta solicitação na direção oposta e implementar o carregamento dinâmico de blocos de dados do “ponto de referência” em ambas as direções - tanto para baixo quanto para cima.

Nota

Sim, neste caso acessamos o índice duas vezes, mas tudo é “puramente por índice”. Portanto, uma subconsulta resultará apenas em para uma varredura adicional somente de índice.
É bastante óbvio que esta técnica só pode ser usada quando você tem valores ts só pode cruzar por acaso, e não há muitos deles. Se o seu caso típico é “um milhão de registros às 00:00:00.000”, você não deveria fazer isso. Quero dizer, você não deveria permitir que tal caso acontecesse. Mas se isso acontecer, use a opção com índice estendido.

Fonte: habr.com

Antipadrões PostgreSQL: navegando no registro