🥇PostgreSQL Antipatterns: navigazione nel registro

Oggi non ci saranno casi complessi e algoritmi sofisticati in SQL. Tutto sarà molto semplice, al livello di Captain Obvious: facciamolo visualizzazione del registro eventi ordinati per tempo.

Cioè, c'è un segno nel database events, e lei ha un campo ts - esattamente l'ora in cui vogliamo visualizzare questi record in modo ordinato:

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

CREATE INDEX ON events(ts DESC);

È chiaro che non avremo una dozzina di dischi lì, quindi avremo bisogno di qualche forma di disco navigazione della pagina.

#0. “Sono il pogromista di mia 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]);

Non è quasi uno scherzo: è raro, ma si trova in natura. A volte, dopo aver lavorato con ORM, può essere difficile passare al lavoro “diretto” con SQL.

Ma passiamo ai problemi più comuni e meno evidenti.

#1. COMPENSARE

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

Da dove viene il numero 26? Questo è il numero approssimativo di voci per riempire una schermata. Più precisamente, 25 record visualizzati, più 1, segnalano che c'è almeno qualcos'altro oltre nel campione e che ha senso andare avanti.

Naturalmente questo valore non può essere “cucito” nel corpo della richiesta, ma fatto passare attraverso un parametro. Ma in questo caso, lo scheduler PostgreSQL non potrà fare affidamento sulla consapevolezza che dovrebbero esserci relativamente pochi record e sceglierà facilmente un piano inefficace.

E mentre nell'interfaccia dell'applicazione, la visualizzazione del registro viene implementata come il passaggio da una "pagina" visiva all'altra, nessuno nota nulla di sospetto per molto tempo. Esattamente fino al momento in cui, nella lotta per la comodità, UI/UX decide di rifare l'interfaccia in "scorrimento infinito", ovvero tutte le voci del registro vengono disegnate in un unico elenco che l'utente può scorrere su e giù.

E così, durante il test successivo, vieni catturato duplicazione di registrazioni nel registro. Perché, perché la tabella ha un indice normale (ts), su cui si basa la tua query?

Proprio perché non ne hai tenuto conto ts non è una chiave univoca in questa tabella. In realtà, e i suoi valori non sono unici, come ogni "tempo" in condizioni reali - quindi, lo stesso record in due query adiacenti "salta" facilmente da una pagina all'altra a causa di un diverso ordine finale nell'ambito dell'ordinamento dello stesso valore chiave.

In effetti, qui si nasconde anche un secondo problema, che è molto più difficile da notare: alcune voci non verranno visualizzate affatto! Dopotutto, i record “duplicati” hanno preso il posto di qualcun altro. È possibile trovare una spiegazione dettagliata con bellissime immagini leggi qui.

Espansione dell'indice

Uno sviluppatore astuto capisce che la chiave dell'indice deve essere resa univoca e il modo più semplice è espanderla con un campo ovviamente unico, per il quale PK è perfetto:

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. Passa a "cursori"

Qualche tempo dopo, un DBA viene da te ed è “soddisfatto” delle tue richieste caricano il server da morire con le loro regole OFFSETe, in generale, è ora di passare a navigazione dall'ultimo valore visualizzato. La tua query muta di nuovo:

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

Hai tirato un sospiro di sollievo finché non è arrivato...

#3. Indici di pulizia

Perché un giorno il tuo DBA ha letto articolo sulla ricerca di indici inefficaci e me ne sono reso conto Il timestamp "non l'ultimo" non è corretto. E sono venuto di nuovo da te, ora con il pensiero che quell'indice dovrebbe ancora trasformarsi in (ts DESC).

Ma cosa fare con il problema iniziale di "saltare" i record tra le pagine?... E tutto è semplice: devi selezionare blocchi con un numero non fisso di record!

In generale, chi ci vieta di leggere non “esattamente 26”, ma “non meno di 26”? Ad esempio, in modo che nel blocco successivo ci siano dischi con significati ovviamente diversi ts - quindi non ci saranno problemi con il "salto" dei record tra i blocchi!

Ecco come ottenere questo risultato:

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;

Cosa sta succedendo qui?

Passiamo 25 record "verso il basso" e otteniamo il valore "limite". ts.
Se non c'è già nulla, sostituisci il valore NULL con -infinity.
Sottraiamo l'intero segmento di valoritra il valore ricevuto ts e il parametro $1 passato dall'interfaccia (il precedente valore renderizzato "ultimo").
Se viene restituito un blocco con meno di 26 record, sarà l'ultimo.

Oppure la stessa immagine:

Perché ora abbiamo il campione non ha alcun “inizio” specifico, quindi nulla ci impedisce di "espandere" questa richiesta nella direzione opposta e di implementare il caricamento dinamico dei blocchi di dati dal "punto di riferimento" in entrambe le direzioni, sia verso il basso che verso l'alto.

osservazione

Sì, in questo caso accediamo all'indice due volte, ma tutto è “puramente per indice”. Pertanto, una sottoquery risulterà solo a un'ulteriore scansione Solo indice.
È abbastanza ovvio che questa tecnica può essere utilizzata solo quando si hanno dei valori ts può attraversare solo per caso, e pochi di loro. Se il tuo caso tipico è “un milione di record alle 00:00:00.000”, non dovresti farlo. Voglio dire, non dovresti permettere che un caso del genere accada. Ma se ciò accade, utilizza l'opzione con un indice esteso.

Fonte: habr.com

Antipattern PostgreSQL: navigazione nel registro