Dan Levy

प्रश्नोत्तरी: डीप पोस्टग्रेज़ — भाग 2

क्याआप SQL स्टील हैं, या SQL सॉफ़्टी?

दो भागों में से भाग 2। भाग 1 पर वापस जाएँ

मेरे डीप पोस्ट्रेस क्विज़ के भाग 2 में आपका स्वागत है!

यह क्विज़ परिचित और कम‑ज्ञात PostgreSQL सुविधाओं व पेचीदा व्यवहार का मिश्रण कवर करता है: JOIN, NULL, ANSI SQL, ट्रांज़ैक्शन, इंटर्नल्स, इंडेक्सिंग, डेटा सैंपलिंग… और भी बहुत कुछ!

शुभकामनाएँ! 🍀

1. 

वार्म‑अप: Joins

कौन सा ❌ एक वैध PostgreSQL JOIN अभिव्यक्ति नहीं है?

आपको CROSS JOIN के बारे में पता था, है ना?

क्या JOIN ALL आपको किसी दूसरी ऑपरेशन, UNION ALL की याद दिला गया?

यहाँ जवाब है JOIN ALL ❌.

सही JOIN प्रकार हैं:

  • INNER JOIN (डिफ़ॉल्ट JOIN)
  • LEFT JOIN (या LEFT OUTER JOIN)
  • RIGHT JOIN (या RIGHT OUTER JOIN)
  • FULL JOIN (या FULL OUTER JOIN)
  • CROSS JOIN (कार्टेशियन प्रोडक्ट, जब तक बाद में WHERE क्लॉज़ इसे फ़िल्टर न करे)

2. 

आइडेंटिटी कॉलम

PostgreSQL (v10+) में एक ऑटो-इन्क्रीमेंटिंग IDENTITY कॉलम के लिए (SQL Standard) सिंटैक्स क्या है?

SERIAL अभी भी आम तौर पर उपयोग किया जाता है, SQL Standard तरीका है:

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • id INT IDENTITY(1,1) SQL Server सिंटैक्स है।
  • id INT AUTO_INCREMENT MySQL सिंटैक्स है。
  • id INTEGER AUTO_INCREMENT PRIMARY KEY MySQL सिंटैक्स है。

यह SERIAL की तुलना में कई फायदे देता है:

  • यह SQL Standard के अनुरूप है
  • सीक्वेंस स्वामित्व का बेहतर प्रबंधन
  • मैन्युअल इन्सर्ट्स के आसपास स्पष्ट अर्थ
  • रेप्लिकेशन के साथ बेहतर काम करता है

SERIAL वास्तव में एक सीक्वेंस बनाने और डिफ़ॉल्ट वैल्यू सेट करने का शॉर्टहैंड है, जिससे डंप और रिस्टोर में समस्याएँ हो सकती हैं।

3. 

ANSI SQL

PostgreSQL के दस्तावेज़ किस क्लॉज़ को मानक SQL का विस्तार बताते हैं?

PostgreSQL अपने RETURNING क्लॉज़ को मानक SQL का विस्तार बताता है। ILIKE भी PostgreSQL का एक विस्तार है।

सभी array या JSON सुविधाओं को गैर-मानक न मानें: SQL ने इन क्षेत्रों की सुविधाएँ मानकीकृत की हैं, जबकि PostgreSQL अपने प्रकार और ऑपरेटर भी देता है, जैसे jsonb।

SQL मानक के अलग-अलग संस्करणों में शामिल सुविधाएँ हैं:

  • WITH (कॉमन टेबल एक्सप्रेशन)।
  • पैटर्न मिलान के लिए LIKE।
  • LATERAL जॉइन।
  • विंडो फ़ंक्शन।

4. 

लेन‑देन

“साइड इफ़ेक्ट्स पर निर्भर करता है” को छोड़कर त्रुटि के बाद क्या होता है?

BEGIN;
UPDATE students
  SET status = 'alumni'
  WHERE grade = 12;
UPDATE students
  SET graduation_date = CURRENT_DATE
  WHERE grade = 12;
