PostgreSQL में CTEs और recursive queries कैसे काम करती हैं?

Question

Accepted Answer

एक **CTE** (Common Table Expression), जिसे **`WITH`** से परिभाषित किया जाता है, एक नामित अस्थायी परिणाम-सेट है जो जटिल queries को पठनीय बनाता है। PostgreSQL CTEs का पूरी तरह समर्थन करता है, जिसमें hierarchical/tree data को traverse करने के लिए **recursive** CTEs (`WITH RECURSIVE`) शामिल हैं।

## मूल CTEs — पठनीय query निर्माण

```sql
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
order_counts AS (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT u.name, oc.cnt
FROM active_users u
JOIN order_counts oc ON u.id = oc.user_id;   -- compose named steps cleanly
```

CTEs एक जटिल query को नामित, पठनीय चरणों में तोड़ते हैं (श्रृंखलाबद्ध, प्रत्येक पिछले को संदर्भित करते हुए) — nested subqueries की तुलना में कहीं अधिक स्पष्ट।

## Recursive CTEs — hierarchies को traverse करना

```sql
-- find all subordinates under employee 1 (an org-chart hierarchy)
WITH RECURSIVE subordinates AS (
  -- BASE CASE: the starting point
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  -- RECURSIVE STEP: find employees whose manager is in the result so far
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;          -- the entire tree under employee 1
```

एक **recursive CTE** में एक base case (शुरुआती rows) और एक recursive step (`UNION ALL` जो CTE से ही वापस जुड़ता है) होता है, जो तब तक iterate करता है जब तक कोई नई rows न रहें — **trees और hierarchies** (org charts, category trees, bill-of-materials, graph paths) के लिए एकदम सही।

## CTE materialization पर एक नोट (Postgres-विशिष्ट)

```sql
-- Postgres 12+ may INLINE CTEs (optimize them) by default; force behavior if needed:
WITH x AS MATERIALIZED (...)      -- force materialization (compute once, store)
WITH x AS NOT MATERIALIZED (...)  -- force inlining (optimize into the main query)
```

Postgres 12 से पहले, CTEs हमेशा एक "optimization fence" (materialized) होते थे; अब बेहतर performance के लिए इन्हें inline किया जा सकता है — `MATERIALIZED`/`NOT MATERIALIZED` इसे नियंत्रित करता है।

## यह क्यों महत्वपूर्ण है

CTEs और recursive queries **पठनीयता और hierarchical data** के लिए मूल्यवान PostgreSQL features हैं, इसलिए इन्हें समझना रखरखाव योग्य जटिल queries लिखने और tree-संरचित data को संभालने के लिए उपयोगी है।

रोज़मर्रा का लाभ **पठनीयता** है: कई subquery स्तरों वाली जटिल queries रखरखाव में कठिन हो जाती हैं, जबकि CTEs आपको उन्हें नामित, क्रमिक तार्किक चरणों में विघटित करने देते हैं (ऊपर से नीचे पढ़े जाने वाले), जिससे स्पष्टता में नाटकीय सुधार होता है — जो CTEs को रखरखाव योग्य SQL लिखने के लिए एक महत्वपूर्ण उपकरण बनाता है।

प्रमुख क्षमता **recursive CTEs** (`WITH RECURSIVE`) है, जो **hierarchical और tree-संरचित data** (organizational charts, category trees, bill-of-materials, threaded comments, graph traversal) को query करना सक्षम बनाती है — queries का एक वर्ग जो अन्यथा SQL में बहुत कठिन है, जो recursive CTEs को hierarchical traversal के लिए मानक समाधान बनाता है।

recursive CTE संरचना (base case + recursive step जो खुद से वापस जुड़ता है) को समझना इस आम आवश्यकता के लिए मुख्य ज्ञान है।

एक उपयोगी Postgres-विशिष्ट विवरण **CTE materialization** व्यवहार है (Postgres 12+ बेहतर performance के लिए CTEs को inline कर सकता है, जिसे `MATERIALIZED`/`NOT MATERIALIZED` से नियंत्रित किया जा सकता है) — optimization के लिए प्रासंगिक।

चूँकि जटिल queries आम हैं (और रखरखाव के लिए पठनीयता मायने रखती है), और चूँकि hierarchical data (जिसे recursive CTEs विशिष्ट रूप से अच्छी तरह संभालते हैं) अक्सर सामने आता है (org structures, nested categories), CTEs और recursive queries को समझना मूल्यवान, व्यावहारिक रूप से प्रासंगिक PostgreSQL ज्ञान है जो query रखरखाव में सुधार करता है और hierarchical querying को unlock करता है — साफ़, परिष्कृत SQL के लिए एक अक्सर-प्रासंगिक क्षमता और एक आम विषय, खासकर tree-संरचित data को query करने की बार-बार आने वाली आवश्यकता के लिए recursive रूप।