PostgreSQL-এ CTEs এবং recursive queries কীভাবে কাজ করে?

Question

Accepted Answer

একটি **CTE** (Common Table Expression), **`WITH`** দিয়ে সংজ্ঞায়িত, একটি নাম বিশিষ্ট অস্থায়ী ফলাফল সেট যা জটিল queries-কে পাঠযোগ্য করে তোলে। PostgreSQL সম্পূর্ণভাবে CTEs সমর্থন করে, যার মধ্যে **recursive** CTEs (`WITH RECURSIVE`) রয়েছে যা hierarchical/tree ডেটা ট্র্যাভার্স করার জন্য।

## Basic 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 ট্র্যাভার্স করুন

```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 নিজের সাথে যুক্ত হয়) থাকে, যতক্ষণ না নতুন কোনো row আসে — এটি **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 এর জন্য inlined হতে পারে — `MATERIALIZED`/`NOT MATERIALIZED` এটি নিয়ন্ত্রণ করে।

## কেন এটি গুরুত্বপূর্ণ

CTEs এবং recursive queries হল মূল্যবান PostgreSQL বৈশিষ্ট্য যা **পাঠযোগ্যতা এবং hierarchical ডেটা** এর জন্য, তাই এগুলি বোঝা রক্ষণাবেক্ষণযোগ্য জটিল queries লেখা এবং tree-structured ডেটা পরিচালনা করার জন্য উপকারী।

দৈনন্দিন সুবিধা হল **পাঠযোগ্যতা**: একাধিক subquery স্তরের সাথে জটিল queries রক্ষণাবেক্ষণ করা কঠিন হয়ে ওঠে, যখন CTEs আপনাকে সেগুলিকে নাম বিশিষ্ট, ক্রমিক logical steps এ বিভক্ত করতে দেয় (উপরে থেকে নিচে পড়ুন), স্পষ্টতা নাটকীয়ভাবে উন্নত করে — CTEs-কে রক্ষণাবেক্ষণযোগ্য SQL লেখার জন্য একটি গুরুত্বপূর্ণ হাতিয়ার করে তোলে।

দুর্দান্ত ক্ষমতা হল **recursive CTEs** (`WITH RECURSIVE`), যা **hierarchical এবং tree-structured ডেটা** (organizational charts, category trees, bill-of-materials, threaded comments, graph traversal) query করতে সক্ষম করে — এটি SQL-এ অন্যথায় অত্যন্ত কঠিন queries-এর একটি শ্রেণী, যা recursive CTEs-কে hierarchical traversal এর জন্য আদর্শ সমাধান করে তোলে।

Recursive CTE কাঠামো (base case + recursive step যা নিজের সাথে যুক্ত হয়) বোঝা এই সাধারণ প্রয়োজনের জন্য মূল জ্ঞান।

একটি উপকারী Postgres-নির্দিষ্ট বিস্তারিত হল **CTE materialization** আচরণ (Postgres 12+ ভালো performance এর জন্য CTEs inline করতে পারে, `MATERIALIZED`/`NOT MATERIALIZED` দিয়ে নিয়ন্ত্রণযোগ্য) — অপ্টিমাইজেশনের জন্য প্রাসঙ্গিক।

যেহেতু জটিল queries সাধারণ (এবং রক্ষণাবেক্ষণের জন্য পাঠযোগ্যতা গুরুত্বপূর্ণ), এবং যেহেতু hierarchical ডেটা (যা recursive CTEs অনন্যভাবে ভালভাবে পরিচালনা করে) ঘন ঘন প্রদর্শিত হয় (org structures, nested categories), CTEs এবং recursive queries বোঝা মূল্যবান, ব্যবহারিকভাবে প্রাসঙ্গিক PostgreSQL জ্ঞান যা query রক্ষণাবেক্ষণযোগ্যতা উন্নত করে এবং hierarchical querying আনলক করে — পরিষ্কার, পরিশীলিত SQL এর জন্য একটি ঘন ঘন-প্রাসঙ্গিক ক্ষমতা এবং একটি সাধারণ বিষয়, বিশেষত recursive form tree-structured ডেটা query করার পুনরাবৃত্ত প্রয়োজনের জন্য।