PostgreSQL

CTE и WITH RECURSIVE

Таблица categories с parent_id. Запрос: найти все подкатегории категории «Электроника» на любой глубине. Без рекурсивного CTE - это либо N запросов в приложении, либо хранимая процедура с циклом, либо денормализация. Один рекурсивный CTE решает это в стандартном SQL. А для графов с циклами - тот же инструмент с небольшим дополнением.

**Org chart (HR системы):** кто подчиняется менеджеру X, на всех уровнях иерархии - WITH RECURSIVE за один запрос
**Маршрутизация (логистика):** найти все возможные пути доставки из A в B - обход графа через CTE
**Финансы (счета):** транзитивное замыкание зависимостей счетов для проверки circular references

CTE: синтаксис и когда применять

**CTE (Common Table Expression)** - именованный подзапрос, определённый в начале запроса через `WITH`. Результат CTE можно использовать несколько раз в основном запросе, как если бы это была временная таблица. Главная ценность - читаемость: сложный запрос разбивается на именованные шаги.

**CTE vs подзапрос в FROM:** синтаксически они взаимозаменяемы для простых случаев. CTE выигрывает когда: (1) один и тот же подзапрос нужен несколько раз, (2) запрос нужно читать сверху вниз как алгоритм, (3) нужна рекурсия.

Главное преимущество CTE перед эквивалентным подзапросом в FROM:

Рекурсивные CTE

С ключевым словом `RECURSIVE` CTE может ссылаться сам на себя. Это единственный способ в стандартном SQL делать итеративные/рекурсивные вычисления без процедурного кода. Структура всегда одинакова: base case (anchor) + рекурсивный шаг, объединённые через `UNION ALL`.

**Предотвращение бесконечной рекурсии:** всегда добавляйте ограничение в WHERE или используйте `LIMIT`. Для графов с циклами - отслеживайте путь через массив посещённых вершин. PostgreSQL не имеет встроенной защиты от infinite loop в рекурсивных CTE.

В рекурсивном CTE какую роль играет anchor (первая часть до UNION ALL)?

Иерархические данные с RECURSIVE

Организационная иерархия, категории с подкатегориями, дерево комментариев - все эти структуры хранятся как таблица с `parent_id`. Обход такого дерева без RECURSIVE CTE требовал N запросов или денормализации. RECURSIVE даёт это в одном запросе.

**Альтернативы для больших иерархий:** Ltree extension (PostgreSQL) - хранит путь как `'1.5.23.104'`, поддерживает индексированные запросы на поддеревья. Для readonly иерархий Nested Set (left/right числа) даёт O(1) запросы на поддерево без рекурсии.

В рекурсивном CTE для обхода иерархии мы добавляем массив `path || current_id`. Зачем?

Обход графа через CTE

Граф (в отличие от дерева) может содержать циклы: A -> B -> C -> A. Без защиты рекурсивный CTE уйдёт в бесконечный цикл. Стандартный приём - отслеживать массив посещённых вершин и фильтровать уже посещённые.

**UNION vs UNION ALL в RECURSIVE CTE:** `UNION ALL` - быстрее, добавляет все строки. `UNION` - удаляет дубликаты, может использоваться для защиты от циклов вместо массива visited, но медленнее на больших графах.

Рекурсивный CTE обходит граф без защиты от циклов. Что произойдёт при наличии цикла A->B->C->A?

MATERIALIZED vs не-MATERIALIZED

До PostgreSQL 12 все CTE были **optimization fences**: планировщик не мог «заглянуть внутрь» CTE и применить внешние условия WHERE для оптимизации. CTE выполнялся полностью и кешировался. С PostgreSQL 12 поведение изменилось - CTE по умолчанию не материализуется, если используется ровно один раз.

Практическое правило: если CTE с `VOLATILE` функцией (`random()`, `now()`, `clock_timestamp()`) используется несколько раз - обязательно `MATERIALIZED`. Иначе каждое использование вызовет функцию заново, давая разные результаты.

**EXPLAIN проверка:** `EXPLAIN (ANALYZE)` покажет `CTE Scan` (материализованный CTE) или инлайненный подзапрос. `CTE Scan` всегда значит материализацию. Если видите `CTE Scan` для CTE, который используется один раз - добавьте `NOT MATERIALIZED` или перепишите в подзапрос.

CTE - это всегда optimization fence: планировщик не может их оптимизировать

До PG12 - да. С PG12+ CTE по умолчанию инлайнятся (NOT MATERIALIZED), если используются один раз. Keyword MATERIALIZED явно включает старое поведение.

Многие советы «использовать CTE как optimization fence» были написаны до PG12. В современном PostgreSQL нужно явно писать MATERIALIZED если нужно кеширование или изоляция от планировщика.

CTE содержит `random()` и используется в двух местах запроса. Без `MATERIALIZED` что произойдёт в PostgreSQL 12+?