PostgreSQL Antipatterns: navigeren door het register

Vandaag zijn er geen complexe casussen of ingewikkelde SQL-algoritmen. Alles is heel eenvoudig, op het niveau van de Captain Obvious - we doen Een registratie van gebeurtenissen bekijken met sortering op tijd.

Dat wil zeggen, er ligt een tabel in de database events, en het heeft een veld ts — precies het tijdstip waarop we deze records ordelijk willen weergeven:

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

CREATE INDEX ON events(ts DESC);

Het is duidelijk dat we daar meer dan een paar records zullen hebben, dus we hebben op een of andere manier pagina-navigatie.

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

Bijna geen grap - zelden, maar het komt voor in de wilde natuur. Soms is het moeilijk om na het werken met ORM over te schakelen naar 'directe' SQL-werkzaamheden.

Maar laten we overstappen naar meer voorkomende en minder voor de hand liggende problemen.

#1. OFFSET

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - records op de pagina, $1 - begin van de pagina

Waar komt dit getal 26 vandaan? Dit is het geschatte aantal records dat één scherm kan vullen. Preciezer, 25 weergegeven records plus 1, die aangeeft dat er verder in de selectie nog iets is en dat het de moeite waard is om verder te gaan.

Natuurlijk, deze waarde kan niet in de query zelf worden 'hardcoded', maar via een parameter worden doorgegeven. Maar in dat geval kan de PostgreSQL-planner niet rekenen op de kennis dat er relatief weinig records zullen zijn, en zal hij zonder meer een inefficiënt plan kiezen.

En zolang de weergave van de registratie in de applicatie wordt geïmplementeerd als het omschakelen tussen visuele 'pagina's', merkt niemand iets verdachts op. Tot het moment dat, in de strijd voor een gebruiksvriendelijke UI/UX, besloten wordt de interface om te stellen op 'oneindige scrollen' - dat wil zeggen dat alle registratie-activiteiten als één lijst worden weergegeven die de gebruiker omhoog en omlaag kan scrollen.

En nu, bij de volgende test wordt u betrapt op het dupliceren van records in de registratie. Waarom, want er is een normale index op de tabel (ts), waarop uw query vertrouwt?

Precisie omdat u niet in aanmerking hebt genomen dat ts geen unieke sleutel is in deze tabel. Eigenlijk zijn de waarden ook niet uniek, zoals bij elk ‘tijd’ in de echte wereld – daarom kan dezelfde vermelding gemakkelijk van de ene naar de andere pagina overspringen bij verschillende verzoeken door een andere eindordening in de sortering van dezelfde sleutelwaarde.

In feite is er hier nog een tweede probleem verborgen, wat veel moeilijker op te merken is — sommige vermeldingen zullen helemaal niet worden getoond! Want ‘gedupliceerde’ vermeldingen hebben iemands plek ingenomen. Een gedetailleerde uitleg met mooie afbeeldingen is hier te lezen.

Index uitbreiden

Een slimme ontwikkelaar begrijpt — de sleutel van de index moet uniek zijn, en de eenvoudigste manier is om deze uit te breiden met een bewust uniek veld, waarbij PK uitstekend geschikt is:

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

En de query muteert:

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

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

Enige tijd later komt er een DBA naar je toe en ‘verblijdt’ je met het feit dat jouw queries de server belasten met hun enorme OFFSET, en eigenlijk is het hoog tijd om over te stappen naar navigatie vanaf de laatst getoonde waarde. Je query muteert weer:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- de laatst verkregen waarden van de vorige stap
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Je haalt opgelucht adem, totdat... er zich een situatie voordoet.

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

Omdat jouw DBA op een dag een artikel over ondoeltreffende indexen heeft gelezen en zich realiseerde dat een ‘niet de laatste’ timestamp — dat is niet goed. En opnieuw kwam hij naar je toe — nu met de gedachte dat die index toch weer moet terugkeren naar (ts DESC).

Maar wat moeten we doen met het oorspronkelijke probleem van het ‘overspringen’ van vermeldingen tussen pagina's?.. Het is heel eenvoudig — we moeten blokken selecteren met een niet-vast aantal vermeldingen!

Over het algemeen, wie verbiedt ons om niet ‘precies 26’, maar ‘minstens 26’ te lezen? Bijvoorbeeld, zodat in het volgende blok vermeldingen met bewust andere waarden verschijnen — dan is er immers geen probleem meer met het ‘overspringen’ van vermeldingen tussen blokken! ts Zo kun je dat bereiken:

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

Wat gebeurt hier eigenlijk?

We stappen 25 vermeldingen ‘omlaag’ en krijgen de ‘grens’ waarde.

1. Als daar al niets is, vervangen we de NULL-waarde door ts.
2. -infinity. We lezen het volledige segment waarden tussen de verkregen waarde.
3. en de parameter $1 (de vorige ‘laatste’ weergegeven waarde) die vanuit de interface is doorgegeven. ts en de vanuit de interface doorgegeven parameter $1 (de voorafgaande "laatste" weergegeven waarde).
4. Als het blok terugkomt met minder dan 26 records - dan is het de laatste.

Of hetzelfde in beeld:

Aangezien we nu de steekproef geen bepaald 'begin' heeft, staat niets ons in de weg om dit verzoek om te keren en dynamisch gegevensblokken te laden vanaf het 'referentiepunt' in beide richtingen - zowel naar beneden als naar boven.

Opmerking

1. Ja, in dat geval raadplegen we de index twee keer, maar alles 'gewoon via de index'. Daarom leidt de geneste query tot slechts één extra Index Only Scan.
2. Het is vrij duidelijk dat deze methode alleen gebruikt kan worden wanneer u waarden heeft ts die slechts toevallig kunnen overlappen, en er niet veel zijn. Als uw typische geval echter 'een miljoen records om 00:00:00.000' is, dan moet dat niet gedaan worden. Met andere woorden, dat soort gevallen moet niet voorkomen. Maar als het zo is gekomen, gebruik dan de optie met de uitgebreide index.

Bron: habr.com