📝 🗄️ 📴 Antipatterns PostgreSQL: Navegação no Registro 👩🏾‍⚕️ 🧑🏽‍🤝‍🧑🏽 ❎

Hoje não haverá casos complicados e algoritmos SQL sofisticados. Tudo será muito simples, no nível do Capitão. Evidência - fazemos uma revisão do registro de eventos com a classificação por tempo.

Ou seja, existe uma placa na base eventse seu campo tsé exatamente o horário em que queremos exibir esses registros de maneira 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 entradas lá, portanto, precisaremos de algum tipo de navegação na página .

# 0 "Eu sou um pogrommist na 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 é brincadeira - raramente, mas encontrada na natureza. Às vezes, depois de trabalhar com o ORM, pode ser difícil mudar para um trabalho "direto" com o SQL.

Mas vamos para problemas mais comuns e menos óbvios.

# 1 DESLOCAMENTO

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 -   , $1 -

26? . , 25 , 1, , - .

, «» , . PostgreSQL , , — .

E enquanto na interface do aplicativo a visualização do registro é implementada como alternando entre "páginas" visuais, ninguém por um longo tempo percebe algo suspeito. Exatamente até o momento em que, na luta pela conveniência, a UI / UX não decide refazer a interface para “rolagem sem fim” - ou seja, todas as entradas do registro são desenhadas em uma única lista que o usuário pode girar para cima e para baixo.

E agora, durante o próximo teste, você é flagrado duplicando entradas no registro. Por que, porque a tabela possui um índice normal (ts)no qual sua consulta se baseia?

Exatamente porque você não considerou o que tsnão é uma chave exclusiva nesta tabela. Na verdade, seus significados não são únicos, como qualquer "tempo" em condições reais - é por isso que o mesmo registro em duas consultas vizinhas "salta" facilmente de uma página para outra devido a um pedido final diferente, como parte da classificação do mesmo valor-chave.

De fato, o segundo problema também está oculto aqui, o que é muito mais difícil de notar - algumas entradas não serão mostradas ! Afinal, os registros "duplicados" substituíram alguém. Uma explicação detalhada com belas imagens pode ser encontrada aqui .

Expandindo o índice

O desenvolvedor astuto entende que você precisa tornar a chave de índice exclusiva e a maneira mais fácil é expandi-la com um campo deliberadamente exclusivo, para o qual o PK é perfeito:

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

E a solicitação muda:

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

# 2 Transição para "cursores"

Algum tempo depois, o DBA chega até você e está "feliz" por seus pedidos estarem carregando infernalmente o servidor com seus OFFSETs puxados a cavalo e, em geral, é hora de mudar para a navegação a partir do último valor mostrado . O seu pedido muda novamente:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) --      
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Você deu um suspiro de alívio antes que chegasse ...

# 3 Limpeza do Índice

Porque um dia seu DBA leu um artigo sobre como encontrar índices ineficientes e percebeu que o carimbo de data e hora do “último mas não menos importante” não é bom . E ele veio até você novamente - agora com o pensamento de que esse índice ainda deve se transformar em (ts DESC).

Mas o que fazer com o problema inicial de “pular” registros entre páginas? .. E tudo é simples - você precisa escolher blocos com um número ilimitado 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 valores obviamente diferentests - não haverá problemas em "pular" entre os blocos!

Veja como fazê-lo:

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?

Diminuímos 25 registros e obtemos o valor "limite" ts.
Se já não houver nada lá, substitua o valor NULL por -infinity.
Subtraia todo o segmento de valores entre o valor recebido tse o parâmetro $ 1 passado da interface (o “último” valor anterior).
Se um bloco retornou com menos de 26 entradas, é o último.

Ou a mesma imagem:

Como agora nossa amostra não possui um "começo" definido , nada nos impede de "reverter" essa solicitação na direção oposta e implementar o carregamento dinâmico de blocos de dados a partir do "ponto de referência" nas duas direções - tanto para baixo quanto para cima.

Comente

, , « ». Index Only Scan.
, , ts , . — « 00:00:00.000», . , . , .