Скрипт миграции на 600 строк сплошного SQL с вложенными SELECT, JOIN и подзапросами — не редкость, а вполне рабочий кошмар из жизни команд, которые поддерживают старые базы и тянут данные в новые сервисы. Именно на таких примерах лучше всего видно, зачем разработчикам нужны CTE в PostgreSQL: они не ускоряют мышление магией, но превращают нечитабельную «лапшу» в последовательный и проверяемый код, который можно разобрать без кофеина внутривенно.
В блоге компании OTUS, как пишет Habr / Карьера, об этом рассказал Сергей Прощаев, Tech Lead и руководитель направления Java | Kotlin-разработки в FinTech и e-commerce. Его тезис довольно земной: обобщённые табличные выражения, то есть Common Table Expressions, полезны не потому, что это «продвинутая возможность PostgreSQL», а потому что они возвращают SQL человеческий порядок чтения. Вместо того чтобы мысленно распутывать запрос из глубины наружу, разработчик сначала объявляет промежуточные шаги через WITH, а потом собирает итоговую выборку как конструктор.
Базовый пример у автора намеренно простой: нужно найти сотрудников, чья зарплата выше средней по отделу, и вывести их руководителей. В варианте без CTE среднее значение прячется внутри WHERE в коррелированном подзапросе. В варианте с CTE логика расчёта средней зарплаты выносится в отдельный блок avg_by_dept, после чего основной SELECT просто джойнит готовый результат. Разница здесь не в красоте ради красоты. Когда расчёт назван и вынесен наверх, его можно быстро проверить отдельно, переиспользовать и без мучений показать коллеге на код-ревью. Для команд, где SQL живёт годами и переживает не одного разработчика, это вполне практическая выгода, а не синтаксическая косметика.
Главная мысль материала — CTE в PostgreSQL особенно хорошо работают там, где запрос совпадает с бизнес-логикой в несколько шагов. В примере из e-commerce автор разбивает задачу на три этапа: сначала выбирает активных пользователей за последние 30 дней, затем считает их выручку за тот же период, а после этого подтягивает годовую историю заказов, чтобы разделить клиентов на сегменты лояльности. В монолитном запросе такая логика быстро превращается в трудно читаемый комок из дат, агрегатов и фильтров. В цепочке CTE каждый шаг отвечает на отдельный вопрос: кто активен, кто принёс больше 1000 рублей, у кого какая история за год. Для продактов и аналитиков это тоже неплохая новость: когда SQL отражает этапы бизнес-процесса, спорить о корректности сегментации проще, чем когда вся логика спрятана в одном абзаце из скобок и HAVING.
Отдельно Прощаев разбирает старый страх: будто бы CTE почти всегда проигрывают по производительности. Здесь полезен именно контекст PostgreSQL. Автор напоминает, что оптимизатор часто умеет разворачивать CTE так же, как обычные подзапросы. А если промежуточный результат, наоборот, нужно зафиксировать и не пересчитывать повторно, можно явно использовать MATERIALIZED. Иными словами, вопрос не в том, «можно ли писать красиво без потери скорости», а в том, понимает ли команда, как именно планировщик выполнит запрос. Для backend-разработчиков это важное уточнение: CTE — не индульгенция от анализа EXPLAIN, но и не запретная зона для всего, что хоть немного сложнее селекта по справочнику.
Ещё один сильный фрагмент статьи — напоминание, что CTE пригодны не только для SELECT. На практике это, пожалуй, даже интереснее. Автор приводит два типовых кейса. Первый — удаление дублей, когда через CTE можно пронумеровать записи по пользователю с помощью ROW_NUMBER(), а затем одним DELETE убрать всё, кроме самой свежей строки. Второй — массовое обновление статуса пользователей на основе суммы покупок за прошлый месяц. В обоих случаях логика отбора сначала формулируется как промежуточная таблица, а затем сразу используется в UPDATE или DELETE. Для эксплуатации и data-команд плюс очевидный: меньше ручных двухшаговых операций, меньше риска, что между выборкой и модификацией данные успеют измениться, больше шансов выполнить всё атомарно.
Самая «инженерная» часть — рекурсивные CTE для иерархий. В материале разбирается классическая структура departments с полем parent_id, где нужно развернуть дерево подчинённости от верхнего уровня вниз. Вместо процедурного кода и циклов используется WITH RECURSIVE: сначала задаётся базовый набор корневых узлов, потом через UNION ALL база шаг за шагом находит дочерние элементы. Для всех, кто работает с оргструктурами, каталогами, рубрикаторами, деревьями прав доступа и прочими иерархическими сущностями, это напоминание из разряда «не обязательно писать лишний сервис, если СУБД уже умеет обходить дерево сама». В российских командах, где PostgreSQL часто несёт на себе и продуктовую транзакционку, и внутреннюю аналитику, такой приём может сэкономить не только строки кода, но и пару лишних архитектурных сущностей.
На уровне рынка и практики статья хорошо попадает в текущий нерв: SQL снова перестал быть «языком только для аналитиков» и вернулся в повседневный инструментарий backend-разработчиков, data-инженеров и тимлидов. Чем больше в компаниях витрин, миграций, антифрода, сегментации и сервисов вокруг одной базы, тем дороже обходится плохая читаемость запросов. Поэтому разговор про CTE в PostgreSQL — не про синтаксический вкус, а про стоимость сопровождения. Вопрос теперь не в том, нужны ли командам такие приёмы, а в том, когда чтение и поддержка SQL наконец начнут оценивать так же серьёзно, как качество обычного прикладного кода.
The post CTE в PostgreSQL: как распутать сложные SQL-запросы appeared first on iTech News.