ProHoster > Blog > Administración > Antipatróns de PostgreSQL: navegación polo rexistro

Antipatróns de PostgreSQL: navegación polo rexistro

Hoxe non haberá casos complexos e algoritmos sofisticados en SQL. Todo será moi sinxelo, ao nivel de Capitán Obvio: imos facelo ver o rexistro de eventos ordenados por tempo.

É dicir, hai un sinal na base de datos events, e ela ten un campo ts - hora exacta na que queremos mostrar estes rexistros de forma ordenada:

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

CREATE INDEX ON events(ts DESC);

Está claro que alí non teremos unha ducia de discos, polo que necesitaremos algún tipo de navegación da páxina.

#0. "Eu son o pogromista da miña nai"

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

Case non é unha broma - é raro, pero atópase na natureza. Ás veces, despois de traballar con ORM, pode ser difícil cambiar ao traballo "directo" con SQL.

Pero pasemos a problemas máis comúns e menos obvios.

#1. COMPENSACIÓN

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

De onde saíu o número 26? Este é o número aproximado de entradas para cubrir unha pantalla. Máis precisamente, 25 rexistros mostrados, máis 1, indican que hai polo menos algo máis na mostra e ten sentido seguir adiante.

Por suposto, este valor non se pode "coser" no corpo da solicitude, senón pasar a través dun parámetro. Pero neste caso, o programador PostgreSQL non poderá confiar no coñecemento de que debería haber relativamente poucos rexistros e escollerá facilmente un plan ineficaz.

E mentres na interface da aplicación, a visualización do rexistro está implementada como un cambio entre "páxinas" visuais, ninguén nota nada sospeitoso durante moito tempo. Exactamente ata o momento no que, na loita pola comodidade, UI/UX decide refacer a interface para "desprazar sen fin", é dicir, todas as entradas do rexistro están debuxadas nunha única lista que o usuario pode desprazar cara arriba e abaixo.

E así, durante a próxima proba, estás atrapado duplicación de rexistros no rexistro. Porque, porque a táboa ten un índice normal (ts), en que se basea a túa consulta?

Exactamente porque non o tivo en conta ts non é unha clave única nesta táboa. En realidade, e os seus valores non son únicos, como calquera "tempo" en condicións reais; polo tanto, o mesmo rexistro en dúas consultas adxacentes "salta" facilmente de páxina en páxina debido a unha orde final diferente no marco de ordenar o mesmo valor clave.

De feito, tamén hai un segundo problema escondido aquí, que é moito máis difícil de notar: algunhas entradas non se mostrarán en todo! Despois de todo, os rexistros "duplicados" ocuparon o lugar doutra persoa. Pódese atopar unha explicación detallada con fermosas imaxes ler aquí.

Ampliación do índice

Un programador astuto entende que a clave de índice debe ser única e o xeito máis sinxelo é expandila cun campo obviamente único, para o que PK é perfecto:

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

E a solicitude muta:

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

#2. Cambiar a "cursores"

Algún tempo despois, un DBA chega a ti e está "contenta" coas túas solicitudes cargan o servidor como o inferno coas súas regras de OFFSET, e en xeral, é hora de cambiar a navegación desde o último valor mostrado. A túa consulta cambia de novo:

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

Suspirás aliviado ata que chegou...

#3. Índices de limpeza

Porque un día o teu DBA leu artigo sobre a busca de índices ineficaces e deuse conta diso A marca de tempo "non é a última" non é boa. E vin a ti de novo, agora co pensamento de que ese índice aínda debería volver converterse (ts DESC).

Pero que facer co problema inicial de "saltar" rexistros entre páxinas?... E todo é sinxelo: cómpre seleccionar bloques cun número indefinido de rexistros!

En xeral, quen nos prohibe ler non "exactamente 26", senón "non menos de 26"? Por exemplo, para que no seguinte bloque haxa rexistros con significados evidentemente diferentes ts - entón non haberá ningún problema con "saltar" rexistros entre bloques!

Aquí tes como conseguir isto:

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;

Que está pasando aquí?

  1. O paso 25 rexistra "abaixo" e obtemos o valor de "límite". ts.
  2. Se xa non hai nada alí, entón substitúe o valor NULL por -infinity.
  3. Restamos todo o segmento de valores entre o valor recibido ts e o parámetro $1 pasou desde a interface (o "último" valor representado anterior).
  4. Se se devolve un bloque con menos de 26 rexistros, é o último.

Ou a mesma imaxe:
Antipatróns de PostgreSQL: navegación polo rexistro

Porque agora temos a mostra non ten ningún "comezo" específico, entón nada nos impide "expandir" esta solicitude na dirección oposta e implementar a carga dinámica de bloques de datos desde o "punto de referencia" en ambas direccións, tanto cara abaixo como cara arriba.

Comentario

  1. Si, neste caso accedemos ao índice dúas veces, pero todo é "puramente por índice". Polo tanto, só resultará unha subconsulta a unha exploración adicional de só índice.
  2. É bastante obvio que esta técnica só se pode usar cando tes valores ts pode cruzar só por casualidade, e non hai moitos deles. Se o teu caso típico é "un millón de rexistros ás 00:00:00.000", non deberías facelo. Quero dicir, non deberías permitir que ocorra un caso así. Pero se isto ocorre, use a opción cun índice estendido.

Fonte: www.habr.com

Engadir un comentario