PostgreSQL Antipatterns: navigazione nel registro

Oggi non ci saranno casi complicati né algoritmi complicati in SQL. Sarà tutto molto semplice, a livello del Capitano Evidente - facciamo la visualizzazione del registro eventi ordinando per tempo.

Cioè, ecco un tavolo nel database events, e ha un campo ts che è esattamente il tempo in base al quale vogliamo mostrare queste registrazioni 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 registrazioni, quindi avremo bisogno in un certo modo di navigazione per pagine.

#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]);

Non è neanche una battuta - raramente, ma succede nella natura selvaggia. A volte, dopo aver lavorato con ORM, è difficile tornare al lavoro "diretto" con SQL.

Ma passiamo a problemi più comuni e meno evidenti.

#1. OFFSET

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

Da dove è saltato fuori il numero 26? È la quantità approssimativa di registrazioni per riempire uno schermo. Più precisamente, 25 registrazioni visualizzate, più 1, che segnala che ci sono ulteriori elementi nella selezione e ha senso andare oltre.

Naturalmente, questo valore non deve essere "incorporato" nel corpo della query, ma può essere passato tramite un parametro. Ma in questo caso, il pianificatore di PostgreSQL non potrà fare affidamento sulla conoscenza che le registrazioni devono essere relativamente poche - e potrebbe facilmente scegliere un piano inefficace.

E finché nell'interfaccia dell'applicazione la visualizzazione del registro è implementata come un cambio tra "pagine" visive, nessuno nota a lungo nulla di strano. Fino al momento in cui, nella lotta per la comodità dell'UI/UX, decidono di rifare l'interfaccia in "scroll infinito" - cioè tutte le registrazioni del registro sono disegnate in un'unica lista, che l'utente può scorrere su e giù.

E quindi, durante un altro test, vi sorprendono a duplicare le registrazioni nel registro. Perché, visto che sulla tabella c'è un normale indice (ts), su cui si basa la tua query?

Proprio perché non hai considerato che ts non è una chiave unica in questa tabella. Infatti, anche i valori in essa non sono unici, come per ogni «tempo» in condizioni reali — pertanto, la stessa registrazione in due interrogazioni consecutive salta facilmente da una pagina all'altra grazie a un diverso ordine finale all'interno dell'ordinamento di un valore chiave identico.

In realtà, qui si nasconde anche un secondo problema, che è molto più difficile da notare — alcune registrazioni non verranno mostrate affatto! Infatti, le registrazioni «doppie» hanno occupato il posto di qualcun altro. Una spiegazione dettagliata con belle immagini può essere letto qui.

Espandiamo l'indice

Uno sviluppatore astuto capisce che è necessario rendere la chiave dell'indice unica, e il modo più semplice è espanderla con un campo sicuramente unico, che potrebbe essere perfettamente PK:

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

E la richiesta muta:

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

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

Dopo un po' di tempo, un DBA viene da te e «si rallegrerà», dicendo che le tue interrogazioni caricano l'intero server con i loro chilometrici OFFSET, e in generale, sarebbe ora di passare a una navigazione dal valore più recente mostrato. La tua richiesta muta di nuovo:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- gli ultimi valori ricevuti nel passo 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 sulla ricerca di indici inefficaci e ha capito che un timestamp «non ultimo» non è una buona cosa. E di nuovo è tornato da te — ora con l'idea che quell'indice dovrebbe comunque tornare a essere (ts DESC).

Ma cosa fare con il problema originale del «salto» delle registrazioni tra le pagine?.. È semplice: bisogna selezionare blocchi con un numero di registrazioni non fissato!

In effetti, chi ci vieta di leggere non «esattamente 26», ma «almeno 26»? Ad esempio, in modo che nel blocco seguente ci siano registrazioni con valori sicuramente diversi ts - in tal caso non ci saranno problemi di «salto» delle registrazioni tra i blocchi!

Ecco come ottenerlo:

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 sta succedendo qui?

1. Scendiamo di 25 registrazioni e otteniamo un valore «limite» ts.
2. Se non c'è già nulla, sostituiamo il valore NULL con -infinity.
3. Sottraiamo l'intero segmento di valori tra il valore ottenuto ts e il parametro $1 passato dall'interfaccia (il precedente «ultimo» valore visualizzato).
4. Se il blocco è tornato con meno di 26 record, è l'ultimo.

O lo stesso in immagine:

Poiché ora abbiamo un campione che non ha un «inizio» definito, non ci impedisce di «invertire» questa richiesta e implementare un caricamento dinamico dei blocchi di dati da un «punto di riferimento» in entrambe le direzioni, sia verso il basso che verso l'alto.

Nota

1. Sì, in questo caso ci rivolgiamo all'indice due volte, ma tutto «pulito per indice». Pertanto, la richiesta annidata porterà solo a una scansione Index Only aggiuntiva.
2. È piuttosto ovvio che questa metodologia può essere utilizzata solo quando hai valori ts che possono sovrapporsi solo casualmente e sono pochi. Se il tuo caso tipico è «un milione di record in 00:00:00.000», non dovresti farlo. Nel senso che non dovresti consentire un tale caso. Ma se è andata così, utilizza l'opzione con l'indice esteso.

Fonte: habr.com