Categories: PostgreSQL

PostgreSQL | Очень долгое удаление записи таблицы из-за триггера внешнего ключа

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

Проблема тут заключается в том, что если вы использовали технологию каскадного удаления между таблицами, то при переименовании оригинальной таблицы могут появиться фантомные проблемы. Не могу утверждать кто в этой ситуации неправ — то ли создатели PostgreSQL, то ли разработчики, которые просто не знают или не учитывают особенности этой нежной СУБД.

Опишу реальный пример, как появилась проблема. Есть три таблицы:

  • 1 — оригинал данных
  • 2 — зависимая от 1
  • 3 — зависимая от 2

Таблицы 2 и 3 работают с каскадным удалением. Если в таблице 1 удаляется запись, то по каскаду удаляются все зависимые данные в таблицах 2 и 3.

Таблица 3 часто меняется в объёме записей и поэтому сильно забирает место в хранилище. Сначала в неё массово заливаются данные, а потом алгоритмы чистят от лишнего. Заливки и чистки могут быть масштабными. Когда она доросла до 50 Гб стало сложно выполнять полную очистку (VACUUM FULL) такой таблицы. Проблема полной очистки в том, что никакие запросы не пролезают в эту таблицу, пока идёт процесс полной очистки. Это значит, что клиентские приложения не могут получать данные в этот момент.

Вы спросите зачем полная очистка? Чтобы не откусывать у ОС 200 Гб в пиковые заливки, при наполненных 30 Гб. Плохая плотность таблицы — это тоже проблема для производительности!

Было принято решение поменять структуру данной большой таблицы и перейти на секционированную. Каждая секция имеет возможность отдельно работать с VACUUM FULL.

Для переноса данных старая таблица была просто переименована с суффиксом «_old«, а в новой были созданы все те же столбцы. В секциях всё тоже самое.

Важный момент! Две таблицы продолжали существовать, пока все данные не перенеслись в секции.

Очень долгое удаление записи таблицы из-за триггера для ограничения — PostgreSQL

Когда дело дошло до удаления, то одна запись удалялась около 40 секунд в среднем по всем запросам на удаление. Это было бы нормально, если бы не существовало индексов. Но индексы были и обычный SELECT отрабатывал за доли секунды.

При использовании EXPLAIN ANALYZE после выполнения удаления мы получили план выполнения запроса, в котором было чётко указано место «торможения»:

Trigger for constraint bla_bla_fkey time=32716.190 calls=1

 

 

Решение проблемы

Удаление старой переименованной таблицы позволило вернуть быстрое удаление записей при каскаде для новой таблицы.

Что мешало им двоим существовать? Сложно ответить. Но после TRUNCATE и последующего DROP TABLE всё само собой нормализовалось.

Предварительный курс лечения был таким — попытки. Сначала были пересозданы ограничения на новой таблице. Затем были удалены все индексы у старой таблицы. Но думаю эти вещи никак не повлияли на нормализацию удаления записей в новой таблице.

admin

Share
Published by
admin

Recent Posts

PostgreSQL | Как получить первый элемент из ячейки типа record?

При помощи функции "row()" мы можем создавать записи - данные с типом record. -- Создаём…

1 год ago

PostgreSQL | Как отфильтровать JSONB-массив объектов по логическому значению ключа?

-- массив JSONB с объектами select '[{"id":1, "q":true}, {"id":2, "q":false}, {"id":3, "q":true}, {"id":4, "q":false}]'::jsonb; --…

1 год ago

PostgreSQL | Как отбросить нули в типе numeric?

Функция "trim_scale()" умеет отбрасывать лишние нули в числах с типом numeric. select trim_scale(37.000000000); -- 37…

1 год ago

JavaScript | Как получить следующий день?

Следующий день относительно текущего т. е. "завтра" var tomorrow = new Date(); tomorrow.setDate(tomorrow.getDate() + 1);…

1 год ago

PostgreSQL | Агрегация JSONB-объектов в один объект

У нас есть столбец из JSONB объектов. Нам нужно соединить все объекты этого столбца в…

1 год ago

PostgreSQL | Группировка по 10 записей

Не всегда нужно группировать записи таблиц в рамках какого-то повторяющегося значения столбца. Иногда нужно просто…

1 год ago