Как анализировать и оптимизировать производительность удаления записи с проверкой потомков в иерархической таблице с миллионами записей

Что проверяют

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

Как отвечать

Начните с диагностики. В PostgreSQL EXPLAIN (ANALYZE, BUFFERS) на DELETE выполняет удаление по-настоящему, поэтому запускайте его внутри BEGIN ... ROLLBACK. Время проверок внешних ключей выводится отдельной строкой Trigger for constraint ..., и чаще всего проблема там.

Типичные причины и решения:

• нет индекса по parent_id: PostgreSQL не создаёт индекс на ссылающейся колонке сам, и каждая проверка внешнего ключа читает таблицу целиком;
• *`COUNT() вместо EXISTS**: EXISTS (SELECT 1 FROM node WHERE parent_id = ?)` останавливается на первом найденном потомке;
• поддерево: собрать идентификаторы через WITH RECURSIVE и удалять пачками по несколько тысяч строк с коммитом между ними;
• ORM: cascade = CascadeType.REMOVE в Hibernate загружает всё дерево в память и удаляет по одной сущности. Для массового удаления нужен нативный запрос или jdbcTemplate.batchUpdate;
• модель хранения: если поддеревья удаляют часто, к parent_id добавляют материализованный путь (ltree с GiST-индексом) или closure table, и поддерево выбирается одним запросом без рекурсии;
• мягкое удаление: сразу ставится deleted_at, физическая очистка идёт фоновой задачей.

Эффект проверяйте тем же EXPLAIN ANALYZE и pg_stat_statements до и после изменений.

Где спотыкаются

Начинают с JMeter и профайлера JVM, хотя время уходит в базе. Первым делом нужен план запроса.

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

Удаляют поддерево в миллион строк одной транзакцией. Это долгие блокировки, всплеск WAL и отставание реплик, а при ошибке откатывается всё.