ProHoster > blog > administratie > PostgreSQL Antipatterns: navigeren door het register

PostgreSQL Antipatterns: navigeren door het register

Tegenwoordig zullen er geen complexe cases en geavanceerde algoritmen meer zijn in SQL. Alles zal heel eenvoudig zijn, op het niveau van Captain Obvious - laten we het doen het gebeurtenissenregister bekijken gesorteerd op tijd.

Dat wil zeggen, er staat een teken in de database events, en ze heeft een veld ts - precies het tijdstip waarop we deze records overzichtelijk 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 geen tiental records zullen hebben, dus we zullen een of andere vorm van nodig hebben paginanavigatie.

#0. “Ik ben de pogromist van mijn moeder”

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

Het is bijna geen grap - het is zeldzaam, maar wordt in het wild gevonden. Soms kan het, na het werken met ORM, lastig zijn om over te stappen naar “direct” werken met SQL.

Maar laten we verder gaan met meer algemene en minder voor de hand liggende problemen.

#1. OFFSET

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

Waar komt het getal 26 vandaan? Dit is het geschatte aantal vermeldingen dat één scherm moet vullen. Om precies te zijn: er worden 25 records weergegeven, plus 1, wat aangeeft dat er nog iets anders in de steekproef zit en dat het zinvol is om verder te gaan.

Uiteraard kan deze waarde niet in de hoofdtekst van het verzoek worden 'genaaid', maar via een parameter worden doorgegeven. Maar in dit geval zal de PostgreSQL-planner niet kunnen vertrouwen op de wetenschap dat er relatief weinig records zouden moeten zijn - en zal hij gemakkelijk een ineffectief plan kiezen.

En terwijl in de applicatie-interface het bekijken van het register wordt geïmplementeerd als het schakelen tussen visuele "pagina's", merkt niemand lange tijd iets verdachts op. Precies tot het moment waarop UI/UX, in de strijd om gemak, besluit om de interface opnieuw te maken naar "eindeloos scrollen" - dat wil zeggen dat alle registervermeldingen in één enkele lijst worden weergegeven, zodat de gebruiker op en neer kan scrollen.

En dus word je tijdens de volgende test betrapt dupliceren van records in het register. Waarom, omdat de tabel een normale index heeft (ts), waar uw vraag op berust?

Juist omdat je daar geen rekening mee hebt gehouden ts is geen unieke sleutel in deze tabel. Eigenlijk, en de waarden ervan zijn niet uniek, zoals elke "tijd" in reële omstandigheden - daarom "springt" hetzelfde record in twee aangrenzende zoekopdrachten gemakkelijk van pagina naar pagina vanwege een andere uiteindelijke volgorde binnen het raamwerk van het sorteren van dezelfde sleutelwaarde.

In feite schuilt hier ook een tweede probleem, dat veel moeilijker op te merken is: sommige vermeldingen worden niet getoond helemaal! De ‘dubbele’ records namen immers de plaats van iemand anders in. Een uitgebreide uitleg met prachtige foto's vindt u lees hier.

Het uitbreiden van de index

Een sluwe ontwikkelaar begrijpt dat de indexsleutel uniek moet worden gemaakt, en de eenvoudigste manier is om deze uit te breiden met een duidelijk uniek veld, waar PK perfect voor is:

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

En het verzoek muteert:

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

#2. Schakel over naar “cursors”

Enige tijd later komt er een DBA naar u toe en is “tevreden” met uw verzoeken ze laden de server als een gek met hun OFFSET-regels, en over het algemeen is het tijd om naar over te schakelen navigatie vanaf de laatst getoonde waarde. Uw zoekopdracht muteert opnieuw:

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

Je slaakte een zucht van verlichting totdat het kwam...

#3. Reinigingsindexen

Omdat op een dag uw DBA gelezen werd artikel over het vinden van ineffectieve indexen en besefte dat “Niet de nieuwste” tijdstempel is niet goed. En ik kwam weer naar je toe - nu met de gedachte dat die index er toch weer in zou moeten veranderen (ts DESC).

Maar wat te doen met het aanvankelijke probleem van het "springen" van records tussen pagina's? En alles is eenvoudig: u moet blokken selecteren met een niet-vast aantal records!

Wie verbiedt ons in het algemeen om niet “precies 26”, maar “niet minder dan 26” te lezen? Bijvoorbeeld zodat ze er in het volgende blok zijn records met duidelijk verschillende betekenissen ts - dan is er geen probleem met het "springen" van records tussen blokken!

Hier ziet u hoe u dit kunt bereiken:

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;

Wat is hier aan de hand?

  1. We stappen 25 records “naar beneden” en krijgen de “grens” -waarde ts.
  2. Als er al niets is, vervang dan de NULL-waarde door -infinity.
  3. We trekken het hele segment van waarden af ​​tussen de ontvangen waarde ts en de parameter $1 doorgegeven vanuit de interface (de vorige “laatst” weergegeven waarde).
  4. Als een blok wordt geretourneerd met minder dan 26 records, is dit het laatste blok.

Of dezelfde foto:
PostgreSQL Antipatterns: navigeren door het register

Omdat we dat nu hebben het monster heeft geen specifiek “begin”, dan weerhoudt niets ons ervan om dit verzoek in de tegenovergestelde richting “uit te breiden” en het dynamisch laden van datablokken vanaf het “referentiepunt” in beide richtingen te implementeren - zowel naar beneden als naar boven.

opmerking

  1. Ja, in dit geval hebben we twee keer toegang tot de index, maar alles gebeurt “puur per index”. Daarom zal een subquery alleen resulteren in naar één extra alleen-indexscan.
  2. Het is overduidelijk dat deze techniek alleen kan worden gebruikt als je waarden hebt ts kan alleen bij toeval oversteken, en er zijn er niet veel. Als uw typische geval “een miljoen records om 00:00:00.000” is, moet u dit niet doen. Ik bedoel, je moet zo'n geval niet laten gebeuren. Maar als dit gebeurt, gebruik dan de optie met een uitgebreide index.

Bron: www.habr.com

Voeg een reactie