UPDATE students
  SET grade = 'GRADUATED'; -- 🚨 Error: type mismatch
COMMIT;

जब यहाँ त्रुटि आती है:

  • पूरे लेन‑देन को रोलबैक किया जाता है
  • कोई परिवर्तन कमिट नहीं होते
  • लेन‑देन को विफल के रूप में चिह्नित किया जाता है

आंशिक कमिट करने के लिए:

BEGIN;
UPDATE ...;
SAVEPOINT my_save;
UPDATE ...; -- Error
ROLLBACK TO my_save;
COMMIT;

5. 

SQL आंतरिक

PostgreSQL HASH JOIN के लिए कौन सी ON शर्त का उपयोग कर सकता है?

SELECT *
FROM students a
JOIN grades b
ON /* What goes here? ⁉ */;

हैश जॉइन ON क्लॉज़ में समानता (=) शर्त के साथ संभव है।

  • = हैश जॉइन के लिए मुख्य प्रेडिकेट रूप है
  • > और >= हैश-जॉइन प्रेडिकेट नहीं हैं
  • HASH JOIN PostgreSQL में वैध सिंटैक्स नहीं है

योजनाकार अभी भी टेबल आकार, आँकड़े, इंडेक्स और मेमोरी सेटिंग्स के आधार पर नेस्टेड लूप या मर्ज जॉइन चुन सकता है।

6. 

इंडेक्सिंग

इस क्वेरी के लिए कौन सा इंडेक्स बेहतर है?

-- Query:
SELECT * FROM students
WHERE grade_level = 42
  AND last_name IN ('Levy', 'Smith');

मेरे लिए यहाँ आश्चर्यजनक बात यह है कि बहु-स्तंभ इंडेक्स का उपयोग तब भी किया जा सकता है जब क्वेरी टेक्स्ट में कॉलम क्रम इंडेक्स परिभाषा से मेल नहीं खाता।

नोट: B‑tree इंडेक्स में अग्रणी कॉलम महत्वपूर्ण होता है। चूँकि यह क्वेरी grade_level और last_name द्वारा फ़िल्टर करती है, grade_level से शुरू होकर फिर last_name वाला इंडेक्स उपयुक्त है।

जितनी तेज़ SQL कम पंक्तियों का चयन कर सके, प्रदर्शन उतना ही बेहतर होगा।

उदाहरण के लिए, यदि आपके पास एक task टेबल है, और आप जानते हैं कि status created_at से अधिक चयनात्मक है, तो आप status = 'done' AND created_at > '2024-01-01' जैसी क्वेरी के लिए status से शुरू होने वाला इंडेक्स चुन सकते हैं। WHERE क्लॉज़ टेक्स्ट में शर्तों का क्रम मायने नहीं रखता; महत्वपूर्ण है इंडेक्स कॉलम क्रम और प्रेडिकेट की संरचना।

एक B+Tree इंडेक्स का उपयोग किया जा सकता है:

  • अग्रणी कॉलम, या कॉलमों का सबसे बाईं ओर से शुरू होने वाले लगातार कॉलम।
  • =, >, <, BETWEEN, और IN जैसे ऑपरेटर, क्वेरी के रूप पर निर्भर करता है।
  • प्रिफिक्स मिलान, LIKE 'prefix%'।

इस व्याख्या में पहले की गलती को इंगित करने के लिए u/mwdb2 का धन्यवाद!

7. 

उद्धरण 1

केस-सेंसिटिव कॉलम नामों के लिए कौन सा सही है?

CREATE TABLE grades (
  "Student Name" VARCHAR(100),
  'Student Grade' INTEGER
);

