PostgreSQL Antipatterns: رجسٽري کي نيويگيٽ ڪرڻ

اڄ ڪو به پيچيده ڪيس نه هوندو ۽ SQL ۾ نفيس الگورتھم. سڀ ڪجھ بلڪل سادو ٿيندو، ڪئپٽن واضح جي سطح تي - اچو ته اھو ڪريون واقعي جي رجسٽري کي ڏسڻ وقت جي ترتيب سان.

اهو آهي، ڊيٽابيس ۾ هڪ نشاني آهي events، ۽ هن وٽ هڪ ميدان آهي ts - بلڪل اهو وقت جنهن تي اسان انهن رڪارڊن کي منظم انداز ۾ ڏيکارڻ چاهيون ٿا:

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

CREATE INDEX ON events(ts DESC);

اهو واضح آهي ته اسان وٽ درجن وارا رڪارڊ نه هوندا، تنهنڪري اسان کي ڪجهه فارم جي ضرورت پوندي صفحي جي نيويگيشن.

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

اهو تقريبا هڪ مذاق نه آهي - اهو نادر آهي، پر جهنگلي ۾ مليو. ڪڏهن ڪڏهن، ORM سان ڪم ڪرڻ کان پوءِ، SQL سان گڏ ”سڌي“ ڪم تي سوئچ ڪرڻ ڏکيو ٿي سگهي ٿو.

پر اچو ته وڌيڪ عام ۽ گهٽ واضح مسئلن ڏانهن وڃو.

#1. آفسٽ

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

نمبر 26 ڪٿان آيو؟ هي هڪ اسڪرين ڀرڻ لاءِ داخلائن جو لڳ ڀڳ تعداد آهي. وڌيڪ واضح طور تي، 25 ڏيکاريل رڪارڊ، گڏوگڏ 1، اشارو ڪري ٿو ته نموني ۾ گهٽ ۾ گهٽ ٻيو ڪجهه آهي ۽ اهو اڳتي وڌڻ جو احساس آهي.

يقينن، هي قدر نه ٿي سگهي ٿو "سلائي" درخواست جي جسم ۾، پر هڪ پيٽرولر ذريعي گذري ٿو. پر انهي صورت ۾، PostgreSQL شيڊولر ان ڄاڻ تي ڀروسو ڪرڻ جي قابل نه هوندو ته نسبتا ڪجھ رڪارڊ هجڻ گهرجي - ۽ آساني سان هڪ غير موثر منصوبو چونڊيو ويندو.

۽ جڏهن ته ايپليڪيشن انٽرفيس ۾، رجسٽري کي ڏسڻ ۾ بصري "صفحن" جي وچ ۾ سوئچنگ جي طور تي عمل ڪيو ويو آهي، ڪو به هڪ ڊگهي وقت تائين مشڪوڪ شيء کي نوٽيس نٿو ڪري. بلڪل ان وقت تائين جڏهن، سهولت لاءِ جدوجهد ۾، UI/UX فيصلو ڪري ٿو ته انٽرفيس کي ”انٽرفيس لامحدود اسڪرول“ ۾ ريميڪ ڪيو وڃي- يعني، سڀ رجسٽري داخلائون هڪ ئي لسٽ ۾ ٺهيل آهن، جنهن کي صارف مٿي ۽ هيٺ ڪري سگهي ٿو.

۽ ائين، ايندڙ جاچ دوران، توهان پڪڙي رهيا آهيو رڪارڊ جو نقل رجسٽري ۾. ڇو، ڇو ته ٽيبل ۾ هڪ عام انڊيڪس آهي (ts)، جنهن تي توهان جو سوال ڀروسو آهي؟

بلڪل انهيءَ ڪري جو توهان ان ڳالهه تي ڌيان نه ڏنو ts هڪ منفرد چاٻي نه آهي هن ٽيبل ۾. حقيقت ۾، ۽ ان جا قدر منفرد نه آهنڪنهن به ”وقت“ وانگر حقيقي حالتن ۾ - تنهن ڪري، ساڳيو رڪارڊ ٻن ويجهن سوالن ۾ آسانيءَ سان ”جمپ“ ٿئي ٿو صفحي کان ٻئي صفحي جي ڇاڪاڻ ته ساڳئي اهم قدر کي ترتيب ڏيڻ جي فريم ورڪ ۾ مختلف حتمي ترتيب جي ڪري.

حقيقت ۾، هتي هڪ ٻيو مسئلو پڻ لڪيل آهي، جنهن کي نوٽيس ڪرڻ تمام گهڻو ڏکيو آهي. ڪجهه داخل نه ڏيکاريا ويندا بلڪل! آخرڪار، "نقل" رڪارڊ ڪنهن ٻئي جي جاء ورتي. خوبصورت تصويرن سان تفصيلي وضاحت ملي سگهي ٿي هتي پڙهو.

انڊيڪس کي وڌائڻ

