Иерархии в PostgreSQL: ошибки, которые дорого исправлять

Одна таблица — не всегда всё дерево

В PostgreSQL 16 иерархию часто начинают хранить через ссылку строки на её родителя. Для каталога с редкими перемещениями это просто и удобно, но внешний ключ не запрещает циклы, а запрос потомков без индекса может читать таблицу снова и снова. Если же выбрать более сложную модель «на вырост», платить за её обновление придётся при каждой перестановке узлов.

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

Ошибка № 1: считать внешний ключ защитой от циклов

Для adjacency list — списка смежности — каждой записи назначают родителя в той же таблице. Внешний ключ не даст сослаться на несуществующий узел, но разрешит сделать так, что A — родитель B, а B — родитель A. Ограничение, запрещающее узлу быть собственным непосредственным родителем, не ловит цикл из нескольких записей.

CREATE TABLE category (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    parent_id bigint REFERENCES category(id) ON DELETE RESTRICT,
    name text NOT NULL,
    CHECK (parent_id IS DISTINCT FROM id)
);

CREATE INDEX category_parent_id_idx ON category (parent_id);

Индекс на parent_id нужен для поиска детей и обхода дерева. PostgreSQL не создаёт его автоматически только потому, что столбец участвует во внешнем ключе. Если часто выводите соседние элементы в пользовательском порядке, может понадобиться составной индекс по родителю, полю сортировки и идентификатору.

Рекурсивный запрос в PostgreSQL 16 умеет выявлять циклы с помощью CYCLE. Для поиска ветки от указанного узла запрос выглядит так:

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

    UNION ALL

    SELECT child.id, child.parent_id, child.name, parent.depth + 1
    FROM category AS child
    JOIN subtree AS parent ON child.parent_id = parent.id
)
CYCLE id SET is_cycle USING path
SELECT id, parent_id, name, depth, is_cycle
FROM subtree
ORDER BY path;

Параметр $1 передавайте как параметр подготовленного запроса, а не подставляйте в SQL строкой. Поле is_cycle помогает обнаружить повторное посещение узла. Не отбрасывайте такие строки молча в диагностическом запросе: иначе можно не заметить испорченные данные.

Ошибка № 2: разрешать перенос без проверки всей ветки

При переносе узла под нового родителя проверка «родитель не равен самому узлу» недостаточна. Нельзя назначить родителем потомка: так существующая ветка замкнётся в цикл. Перед обновлением проверьте, входит ли новый родитель в поддерево переносимого узла.

WITH RECURSIVE descendants(id) AS (
    SELECT id
    FROM category
    WHERE id = $1

    UNION

    SELECT child.id
    FROM category AS child
    JOIN descendants AS parent ON child.parent_id = parent.id
)
SELECT EXISTS (
    SELECT 1
    FROM descendants
    WHERE id = $2
) AS would_create_cycle;

Здесь $1 — переносимый узел, $2 — предполагаемый новый родитель. UNION убирает повторы и не даёт запросу зациклиться, если данные уже повреждены; на больших ветках дедупликация тоже стоит ресурсов. Если результат true, перенос создавать нельзя.

Есть ещё гонка: две параллельные транзакции могут одновременно проверить разные перемещения, каждая не увидеть цикла, а вместе создать его. Если перемещения выполняются конкурентно, одной проверки перед UPDATE мало. Сериализуйте такие операции общей блокировкой или другим подходящим для приложения механизмом, а после обновления проверяйте целостность. Какую именно блокировку использовать, зависит от того, какие участки дерева меняются параллельно.

Ошибка № 3: выбирать модель, не считая цену изменений

Adjacency list — не единственный вариант. Таблица замыкания хранит пары «предок — потомок» и глубину между ними; ltree представляет путь как значение специального типа; nested sets кодируют поддеревья границами. Запросы на чтение и обновление у этих схем устроены по-разному.

У ltree есть операторы для поиска по отношениям предка и потомка и индексная поддержка. Но это не «бесплатный ускоритель»: перенос узла меняет путь у него и потомков. Для таблицы замыкания другая цена — количество связей может расти намного быстрее числа узлов. Оцените реальные чтения и изменения, прежде чем выбирать схему только по скорости одного запроса.

Выбирайте иерархию не по самому красивому SELECT, а по самой дорогой операции в вашем приложении: чтению поддерева, перемещению ветки или поддержанию целостности.

Ошибка № 4: забывать, что удаление тоже проходит по дереву

Внешний ключ может ограничить удаление родителя, пока на него ссылаются дочерние строки, либо каскадно удалить связанные записи — выбор зависит от заданного действия. ON DELETE CASCADE удобен для небольших зависимостей, но в большом дереве удаление одного верхнего узла способно убрать целую ветку. Если удаление должно быть запрещено или сначала подтверждаться отдельно, задайте это явно и проверьте на тестовом дереве.

Не смешивайте удаление записи с удалением поддерева в бизнес-логике. Перед массовой операцией полезно сначала получить список затрагиваемых узлов и оценить его размер. Для большой ветки учитывайте время транзакции, блокировки и нагрузку на связанные таблицы. Если в других таблицах хранятся ссылки на узлы, каскадное удаление может затронуть не только саму иерархию.

Как выбрать и что проверять

Adjacency list — разумный старт, если узлы часто добавляют и перемещают, а обходы не требуют сложной оптимизации. Таблица замыкания полезна, когда приложению постоянно нужны предки и потомки, и вы готовы обслуживать дополнительные связи. ltree стоит рассматривать для моделей, где путь — важное поле и его изменение можно контролировать. Nested sets имеют смысл для стабильных деревьев с преобладанием чтения.

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

• попытка назначить узлу самого себя или собственного потомка родителем;
• обход пустого узла и очень глубокой ветки;
• перенос поддерева, которое содержит много записей;
• удаление родителя с дочерними узлами и внешними ссылками;
• два одновременных перемещения в пересекающихся частях дерева.

Термин. Таблица замыкания — отдельная таблица, где для каждой пары «предок — потомок» записывают связь, часто вместе с расстоянием между ними. Это ускоряет поиск связей по дереву, но увеличивает объём данных и усложняет обновления.

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

Соберите несколько типичных запросов приложения: получить детей, полный путь к корню, всё поддерево, переместить узел, удалить ветку. Замерьте их на данных, похожих на производственные: важны размер дерева, его глубина и частота изменений. Для списка смежности начните с индекса по родителю и проверки циклов; если именно обходы становятся узким местом, сравните его с таблицей замыкания или ltree на тех же сценариях. Синтаксис рекурсивных запросов и особенности расширения сверяйте с документацией PostgreSQL 16.