PostgreSQL अँटीपॅटर्न: रेजिस्ट्री नेव्हिगेट करणे

आज 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;

#२. "कर्सर" वर स्विच करा

काही काळानंतर, एक DBA तुमच्याकडे येतो आणि तुमच्या विनंतीबद्दल "खुश" होतो ते त्यांच्या ऑफसेट नियमांसह सर्व्हरला नरकासारखे लोड करतात, आणि सर्वसाधारणपणे, यावर स्विच करण्याची वेळ आली आहे दाखवलेल्या शेवटच्या मूल्यावरून नेव्हिगेशन. तुमची क्वेरी पुन्हा बदलते:

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

तो येईपर्यंत तू सुटकेचा नि:श्वास सोडलास...

#३. स्वच्छता निर्देशांक

कारण एक दिवस तुमचा डीबीए वाचला अप्रभावी अनुक्रमणिका शोधण्याविषयी लेख आणि ते लक्षात आले “नवीनतम नाही” टाइमस्टॅम्प चांगला नाही. आणि मी पुन्हा तुमच्याकडे आलो - आता त्या निर्देशांकात परत यावे या विचाराने (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