🏣 ⛷️ 🤚🏻 Antipatterns PostgreSQL: Navegación del registro ▪️ 👊 😕

Hoy no habrá casos complicados y algoritmos sofisticados de SQL. Todo será muy simple, a nivel de Capitán. Evidencia: hacemos una revisión del registro de eventos con clasificación por tiempo.

Es decir, hay una placa en la base events, y su campo tses exactamente el mismo momento en que queremos mostrar estos registros de manera ordenada:

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

CREATE INDEX ON events(ts DESC);

Está claro que no tendremos una docena de entradas allí, por lo que necesitaremos algún tipo de navegación de página .

# 0 "Soy un pogrommista de mi madre"

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

Casi no es broma, rara vez, pero se encuentra en la naturaleza. A veces, después de trabajar con ORM, puede ser difícil cambiar a un trabajo "directo" con SQL.

Pero pasemos a problemas más comunes y menos obvios.

# 1 COMPENSAR

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

26? . , 25 , 1, , - .

, «» , . PostgreSQL , , — .

Y mientras se visualiza el registro en la interfaz de la aplicación se implementa como un cambio entre "páginas" visuales, nadie por mucho tiempo nota nada sospechoso. Exactamente hasta el momento en que, en la lucha por la conveniencia, UI / UX no decide rehacer la interfaz a "desplazamiento sin fin", es decir, todas las entradas del registro se dibujan en una sola lista que el usuario puede girar hacia arriba y hacia abajo.

Y ahora, durante la próxima prueba, lo atrapan duplicando entradas en el registro. ¿Por qué, porque la tabla tiene un índice normal (ts)en el que se basa su consulta?

Exactamente porque no consideró lo que tsno es una clave única en esta tabla. En realidad, sus significados no son únicos., como cualquier "tiempo" en condiciones reales, es por eso que el mismo registro en dos consultas vecinas "salta" fácilmente de una página a otra debido a un orden final diferente como parte de la clasificación del mismo valor clave.

De hecho, el segundo problema también está oculto aquí, lo cual es mucho más difícil de notar: ¡ algunas entradas no se mostrarán en absoluto! Después de todo, los registros "duplicados" tomaron el lugar de alguien. Una explicación detallada con bellas imágenes se puede encontrar aquí .

Expandiendo el Índice

El desarrollador astuto comprende que debe hacer que la clave de índice sea única, y la forma más fácil es expandirla con un campo deliberadamente único, para el cual PK es perfecto:

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

Y la solicitud muta:

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

# 2 Transición a "cursores"

Algún tiempo después, el DBA llega a usted y está "feliz" de que sus solicitudes estén cargando infernalmente el servidor con sus DESPLAZAMIENTOS , y en general, es hora de cambiar a la navegación desde el último valor mostrado . Su solicitud vuelve a mutar:

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

Soltaste un suspiro de alivio antes de que llegara ...

# 3 Limpieza de índice

Porque un día su DBA leyó un artículo sobre la búsqueda de índices ineficientes y se dio cuenta de que la marca de tiempo "último pero no menos importante" no es buena . Y volvió a ti, ahora con la idea de que este índice debería volver a convertirse (ts DESC).

Pero, ¿qué hacer con el problema inicial de "saltar" registros entre páginas? ... Y todo es simple: ¡debe elegir bloques con un número ilimitado de registros!

En general, ¿quién nos prohíbe leer no "exactamente 26", sino "no menos de 26"? Por ejemplo, para que en el siguiente bloque haya registros con valores obviamente diferentests , ¡entonces no habrá problemas con el "salto" entre los bloques!

Aquí se explica cómo hacerlo:

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 esta pasando aqui?

Renunciamos a 25 registros y obtenemos el valor de "límite" ts.
Si ya no hay nada allí, reemplace el valor NULL con -infinity.
Reste todo el segmento de valores entre el valor recibido tsy el parámetro $ 1 pasado de la interfaz (el "último" valor extraído anterior).
Si un bloque regresó con menos de 26 entradas, es el último.

O la misma imagen:

Como ahora nuestra muestra no tiene ningún "comienzo" definido , nada nos impide "revertir" esta consulta en la dirección opuesta e implementar una carga dinámica de bloques de datos desde el "punto de referencia" en ambas direcciones, tanto hacia abajo como hacia arriba.

Comentario

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