هڪ هوشيار ڊولپر سمجهي ٿو ته انڊيڪس ڪي کي منفرد بڻائڻ جي ضرورت آهي، ۽ آسان طريقو اهو آهي ته ان کي واضح طور تي منفرد فيلڊ سان وڌايو وڃي، جنهن لاءِ PK مڪمل آهي:

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

۽ درخواست کي تبديل ڪري ٿو:

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

#2. "Cursors" ڏانهن تبديل ڪريو

ڪجهه وقت کان پوء، هڪ DBA توهان وٽ اچي ٿو ۽ "خوش" آهي ته توهان جي درخواستن اهي سرور کي دوزخ وانگر لوڊ ڪندا آهن انهن جي آفسٽ قاعدن سان، ۽ عام طور تي، ان کي تبديل ڪرڻ جو وقت آهي ڏيکاريل آخري قدر مان نيويگيشن. توھان جو سوال وري بدلجي ٿو:

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

توهان راحت جو ساهه کنيو جيستائين اهو آيو ...

#3. صفائي جي انڊيڪس

ڇو ته هڪ ڏينهن تنهنجو DBA پڙهيو غير موثر انڊيڪس ڳولڻ بابت آرٽيڪل ۽ اهو محسوس ڪيو "تازو نه" ٽائم اسٽيمپ سٺو ناهي. ۽ مان توهان وٽ ٻيهر آيو آهيان - هاڻي ان سوچ سان ته انڊيڪس اڃا تائين واپس موٽڻ گهرجي (ts DESC).

پر صفحن جي وچ ۾ ”جمپنگ“ رڪارڊ جي شروعاتي مسئلي سان ڇا ڪجي؟... ۽ سڀ ڪجھ سادو آهي - توهان کي اڻڄاتل رڪارڊن سان بلاڪ چونڊڻ جي ضرورت آهي!

عام طور تي، ڪير اسان کي پڙهڻ کان منع ڪري ٿو "بلڪل 26" نه، پر "26 کان گهٽ نه"؟ مثال طور، ته جيئن ايندڙ بلاڪ ۾ موجود آهن واضح طور تي مختلف معنائن سان رڪارڊ ts - پوءِ بلاڪن جي وچ ۾ ”جمپنگ“ ريڪارڊ سان ڪو مسئلو نه ٿيندو!

هتي اهو ڪيئن حاصل ڪجي:

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;

هتي ڇا ٿي رهيو آهي؟

1. اسان قدم 25 رڪارڊ "هيٺ" ۽ حاصل ڪريو "حد" قدر ts.
2. جيڪڏهن اتي اڳ ۾ ئي ڪجھ به نه آهي، پوء NULL قدر سان تبديل ڪريو -infinity.
3. اسان حاصل ڪيل قيمت جي وچ ۾ قيمتن جي پوري ڀاڱي کي ختم ڪريون ٿا ts ۽ انٽرفيس مان گذري ويو $1 پيٽرول (اڳوڻي "آخري" پيش ڪيل قيمت).
4. جيڪڏهن هڪ بلاڪ 26 کان گهٽ رڪارڊ سان واپس آيو آهي، اهو آخري آهي.

يا ساڳي تصوير:

ڇاڪاڻ ته هاڻي اسان وٽ آهي نموني ۾ ڪا خاص "شروع" نه آهي، پوءِ ڪجھ به اسان کي هن درخواست کي مخالف سمت ۾ ”وڌائڻ“ کان روڪي ٿو ۽ ٻنهي طرفن ۾ ”ريفرنس پوائنٽ“ کان ڊيٽا بلاڪ جي متحرڪ لوڊشيڊنگ کي لاڳو ڪرڻ - ٻئي هيٺ ۽ مٿي.

نوٽ

1. ها، هن معاملي ۾ اسان انڊيڪس تائين ٻه ڀيرا رسائي ڪريون ٿا، پر هر شيء "خالص طور تي انڊيڪس" آهي. تنهن ڪري، هڪ subquery رڳو نتيجو ٿيندو هڪ اضافي انڊيڪس صرف اسڪين تائين.
2. اهو بلڪل واضح آهي ته هي ٽيڪنڪ صرف استعمال ڪري سگهجي ٿي جڏهن توهان وٽ قدر آهن ts صرف اتفاق سان پار ڪري سگهجي ٿو، ۽ انهن مان گهڻا نه آهن. جيڪڏهن توهان جو عام ڪيس آهي "00:00:00.000 تي هڪ ملين رڪارڊ"، توهان کي اهو نه ڪرڻ گهرجي. منهنجو مطلب، توهان کي اهڙي صورت ۾ ٿيڻ جي اجازت نه ڏيڻ گهرجي. پر جيڪڏهن ائين ٿئي ٿو، اختيار استعمال ڪريو وڌايل انڊيڪس سان.

جو ذريعو: www.habr.com