При администрировании крупных информационных баз на платформе 1С:Предприятие часто возникает необходимость очистки устаревших данных напрямую через СУБД. На СУБД MS SQL для решения этой задачи традиционно используется конструкция SET ROWCOUNT в сочетании с системной переменной @@rowcount. Это позволяет удалять данные небольшими порциями (батчами) в цикле, не перегружая журнал транзакций и избегая длительных блокировок таблиц. Однако при переносе этих скриптов на PostgreSQL администраторы и разработчики сталкиваются с отсутствием прямых аналогов этих инструментов. В этой статье мы подробно разберем, как эффективно переписать логику пакетного удаления под PostgreSQL, рассмотрим особенности архитектуры этой СУБД и изучим передовые методы оптимизации очистки таблиц.
Для начала проанализируем классический скрипт для MS SQL, который требуется перенести на PostgreSQL. В исходном примере мы видим попытку удалить документы за определенный период порциями по 100 000 строк:
set rowcount 100000
set dateformat ymd
delete from _Document452
where _Document452._Date_Time >=('4018-12-01 00:00:00')
and _Document452._Date_Time <=('4018-12-31 23:59:59')
while @@rowcount > 0
begin
delete from _Document452
where _Document452._Date_Time >=('4018-12-01 00:00:00')
and _Document452._Date_Time <=('4018-12-31 23:59:59')
end;
Давайте подробнее разберем исходный код. Конструкция SET ROWCOUNT 100000 в MS SQL является глобальной настройкой сессии. Это означает, что абсолютно любой последующий запрос, будь то SELECT, UPDATE или DELETE, будет ограничен указанным лимитом строк. В PostgreSQL такого глобального переключателя не существует. Это сделано намеренно для повышения безопасности и предсказуемости выполнения запросов, чтобы исключить ситуации, когда разработчик случайно забыл отключить ограничение, и последующие транзакции выполнились некорректно. Следовательно, в PostgreSQL нам необходимо явно указывать лимиты для каждого конкретного шага удаления.
Первое ключевое отличие PostgreSQL заключается в том, что оператор DELETE напрямую не поддерживает конструкцию LIMIT. Мы не можем написать простой запрос вида DELETE FROM _Document452 WHERE ... LIMIT 100000. Чтобы ограничить количество удаляемых строк, нам придется использовать обходные пути. Для этого отлично подходят обобщенные табличные выражения (Common Table Expressions, или CTE) или подзапросы. С их помощью мы можем сначала выбрать ограниченный пул идентификаторов строк, а затем удалить только их.
Если мы пишем процедурный код на языке PL/pgSQL (например, внутри функции или процедуры), прямым аналогом @@rowcount является команда GET DIAGNOSTICS с параметром ROW_COUNT. Эта инструкция позволяет получить количество строк, обработанных последней выполненной SQL-командой. Разберем пример реализации этого подхода:
CREATE OR REPLACE FUNCTION delete_documents_batch()
RETURNS integer AS $$
DECLARE
deleted_rows integer := 0;
total_deleted integer := 0;
BEGIN
LOOP
-- Удаляем порцию строк с использованием подзапроса
DELETE FROM _Document452
WHERE ctid IN (
SELECT ctid FROM _Document452
WHERE _Date_Time >= '4018-12-01 00:00:00'
AND _Date_Time <= '4018-12-31 23:59:59'
LIMIT 100000
);
-- Получаем количество удаленных строк на этой итерации
GET DIAGNOSTICS deleted_rows = ROW_COUNT;
-- Накапливаем общий счетчик
total_deleted := total_deleted + deleted_rows;
-- Если на текущем шаге ничего не удалено, выходим из цикла
EXIT WHEN deleted_rows = 0;
END LOOP;
RETURN total_deleted;
END;
$$ LANGUAGE plpgsql;
В этом примере мы использовали специальный системный идентификатор ctid для связи подзапроса с операцией удаления. Это стандартный и вполне рабочий подход, однако его можно существенно улучшить и оптимизировать.
В языке PL/pgSQL есть встроенная системная переменная FOUND, которая имеет логический тип (boolean). Она автоматически принимает значение TRUE, если предыдущая операция (например, INSERT, UPDATE или DELETE) затронула хотя бы одну строку. Использование этой переменной позволяет избавиться от необходимости объявлять дополнительные числовые переменные и запрашивать диагностику. Посмотрим, как сокращается наш код:
CREATE OR REPLACE FUNCTION delete_documents_found()
RETURNS void AS $$
BEGIN
LOOP
DELETE FROM _Document452
WHERE ctid IN (
SELECT ctid FROM _Document452
WHERE _Date_Time >= '4018-12-01 00:00:00'
AND _Date_Time <= '4018-12-31 23:59:59'
LIMIT 100000
);
-- Выходим, если предыдущий запрос не нашел строк для удаления
EXIT WHEN NOT FOUND;
END LOOP;
END;
$$ LANGUAGE plpgsql;
Этот вариант выглядит значительно лаконичнее и работает быстрее за счет отсутствия лишних вызовов диагностических команд.
Для разовых административных задач создавать постоянную функцию в базе данных не рекомендуется, так как это засоряет схему БД. Гораздо удобнее использовать анонимные блоки DO. Кроме того, начиная с PostgreSQL 11, в анонимных блоках и процедурах появилась поддержка управления транзакциями. Мы можем выполнять фиксацию транзакции (команда COMMIT) прямо внутри цикла!
Зачем нужен COMMIT внутри цикла? Если мы удаляем миллионы строк в рамках одной транзакции, то журнал транзакций (WAL) разрастается до огромных размеров, а блокировки строк удерживаются до самого конца работы скрипта, мешая другим пользователям. Фиксация транзакции после каждого батча мгновенно освобождает память, лог-файлы и снимает блокировки.
Также мы задействуем системное поле ctid. Поле ctid представляет собой физический адрес строки на диске. Использование конструкции USING совместно с ctid позволяет PostgreSQL мгновенно находить строки для удаления, минуя повторный поиск по индексным деревьям. Разберем итоговый высокопроизводительный скрипт:
DO $$
DECLARE
deleted_rows bigint;
BEGIN
LOOP
-- Выбираем ctid батча строк и удаляем их в рамках одного запроса
WITH delete_batch AS (
SELECT ctid FROM _Document452
WHERE _Date_Time >= '4018-12-01 00:00:00'
AND _Date_Time <= '4018-12-31 23:59:59'
LIMIT 100000
)
DELETE FROM _Document452
USING delete_batch
WHERE _Document452.ctid = delete_batch.ctid;
-- Проверяем, были ли удалены строки
GET DIAGNOSTICS deleted_rows = ROW_COUNT;
-- Если строки закончились, завершаем выполнение
EXIT WHEN deleted_rows = 0;
-- Выводим информационное сообщение в консоль СУБД
RAISE NOTICE 'Успешно удалено очередных % строк', deleted_rows;
-- Фиксируем транзакцию для очистки WAL и снятия блокировок
COMMIT;
END LOOP;
END $$;
Давайте детально изучим, почему системное поле ctid является настолько эффективным. В PostgreSQL каждая таблица состоит из страниц данных (обычно размером 8 Кб). Каждая строка имеет свой уникальный физический адрес на диске, который и записывается в поле ctid в формате (номер_страницы, индекс_строки). Когда мы выполняем обычный поиск по первичному ключу или по дате, планировщик сначала обращается к индексу, находит там физический адрес строки, а затем считывает саму строку с диска. Используя ctid, мы полностью исключаем шаг поиска по индексу при удалении. Мы сначала выбираем нужные адреса с помощью быстрого индексного сканирования (Index Scan), а затем напрямую передаем эти адреса оператору DELETE. Это минимизирует нагрузку на дисковую подсистему и процессор, что критически важно при работе с высоконагруженными базами данных 1С.
Этот скрипт объединяет в себе все лучшие практики работы с PostgreSQL: он не засоряет базу данных постоянными объектами, удаляет данные максимально быстро за счет прямого доступа по физическим адресам ctid и не создает пиковых нагрузок на сервер благодаря регулярным коммитам транзакций.
Если вы предпочитаете выполнять администрирование через консольный клиент psql, существует элегантный способ организовать пакетное удаление вообще без использования процедурного языка PL/pgSQL. Для этого мы можем задействовать встроенную команду интерактивного терминала \watch.
Мы пишем один-единственный SQL-запрос, который удаляет ровно одну порцию данных:
WITH delete_batch AS (
SELECT ctid FROM _Document452
WHERE _Date_Time >= '4018-12-01 00:00:00'
AND _Date_Time <= '4018-12-31 23:59:59'
LIMIT 100000
)
DELETE FROM _Document452
USING delete_batch
WHERE _Document452.ctid = delete_batch.ctid;
А затем вводим в консоли команду \watch 1. Эта команда заставит psql циклически отправлять на сервер последний выполненный запрос каждые 1 секунду. На экран будет выводиться отчет СУБД (например, "DELETE 100000"). Как только на экране появится сообщение "DELETE 0", это будет означать, что все целевые данные удалены. Для остановки процесса достаточно нажать комбинацию клавиш Ctrl + C.
Критически важно понимать внутреннее устройство PostgreSQL. В отличие от MS SQL, при выполнении команды DELETE строки физически не удаляются из файлов на диске. Архитектура MVCC (многоверсионность) просто помечает эти строки как неактивные ("мертвые" версии строк, или dead tuples). Дисковое пространство при этом остается занятым, а сама таблица раздувается (происходит bloat), что может значительно замедлить последующее чтение данных.
Выясним причину, почему СУБД PostgreSQL работает именно так. Архитектура MVCC (Multi-Version Concurrency Control) спроектирована таким образом, чтобы читающие транзакции никогда не блокировали пишущие, и наоборот. Когда мы удаляем строку, СУБД не может сразу физически стереть ее с диска, поскольку в этот же самый момент другая параллельная транзакция может выполнять построение отчета и считывать эти данные. Поэтому строка просто помечается специальным флагом удаления. Со временем накопление таких мертвых строк приводит к снижению производительности всей системы. Своевременный вызов утилиты VACUUM позволяет очистить эти мертвые зоны и подготовить их для повторного использования платформой 1С. Мы рекомендуем настраивать параметры автоочистки (autovacuum) на уровне сервера СУБД, но после проведения таких масштабных ручных работ, как удаление целого месяца документов, ручной запуск полной очистки является обязательным этапом администрирования.
Чтобы физически очистить файлы данных и обновить статистику планировщика после удаления миллионов строк, необходимо обязательно выполнить операцию очистки. Для этого мы используем следующие команды:
VACUUM ANALYZE _Document452;. Она освободит место внутри страниц таблицы для будущих новых записей и обновит статистику для оптимизатора запросов. Это безопасная операция, которая может выполняться параллельно с работой пользователей.VACUUM FULL _Document452;. Обратите внимание, что эта команда создает новую копию таблицы на диске и полностью блокирует исходную таблицу как на запись, так и на чтение до завершения процесса. Ее следует выполнять только в технологическое окно обслуживания.В завершение напомним о важном аспекте работы с базами данных 1С:Предприятие. Платформа 1С не использует внешние ключи (Foreign Keys) на уровне СУБД. Контроль ссылочной целостности полностью возложен на логику самой платформы. Если вы удаляете документы напрямую через SQL-запросы (в обход объектной техники 1С), в базе останутся связанные записи в табличных частях документов, а также движения по регистрам сведений, накопления и бухгалтерии.
Поэтому подобные "жесткие" операции удаления на уровне СУБД должны выполняться только опытными администраторами, четко понимающими структуру метаданных 1С, и сопровождаться последующим тестированием и исправлением информационной базы на уровне конфигуратора.