🥇PostgreSQL Antipatterns: navigazione nel registro

Oggi non ci saranno casi complessi o algoritmi complicati in SQL. Tutto sarà molto semplice, a livello di Capitano Ovvio — facciamo la visualizzazione del registro eventi con ordinamento per data e ora.

Cioè, abbiamo una tabella nel database events, e ha un campo ts — proprio il tempo in base al quale vogliamo mostrare questi record in modo ordinato:

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

CREATE INDEX ON events(ts DESC);

È chiaro che avremo più di una dozzina di record, quindi avremo bisogno in qualche modo di navigazione paginata.

#0. «Я у мамы погроммист»

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

Nemmeno uno scherzo — è raro, ma può succedere. A volte, dopo aver lavorato con ORM, è difficile tornare a lavorare "direttamente" con SQL.

Ma passiamo a problemi più comuni e meno ovvi.

#1. OFFSET

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

Da dove viene questo numero 26? È la quantità approssimativa di record per riempire un'interfaccia. Più precisamente, 25 record visualizzati, più 1 che segnala che ci sono altri record da mostrare.
Naturalmente, questo valore può essere passato come parametro e non "incapsulato" nella query. Ma in questo caso, il planner di PostgreSQL non avrà la conoscenza che ci saranno relativamente pochi record — e potrebbe scegliere un piano inefficace.

E finché nell'interfaccia dell'applicazione la visualizzazione del registro è implementata come un passaggio tra "pagine" visive, nessuno noterà nulla di strano. Fino al momento in cui nella lotta per un'interfaccia utenti comoda non si decide di cambiare all'interfaccia "scroll infinito" — ovvero, tutti i record del registro sono disegnati come un'unica lista che l'utente può scorrere su e giù.

E così, durante i successivi test, qualcuno si accorge di record duplicati nel registro. Perché, dato che sulla tabella c'è un indice corretto (ts), su cui si basa la tua query?

Esattamente perché non hai considerato che ts non è una chiave unica in questa tabella. In effetti, i valori non sono unici, come per ogni "tempo" nelle condizioni reali — quindi lo stesso record in due query consecutive può "saltare" da una pagina all'altra a causa di un altro ordine finale nella sort di valori identici della chiave.

In realtà, qui c'è anche un secondo problema, molto più difficile da notare — alcuni record non verranno mostrati affatto! Dato che i record "duplicati" hanno occupato altri spazi. Una spiegazione dettagliata con belle immagini è disponibile qui.

Espandiamo l'indice

Un abile sviluppatore capisce — è necessario rendere la chiave dell'indice unica, e il modo più semplice è espanderla con un campo univoco, in questo caso il PK va benissimo:

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

E la query muta in:

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

#2. Переход на «курсоры»

Un po' di tempo dopo, un DBA viene da te e "ti rende felice", dicendo che le tue query stanno sovraccaricando il server con i loro enormi OFFSET, e in effetti, sarebbe ora di passare a navigazione a partire dall'ultimo valore mostrato.La tua query muta di nuovo:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- gli ultimi valori ricevuti nel passaggio precedente
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Hai tirato un sospiro di sollievo, finché non è successo...

#3. Чистка индексов

Perché un giorno il tuo DBA ha letto un articolo sui metodi per trovare indici inefficaci e ha capito che "un timestamp non ultimo" non va bene.E ancora è tornato da te — ora con la convinzione che quell'indice debba tornare a essere (ts DESC).

Ma cosa fare con il problema iniziale dei "salti" tra le pagine?.. È semplice — dobbiamo selezionare blocchi con un numero non fisso di record!

In realtà, chi ci vieta di leggere non "esattamente 26", ma "almeno 26"? Ad esempio, in modo che nel blocco successivo ci siano record con valori notoriamente diversi ts — in tal modo non ci sarebbero problemi di "salto" tra i blocchi!

Ecco come raggiungere questo obiettivo:

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

Cosa succede qui?

Scendiamo di 25 record e otteniamo il valore "limite" ts.
Se non ci sono più record, sostituiamo il valore NULL con -infinity.
Sottraiamo l'intero segmento di valori tra il valore ottenuto ts e quello passato come parametro $1 (il precedente "ultimo" valore visualizzato).
Se il blocco restituito ha meno di 26 record — è l'ultimo.

Oppure la stessa cosa in forma grafica:

Dato che ora abbiamo una selezione senza un "inizio" definito, allora nulla ci impedisce di "inversare" questa richiesta e implementare il caricamento dinamico dei blocchi di dati dal "punto di riferimento" in entrambe le direzioni — sia verso il basso che verso l'alto.

Nota

Sì, in tal caso accediamo all'indice due volte, ma tutto "pura indice". Pertanto, la query annidata porterà solo a una sola scansione Index Only aggiuntiva.
È abbastanza ovvio che questa metodologia può essere utilizzata solo quando i vostri valori ts possono sovrapporsi solo per caso, e ce ne sono pochi. Tuttavia, se il vostro caso tipico è "un milione di record a 00:00:00.000", non dovreste farlo. Nel senso, non dovreste permettere che si presenti un tale caso. Ma se è andata così, usate l'opzione con l'indice esteso.

Fonte: habr.com