🥇PostgreSQL Antipatterns: navigering genom registret

Idag blir det inga komplexa fall eller snåriga algoritmer i SQL. Allt kommer att vara väldigt enkelt, på nivå med Kapten Upplysning — vi gör en visning av händelseregistret med sortering efter tid.

Det vill säga, här har vi en tabell i databasen events, och den har ett fält ts — exakt den tid vi vill visa dessa poster ordnat:

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

CREATE INDEX ON events(ts DESC);

Självklart kommer det att finnas fler än ett dussin poster, så vi behöver på något sätt sida navigering.

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

Det är nästan ingen skämt — det dyker sällan upp i det vilda. Ibland kan det vara svårt att ställa om från ORM till "direkt" arbete med SQL.

Men låt oss gå vidare till mer vanliga och mindre uppenbara problem.

#1. OFFSET

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - poster på sidan, $1 - början på sidan

Var kom siffran 26 ifrån? Det är den ungefärliga mängden poster för att fylla en skärm. Närmare bestämt 25 visade poster, plus 1, som signalerar att det fortfarande finns något mer i urvalet och att det är värt att gå vidare.
Självklart kan detta värde inte "bakas in" i frågan, utan överföras via en parameter. Men i så fall kan PostgreSQL:s planering inte lita på att det kommer att finnas få poster — och kan lätt välja en ineffektiv plan.

Och så länge visningen av händelseregistret i applikationen är implementerad som en växling mellan visuella "sidor", lägger ingen länge märke till något misstänkt. Fram till dess att man i jakten på användarvänlighet UI/UX bestämmer sig för att göra om gränssnittet till "oändlig scroll" — det vill säga alla registreringsposter visas som en lista som användaren kan scrolla upp och ner.

Och nu, vid nästa testning fångas du på duplicering av poster i registret. Varför, eftersom det finns ett normalt index i tabellen (ts), som din fråga förlitar sig på?

Exakt därför att du inte tog hänsyn till att ts inte är en unik nyckel i denna tabell. I själva verket är värdena inte unika, som med all "tid" i verkliga förhållanden — därför kan samma post i två angränsande förfrågningar lätt "hoppa" från sida till sida på grund av en annan slutlig ordning i sorteringen av samma nyckelvärdesresultat.

Faktum är att här döljer sig även ett andra problem, som är mycket svårare att upptäcka — vissa poster kommer inte att visas överhuvudtaget! Eftersom de "dubbelnoterade" posterna tog upp någon annans plats. En detaljerad förklaring med fina bilder kan läsas här.

Utöka indexet

Den smarta utvecklaren förstår — man måste göra indexnyckeln unik, och det enklaste sättet är att utöka den med ett medvetet unikt fält, där primärnyckel (PK) passar utmärkt:

SKAPA UNIKT INDEX PÅ events(ts DESC, id DESC);

Och frågan muterar:

VÄLJ
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

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

Ett tag senare kommer DBA:en till dig och "gläder" dig med att dina frågor trycker ner servern med sina förskräckliga OFFSET, och överlag är det dags att övergå till navigering från det senaste visade värdet. Din fråga muterar igen:

VÄLJ
  ...
DÄR
  (ts, id) < ($1, $2) -- senaste mottagna värden från föregående steg
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Du suckade lättat, tills det hände...

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

För en dag läste din DBA en artikel om ineffektiva index och insåg att "inte sista" tidsstämpel — det är inte bra. Och han kom åter till dig — den här gången med tanken att det indexet verkligen borde förvandlas tillbaka till (ts DESC).

Men vad ska man göra med det ursprungliga problemet med "hoppen" mellan sidorna?.. Jo, allt är enkelt — man måste välja block med obestämt antal poster!

Vem förbjuder oss att läsa inte "exakt 26", utan "minst 26"? Till exempel så att i nästa block hamnar poster med medvetet andra värden ts — då kommer det inte att finnas något problem med "hoppen" mellan blocken!

Så här gör man för att uppnå det:

VÄLJ
  ...
DÄR
  ts = coalesce(
    VÄLJ
      ts
    FRÅN
      events
    DÄR
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-oändlighet')
ORDER BY
  ts DESC;

Vad händer här överhuvudtaget?

Vi kliver ner 25 poster och får det "gränsvärde" ts.
Om det inte finns något där, byter vi NULL-värdet mot -oändlighet.
Vi beräknar hela segmentet av värden mellan det erhållna värdet ts och parametern $1 som skickats från gränssnittet (det tidigare "senaste" renderade värdet).
Om blocket har färre än 26 poster — är det det sista.

Eller samma sak med en bild:

Eftersom vi nu har urvalet har inget specifikt "början", hindrar inget oss från att "utveckla" denna fråga bakåt och genomföra dynamisk inläsning av datablokker från "referenspunkt" i båda riktningar — både nedåt och uppåt.

Anmärkning

Ja, i så fall kontaktar vi indexet två gånger, men allt sker "rent enligt index". Därför kommer den inbäddade frågan att leda till endast en extra Index Only Scan.
Det är ganska uppenbart att denna metodik bara kan användas när ni har värden ts kan korsa varandra endast av en slump, och det är inte många. Men om ert typiska fall är "en miljon poster på 00:00:00.000", bör ni inte göra så. Med andra ord, ni bör inte tillåta ett sådant fall. Men om det nu har blivit så, använd alternativet med det utvidgade indexet.

Källa: habr.com