PostgreSQL में:

  • डबल कोट्स (") पहचानकर्ताओं (टेबल नाम, कॉलम नाम) के लिए होते हैं
  • सिंगल कोट्स (') स्ट्रिंग लिटरल्स के लिए होते हैं

सर्वोत्तम अभ्यास:

  • नामों में मिश्रित केस या स्पेस से बचें
  • पहचानकर्ताओं के लिए snake_case उपयोग करें
  • केवल तभी कोटेड पहचानकर्ता उपयोग करें जब बिल्कुल आवश्यक हो

8. 

कोटिंग 2

आप बिंदु (.) वाले कॉलम नाम को कैसे निर्दिष्ट करेंगे?

CREATE TABLE teachers (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  first.name TEXT,
  -- How do you include the column: first.name?
  salary NUMERIC
);

PostgreSQL पहचानकर्ता कोटिंग के लिए डबल कोट्स का उपयोग करता है:

  • पहचानकर्ताओं (कॉलम नाम, टेबल नाम) के लिए डबल कोट्स "..."
  • स्ट्रिंग लिटरल्स के लिए सिंगल कोट्स '...'
  • रूप first.name को table_name.column_name के रूप में समझा जाता है

बिना कोट्स के, कॉलम नाम में बिंदु को स्कीमा/टेबल विभाजक माना जाएगा! अतिरिक्त रूप से, सभी बिना कोट वाले पहचानकर्ता डिफ़ॉल्ट रूप से लोअर‑केस में बदल जाते हैं.

आश्चर्य कम करने के लिए, snake_case का उपयोग करना और विशेष अक्षरों से बचना बेहतर है.

9. 

डेटा सैंपलिंग

कौन सा वैध सिंटैक्स है रैंडम रूप से 10% छात्रों का सैंपल लेने के लिए?

यह थोड़ा tricky है! इसमें लगभग 6 जाल हैं, कुछ इस प्रकार:

  1. उच्च‑स्तरीय विवरण + सिंटैक्स के टुकड़े। यह देखता है कि आप अधूरी जानकारी को कैसे जोड़‑जोड़ कर उपयोग करते हैं।
  2. TABLESAMPLE सिंटैक्स का परिचित ज्ञान चाहिए, क्योंकि TABLESAMPLE विकल्पों में से कोई भी सही सिंटैक्स नहीं देता! (भयानक! मैं जानता हूँ! आगे बढ़ते रहो, मैं तुम पर भरोसा करता हूँ!!! ❤️)
  3. ROW_NUMBER() OVER (ORDER BY RAND()) और ORDER BY RANDOM() > 10 सिर्फ भटकाने वाले विकल्प हैं। वे सही लग सकते हैं, लेकिन नहीं हैं। RAND() PostgreSQL में नहीं है। और RANDOM() > 10 एक बूलियन अभिव्यक्ति है जो हमेशा false होगी क्योंकि RANDOM() का मान हमेशा 0.0-1.0 के बीच रहता है। एक और समस्या यह है कि ORDER BY RANDOM() > 10 बूलियन मान पर सॉर्ट करेगा, न कि रैंडम नंबर पर। लेकिन > 10 को प्रतिशत समझना स्वाभाविक लग सकता है, है ना?
  4. BERNOULLI वैध है, लेकिन SAMPLETABLE नहीं है।
  5. WHERE RANDOM() >= 0.1 एक चाल है! RANDOM() का मान 0.0 <= x < 1.0 के बीच होता है, इसलिए >= 0.1 लगभग 90% पंक्तियों को लौटाएगा, 10% नहीं।

TABLESAMPLE मेथड के बारे में, PostgreSQL दो सैंपलिंग मेथड सपोर्ट करता है:

  • BERNOULLI: प्रत्येक पंक्ति की समान संभावना
  • SYSTEM: ब्लॉक‑लेवल सैंपलिंग (तेज़ लेकिन कम रैंडम)

सैंपलिंग के बारे में अधिक जानने के लिए, इस लेख को देखें: Render।

उदाहरण:

-- Sample 10% of rows
SELECT * FROM students TABLESAMPLE BERNOULLI (10);


-- For repeatable results, use REPEATABLE:
SELECT * FROM students
TABLESAMPLE BERNOULLI (10) REPEATABLE (42);

यह भी उल्लेखनीय है कि TABLESAMPLE को रिपोर्ट किया गया है कि वह बहुत रैंडम नहीं है। यदि आपको एक और रैंडम फिक्स्ड‑साइज़ सैंपल चाहिए, तो ORDER BY RANDOM() LIMIT 10 का उपयोग करने पर विचार करें। लेकिन ध्यान रखें कि यह 10 पंक्तियाँ लौटाता है, 10% नहीं, और बड़े टेबल पर धीमा हो सकता है।

कुछ थर्ड‑पार्टी एक्सटेंशन बेहतर रैंडम सैंपलिंग मेथड प्रदान करते हैं, लेकिन वह इस संदर्भ से बाहर है! 😅

10. 

सूचकांक

यह जादू क्या करता है?

CREATE INDEX idx_active_students
ON students(last_name)
WHERE status = 'active';

इस ट्रिक को Partial Index कहा जाता है।

ये डिस्क उपयोग कम करने और क्वेरी को तेज़ करने में मदद करते हैं जब सामान्यतः केवल पंक्तियों का एक उपसमुच्चय पर क्वेरी की जाती है।

प्लानर इस इंडेक्स पर तब विचार कर सकता है जब वह साबित कर सके कि क्वेरी की शर्तों से status = 'active' अनिवार्य रूप से सत्य होता है। हूबहू समान पाठ आवश्यक नहीं है, लेकिन प्लानर की तर्क-जाँच सीमित है; पैरामीटर वाली शर्त योजना बनाते समय इसे साबित न कर पाए। योग्य होने पर भी इंडेक्स का चुना जाना निश्चित नहीं है।

याद रखें: Postgres अन्य RDBMS की तरह ‘query hints’ का समर्थन नहीं करता। क्वेरी प्लानर तय करेगा कि कब कोई उपयुक्त सूचकांक (इंडेक्स) उपयोग किया जाए।

11. 

NULL व्यवहार

यह क्वेरी कितनी पंक्तियों लौटाएगी?

SELECT COUNT(*)
FROM students
WHERE grade = NULL;

ठीक है, यह थोड़ा tricky था। बात यह है कि x = NULL कभी भी किसी पंक्ति से मेल नहीं खाएगा, और यह हमेशा 0 की गिनती के साथ एक पंक्ति लौटाएगा।

चूँकि NULL एक अज्ञात मान है, इसे किसी भी चीज़ से तुलना नहीं की जा सकती, यहाँ तक कि स्वयं से भी नहीं।

NULL मानों की जाँच करने का सही तरीका IS NULL है।

उदाहरण:

postgres=# SELECT count(*)
  FROM students
  WHERE age = null;
count
-------
    0
(1 row)


postgres=#
postgres=# SELECT count(*)
  FROM students
  WHERE age is null;
count
-------
    3
(1 row)

12. 

प्रदर्शन

EXPLAIN ANALYZE UPDATE कथनों के लिए क्या करता है?

EXPLAIN ANALYZE
UPDATE students
SET grade = grade + 1
WHERE status = 'active';

EXPLAIN ANALYZE वास्तव में क्वेरी को चलाता है! लिखने वाले ऑपरेशन का विश्लेषण करते समय सावधान रहें, विशेष रूप से UPDATE, DELETE, और INSERT कथनों का। प्रत्येक EXPLAIN ANALYZE के बाद दोहराने योग्य परिणाम पाने के लिए आपको सफाई कार्य चलाने पड़ सकते हैं।

सर्वोत्तम प्रथाएँ:

  • UPDATE/DELETE/INSERT के लिए EXPLAIN (बिना ANALYZE) उपयोग करें
  • संशोधनों को लेन‑देन में घेरें:
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

शाबाश! आपने PostgreSQL के कई पहलुओं में गहराई तक पहुँच बना ली! 🐘

आशा है कुछ नया सीख पाए हों, या कम से कम स्कोर तो मिला हो ग़ौर करने के लिये! 🏆

और रोमांच चाहिए? मेरे क्विज़ संग्रह में झाँकें—असीमित मज़ा आपका इंतज़ार कर रहा है!

Loading comments...