پوسٹگری ایس کیو ایل اینٹی پیٹرنز: رجسٹری کو نیویگیٹ کرنا

آج 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 "کرسر" پر سوئچ کریں

کچھ دیر بعد، ایک DBA آپ کے پاس آتا ہے اور آپ کی درخواستوں سے "خوش" ہوتا ہے۔ وہ اپنے آف سیٹ قوانین کے ساتھ سرور کو جہنم کی طرح لوڈ کرتے ہیں۔، اور عام طور پر، اس پر سوئچ کرنے کا وقت ہے۔ دکھائی گئی آخری قدر سے نیویگیشن. آپ کا استفسار دوبارہ بدل جاتا ہے:

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

آپ نے سکون کا سانس لیا یہاں تک کہ وہ آ گیا...

#3 اشاریہ جات کی صفائی

کیونکہ ایک دن آپ کا ڈی بی اے پڑھا تھا۔ غیر موثر اشاریہ جات تلاش کرنے کے بارے میں مضمون اور اس کا احساس ہوا "تازہ ترین نہیں" ٹائم اسٹیمپ اچھا نہیں ہے۔. اور میں آپ کے پاس دوبارہ آیا ہوں - اب اس سوچ کے ساتھ کہ اس انڈیکس کو اب بھی واپس جانا چاہئے۔ (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. جی ہاں، اس صورت میں ہم انڈیکس تک دو بار رسائی حاصل کرتے ہیں، لیکن سب کچھ "خالص طور پر انڈیکس کے لحاظ سے" ہے۔ لہذا، ایک ذیلی استفسار کا نتیجہ ہی نکلے گا۔ ایک اضافی انڈیکس صرف اسکین کے لیے.
2. یہ بالکل واضح ہے کہ یہ تکنیک صرف اس وقت استعمال کی جا سکتی ہے جب آپ کے پاس اقدار ہوں۔ ts صرف اتفاق سے پار کر سکتے ہیں، اور ان میں سے بہت سے نہیں ہیں. اگر آپ کا عام کیس "00:00:00.000 پر ایک ملین ریکارڈ" ہے، تو آپ کو ایسا نہیں کرنا چاہیے۔ میرا مطلب ہے، آپ کو ایسا معاملہ نہیں ہونے دینا چاہیے۔ لیکن اگر ایسا ہوتا ہے تو، توسیعی انڈیکس کے ساتھ آپشن کا استعمال کریں۔

ماخذ: www.habr.com