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 शेड्यूलर इस ज्ञान पर भरोसा नहीं कर पाएगा कि अपेक्षाकृत कम रिकॉर्ड होने चाहिए - और आसानी से एक अप्रभावी योजना चुन लेगा।

और एप्लिकेशन इंटरफ़ेस में, रजिस्ट्री को देखने को विज़ुअल "पेजों" के बीच स्विच करने के रूप में कार्यान्वित किया जाता है, किसी को भी लंबे समय तक कुछ भी संदिग्ध नज़र नहीं आता है। ठीक उस क्षण तक, जब सुविधा के लिए संघर्ष में, यूआई/यूएक्स इंटरफ़ेस को "अंतहीन स्क्रॉल" में रीमेक करने का निर्णय लेता है - अर्थात, सभी रजिस्ट्री प्रविष्टियाँ एक ही सूची में खींची जाती हैं जिसे उपयोगकर्ता ऊपर और नीचे स्क्रॉल कर सकता है।

और इसलिए, अगले परीक्षण के दौरान, आप पकड़े जाएंगे अभिलेखों का दोहराव रजिस्ट्री में. क्यों, क्योंकि तालिका में एक सामान्य सूचकांक है (ts), आपकी क्वेरी किस पर निर्भर करती है?

बिल्कुल इसलिए क्योंकि आपने उस पर ध्यान नहीं दिया ts कोई अनोखी कुंजी नहीं है इस तालिका में. दरअसल, और इसके मूल्य अद्वितीय नहीं हैं, वास्तविक परिस्थितियों में किसी भी "समय" की तरह - इसलिए, समान कुंजी मान को सॉर्ट करने के ढांचे के भीतर एक अलग अंतिम क्रम के कारण दो आसन्न प्रश्नों में एक ही रिकॉर्ड आसानी से एक पृष्ठ से दूसरे पृष्ठ पर "कूद" जाता है।

दरअसल, यहां एक दूसरी समस्या भी छिपी हुई है, जिसे नोटिस करना ज्यादा मुश्किल है - कुछ प्रविष्टियाँ नहीं दिखाई जाएंगी बिल्कुल भी! आख़िरकार, "डुप्लिकेट" रिकॉर्ड ने किसी और की जगह ले ली। सुंदर चित्रों के साथ विस्तृत विवरण पाया जा सकता है यहाँ पढ़ें.

सूचकांक का विस्तार

एक चालाक डेवलपर समझता है कि सूचकांक कुंजी को अद्वितीय बनाने की आवश्यकता है, और सबसे आसान तरीका इसे स्पष्ट रूप से अद्वितीय फ़ील्ड के साथ विस्तारित करना है, जिसके लिए पीके बिल्कुल सही है:

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

और अनुरोध बदल जाता है:

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

#2. "कर्सर" पर स्विच करें

कुछ समय बाद, एक डीबीए आपके पास आता है और आपके अनुरोधों से "प्रसन्न" होता है वे अपने OFFSET नियमों के साथ सर्वर को नरक की तरह लोड करते हैं, और सामान्य तौर पर, यह स्विच करने का समय है दिखाए गए अंतिम मान से नेविगेशन. आपकी क्वेरी फिर से बदल जाती है:

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