Рекурсивные запросы в PostgreSQL: приёмы без ловушек

Рекурсивный запрос в PostgreSQL может вернуть всё поддерево за один SQL-вызов, но неограниченный обход легко превращает ошибку в бесконечную работу или тяжёлый запрос. Ниже — три приёма для PostgreSQL 16: ограниченный обход дерева, защита от циклов и сбор пути для каждого узла.

Сначала задайте границы обхода

Рекурсивный CTE состоит из начальной выборки и шага, который присоединяет новые строки к уже найденным. PostgreSQL повторяет шаг, пока он возвращает результаты. В примерах предполагается таблица category со столбцами id integer, parent_id integer и name text. Внешний ключ на parent_id не запрещает циклы: запись может оказаться собственным предком через несколько строк.

Для обычного дерева нужны два ограничения: точка старта и максимальная глубина. Индекс на столбце связи помогает находить дочерние строки без полного сканирования таблицы на каждом шаге:

CREATE INDEX category_parent_id_idx ON category (parent_id);

Если индекс уже есть, второй такой же создавать не нужно. В примерах ниже $1 — ID стартовой категории, а $2 — максимальная глубина обхода.

Приём 1: получить потомков до заданной глубины

Начальная строка включает саму стартовую категорию с глубиной 0. Рекурсивная часть добавляет детей, пока текущая глубина меньше переданного лимита.

WITH RECURSIVE descendants AS (
    SELECT id, parent_id, 0 AS depth
    FROM category
    WHERE id = $1

    UNION ALL

    SELECT c.id, c.parent_id, d.depth + 1
    FROM category AS c
    JOIN descendants AS d ON c.parent_id = d.id
    WHERE d.depth < $2
)
SELECT id, parent_id, depth
FROM descendants
ORDER BY depth, id;

Если $2 равен 3, результат включает стартовый узел, детей, внуков и правнуков. Отрицательное значение лимита оставит только стартовую строку, если она существует; неизвестный ID не вернёт строк. Проверьте это на маленьком поддереве и сравните число строк с ожидаемым.

UNION ALL здесь предпочтительнее UNION: он не тратит ресурсы на удаление повторов. Заменять его на UNION ради защиты от цикла опасно. Если в результат входят глубина или путь, повторно найденный узел будет отличаться по этим столбцам, поэтому дедупликация не остановит обход.

Приём 2: остановить ветку, если в ней найден цикл

Для PostgreSQL 14 и новее доступна конструкция CYCLE; пример рассчитан на PostgreSQL 16. Она отслеживает повторение ключа в текущем пути и помечает строку, которая замыкает цикл. Выбирайте ключ, который однозначно определяет узел.

WITH RECURSIVE walk (id, parent_id, depth) AS (
    SELECT id, parent_id, 0
    FROM category
    WHERE id = $1

    UNION ALL

    SELECT c.id, c.parent_id, w.depth + 1
    FROM category AS c
    JOIN walk AS w ON c.parent_id = w.id
)
CYCLE id SET is_cycle USING cycle_path
SELECT id, parent_id, depth, cycle_path
FROM walk
WHERE NOT is_cycle
ORDER BY depth, id;

Строка, которая повторяет ID на своём пути, получает признак is_cycle; ветка через неё дальше не разворачивается. Фильтр убирает эту строку из выдачи, но не исправляет испорченные данные. Если структура большая или глубина должна быть ограничена по бизнес-правилам, добавьте в рекурсивную часть условие на глубину: защита от циклов не заменяет лимит работы.

В графе один узел может достигаться разными маршрутами. CYCLE id считает циклом повтор узла внутри конкретного пути, а не повтор узла где-либо во всём результате. Поэтому при множестве маршрутов число строк может быстро расти, даже если циклов нет.

Если запросу нужна одна строка на узел, не путайте «не ходить по кругу» с «посетить каждый узел ровно один раз». Это разные правила обхода, и простой рекурсивный CTE не превращает граф в дерево.

Приём 3: собрать путь от корня к каждому узлу

Путь полезен для хлебных крошек, отладки и объяснения, почему узел попал в выборку. В этом варианте массив labels накапливает названия, а CYCLE отдельно защищает рекурсию. Здесь предполагается, что имя категории хранится в текстовом столбце name.

WITH RECURSIVE walk (id, parent_id, name, depth, labels) AS (
    SELECT id, parent_id, name, 0, ARRAY[name]::text[]
    FROM category
    WHERE id = $1

    UNION ALL

    SELECT c.id, c.parent_id, c.name,
           w.depth + 1, w.labels || c.name
    FROM category AS c
    JOIN walk AS w ON c.parent_id = w.id
    WHERE w.depth < $2
)
CYCLE id SET is_cycle USING cycle_path
SELECT id, parent_id, name, depth, labels
FROM walk
WHERE NOT is_cycle
ORDER BY depth, id;

labels будет массивом от стартовой категории к найденной. Это не то же самое, что путь от корня дерева: если передать в $1 узел в середине иерархии, запрос начнёт путь с него. Для настоящих хлебных крошек сначала определите, где находится корень нужной ветки, или обходите связи вверх.

Какой вариант выбрать

Что обычно ломается

Слишком широкая начальная выборка

Если стартовая часть выбирает все строки, а не один заданный узел, запрос запускает обход сразу из нескольких точек. Проверьте условие в начальном SELECT и оцените число стартовых строк до добавления рекурсии.

Глубина не ограничена, хотя данные растут

Даже корректное дерево может оказаться гораздо глубже, чем предполагалось. Предел в запросе помогает контролировать работу и выявлять неожиданные данные. Выбирайте его по правилам приложения, а не как случайное «достаточно большое» число.

Нет индекса на связи

На каждом шаге PostgreSQL ищет строки, где parent_id совпадает с текущим ID. Без подходящего индекса такие поиски могут стать дорогими. Проверьте план через EXPLAIN (ANALYZE, BUFFERS) на данных, похожих на рабочие: команда с ANALYZE действительно выполняет запрос, поэтому не запускайте её бездумно на тяжёлой операции в продакшене.

UNION принят за универсальную защиту

UNION удаляет одинаковые строки результата, а не любые повторы одного ID. Меняется глубина или накопленный путь — строка уже не равна предыдущей. Для циклов используйте CYCLE, а для ограничения объёма — явный предел глубины.

Термин. Рекурсивный CTE — временный результат запроса, который строится итерациями: следующий шаг читает строки, найденные на предыдущем. В PostgreSQL он задаётся через WITH RECURSIVE; полученные строки доступны в рамках этого же SQL-запроса.

Что делать дальше

Возьмите небольшой фрагмент реальных данных и проверьте три сценария: обычную ветку, отсутствующий ID и искусственно замкнутый цикл. Затем измерьте запрос на реалистичном объёме с EXPLAIN (ANALYZE, BUFFERS), проверьте использование индекса по parent_id и число возвращённых строк. Если нужна одна строка на узел графа, а не все маршруты, отдельно продумайте дедупликацию и правила выбора пути: простое накопление путей может дать слишком большой результат.