ProHoster > Blog > Administrasi > Antipatterns PostgreSQL: Napigasi pendaptaran

Antipatterns PostgreSQL: Napigasi pendaptaran

Kiwari moal aya kasus rumit sareng algoritma canggih dina SQL. Sagalana bakal saderhana pisan, dina tingkat Kaptén Obvious - hayu urang laksanakeun nempo pendaptaran acara diurutkeun dumasar kana waktu.

Hartina, aya tanda dina database events, jeung manehna boga sawah ts - persis waktos urang hoyong nampilkeun rékaman ieu sacara teratur:

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

CREATE INDEX ON events(ts DESC);

Ieu jelas yén urang moal boga belasan rékaman aya, jadi urang bakal butuh sababaraha bentuk navigasi kaca.

#0. "Kuring pogromist indung kuring"

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

Ieu ampir teu lulucon - éta langka, tapi kapanggih di alam liar. Sakapeung, saatos damel sareng ORM, tiasa sesah ngalih ka "langsung" damel sareng SQL.

Tapi hayu urang ngaléngkah ka masalah anu langkung umum sareng kirang atra.

#1. OFFSET

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

Ti mana asalna angka 26? Ieu mangrupikeun perkiraan jumlah éntri pikeun ngeusian hiji layar. Leuwih tepat, 25 ditampilkeun rékaman, tambah 1, signalling yén aya sahanteuna hal sejenna salajengna dina sampel sarta asup akal pikeun ngaléngkah.

Tangtu, nilai ieu teu bisa "sewn" kana awak pamundut, tapi ngaliwatan parameter a. Tapi dina hal ieu, penjadwal PostgreSQL moal tiasa ngandelkeun pangaweruh yén kedah aya sababaraha rékaman - sareng bakal gampang milih rencana anu henteu efektif.

Sareng nalika dina antarmuka aplikasi, ningali pendaptaran dilaksanakeun salaku gentos antara "halaman" visual, teu aya anu perhatikeun naon waé anu curiga pikeun waktos anu lami. Persis dugi ka momen nalika, dina perjuangan pikeun genah, UI / UX mutuskeun pikeun ngadamel ulang antarmuka kana "gulungan sajajalan" - nyaéta, sadaya éntri pendaptaran digambar dina daptar tunggal anu pangguna tiasa ngagulung ka luhur sareng ka handap.

Janten, salami tés salajengna, anjeun katangkep duplikasi rékaman dina pendaptaran. Naha, sabab tabél boga indéks normal (ts), nu ngandelkeun pamundut anjeun?

Persis sabab anjeun henteu tumut kana akun éta ts sanes konci unik dina tabél ieu. Sabenerna, jeung nilai na teu unik, Sapertos "waktos" dina kaayaan nyata - janten, catetan anu sami dina dua pamundut padeukeut gampang "luncat" tina halaman ka halaman kusabab urutan ahir anu béda dina kerangka asihan nilai konci anu sami.

Nyatana, aya ogé masalah kadua anu disumputkeun di dieu, anu langkung hese perhatikeun - sababaraha éntri moal ditémbongkeun pisan! Barina ogé, rékaman "duplikat" nyandak tempat batur. Penjelasan lengkep sareng gambar anu saé tiasa dipendakan baca di dieu.

Ngalegaan indéks

Pamekar anu licik ngartos yén konci indéks kedah dilakukeun unik, sareng cara anu paling gampang nyaéta ngalegaan éta kalayan lapangan anu jelas unik, anu PK sampurna pikeun:

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

Jeung pamundut mutates:

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

#2. Pindah ka "kursor"

Sababaraha waktu engké, a DBA datang ka anjeun sarta "pleased" nu requests Anjeun aranjeunna ngamuat server kawas naraka jeung aturan OFFSET maranéhna, sarta sacara umum, éta waktu pikeun pindah ka navigasi ti nilai panungtungan ditémbongkeun. Patarosan anjeun mutates deui:

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

Anjeun ngarenghap lega dugi ka sumping...

#3. Indéks beberesih

Kusabab hiji poé DBA Anjeun maca artikel ngeunaan manggihan indexes teu epektip sarta sadar éta "sanes panganyarna" timestamp teu alus. Sareng kuring sumping ka anjeun deui - ayeuna kalayan pamikiran yén indéks éta masih kedah dibalikkeun deui (ts DESC).

Tapi naon anu kudu dipigawé kalayan masalah awal "luncat" rékaman antara kaca?.. Jeung sagalana basajan - anjeun kudu milih blok kalawan jumlah unfixed rékaman!

Sacara umum, anu ngalarang urang maca teu "persis 26", tapi "teu kirang ti 26"? Contona, supados dina blok salajengna aya rékaman kalawan harti jelas béda ts - teras moal aya masalah sareng "luncat" rékaman antara blok!

Ieu kumaha carana ngahontal ieu:

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;

Aya naon di dieu?

  1. Urang lengkah 25 rékaman "handap" tur meunangkeun nilai "wates". ts.
  2. Upami teu aya nanaon, teras ganti nilai NULL sareng -infinity.
  3. Urang ngurangan sakabéh bagéan nilai antara nilai narima ts jeung $ 1 parameter diliwatan ti panganteur (saméméhna "panungtungan" nilai rendered).
  4. Upami blok dipulangkeun kalayan kirang ti 26 rékaman, éta mangrupikeun anu terakhir.

Atawa gambar anu sarua:
Antipatterns PostgreSQL: Napigasi pendaptaran

Kusabab ayeuna urang geus Sampel henteu ngagaduhan "awal" khusus, teras teu aya anu nyegah urang "ngalegaan" pamundut ieu dina arah anu sabalikna sareng ngalaksanakeun beban dinamis blok data tina "titik rujukan" dina dua arah - boh ka handap sareng ka luhur.

nyarios

  1. Leres, dina hal ieu kami ngaksés indéks dua kali, tapi sadayana "murni ku indéks". Ku alatan éta, hiji subquery ngan bakal ngahasilkeun ka hiji Indéks Ukur Scan tambahan.
  2. Ieu rada atra yén téhnik ieu ngan bisa dipaké nalika anjeun boga nilai ts bisa meuntas ngan ku kasempetan , jeung aya teu loba di antarana. Upami kasus has anjeun "sajuta rékaman di 00:00:00.000", anjeun teu kedah ngalakukeun ieu. Maksud kuring, anjeun henteu kedah ngantepkeun kasus sapertos kitu. Tapi upami ieu kajantenan, paké pilihan kalayan indéks anu diperpanjang.

sumber: www.habr.com

Tambahkeun komentar