🥇Antipatrones PostgreSQL: navegación del registro

Hoy en día no habrá casos complejos ni algoritmos sofisticados en SQL. Todo será muy sencillo, al nivel del Capitán Obvio: hagámoslo. viendo el registro de eventos ordenados por tiempo.

Es decir, hay un cartel en la base de datos. eventsy ella tiene un campo ts - exactamente la hora a la que queremos mostrar estos registros de forma 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 registros allí, por lo que necesitaremos algún tipo de navegación de página.

#0. “Soy el pogromista 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 una broma; es raro, pero se encuentra en la naturaleza. A veces, después de trabajar con ORM, puede resultar difícil cambiar al 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 - начало страницы

¿De dónde viene el número 26? Este es el número aproximado de entradas para llenar una pantalla. Más precisamente, se muestran 25 registros, más 1, lo que indica que hay al menos algo más en la muestra y que tiene sentido seguir adelante.

Por supuesto, este valor no se puede "coser" en el cuerpo de la solicitud, sino que se puede pasar a través de un parámetro. Pero en este caso, el programador de PostgreSQL no podrá confiar en el conocimiento de que debería haber relativamente pocos registros y elegirá fácilmente un plan ineficaz.

Y aunque en la interfaz de la aplicación la visualización del registro se implementa como un cambio entre "páginas" visuales, nadie nota nada sospechoso durante mucho tiempo. Exactamente hasta el momento en que, en la lucha por la comodidad, UI/UX decide rehacer la interfaz para "desplazamiento sin fin", es decir, todas las entradas del registro se dibujan en una única lista en la que el usuario puede desplazarse hacia arriba y hacia abajo.

Y así, durante la próxima prueba, te atraparán. duplicación de registros en el registro. Por qué, porque la tabla tiene un índice normal. (ts), ¿en qué se basa su consulta?

Exactamente porque no tomaste en cuenta eso. ts no es una clave única en esta tabla. En realidad, y sus valores no son únicos, como cualquier "tiempo" en condiciones reales; por lo tanto, el mismo registro en dos consultas adyacentes "salta" fácilmente de una página a otra debido a un orden final diferente en el marco de la clasificación del mismo valor clave.

De hecho, aquí también se esconde un segundo problema, que es mucho más difícil de notar: algunas entradas no se mostrarán ¡en absoluto! Después de todo, los registros "duplicados" ocuparon el lugar de otra persona. Puede encontrar una explicación detallada con bellas imágenes. leer aquí.

Ampliando el índice

Un desarrollador astuto entiende que la clave de índice debe ser única, y la forma más fácil es expandirla con un campo obviamente único, para lo 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. Cambiar a "cursores"

Algún tiempo después, un DBA se acerca a usted y está "contento" de que sus solicitudes cargan el servidor como el infierno con sus reglas OFFSETy, en general, es hora de cambiar a navegación desde el último valor mostrado. Su consulta vuelve a mutar:

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

Respiraste aliviado hasta que llegó...

#3. Índices de limpieza

Porque un día tu DBA leyó artículo sobre cómo encontrar índices ineficaces y me di cuenta de que La marca de tiempo “no es la última” no es buena. Y volví a verte, ahora con el pensamiento de que ese índice debería volver a convertirse en (ts DESC).

Pero, ¿qué hacer con el problema inicial de “saltar” registros entre páginas?... Y todo es simple: ¡debes seleccionar bloques con un número no fijo de registros!

En general, ¿quién nos prohíbe leer no “exactamente 26”, sino “al menos 26”? Por ejemplo, para que en el siguiente bloque haya registros con significados obviamente diferentes ts - ¡Entonces no habrá problemas con "saltar" registros entre bloques!

A continuación se explica cómo lograrlo:

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

Bajamos 25 registros y obtenemos el valor "límite" ts.
Si ya no hay nada allí, reemplace el valor NULL con -infinity.
Restamos todo el segmento de valores entre el valor recibido. ts y el parámetro $1 pasado desde la interfaz (el “último” valor renderizado anterior).
Si se devuelve un bloque con menos de 26 registros, es el último.

O la misma imagen:

porque ahora tenemos la muestra no tiene ningún “comienzo” específico, entonces nada nos impide "expandir" esta solicitud en la dirección opuesta e implementar la carga dinámica de bloques de datos desde el "punto de referencia" en ambas direcciones, tanto hacia abajo como hacia arriba.

Nota

Sí, en este caso accedemos dos veces al índice, pero todo es “puramente por índice”. Por lo tanto, una subconsulta sólo dará como resultado a un escaneo adicional de solo índice.
Es bastante obvio que esta técnica sólo se puede utilizar cuando se tienen valores ts sólo puede cruzar por casualidad, y pocos de ellos. Si tu caso típico es “un millón de registros a las 00:00:00.000”, no deberías hacer esto. Quiero decir, no deberías permitir que ocurra un caso así. Pero si esto sucede, utilice la opción con índice extendido.

Fuente: habr.com

Antipatrones de PostgreSQL: Navegando por el Registro