При администрировании серверных баз данных 1С на СУБД PostgreSQL администраторы часто сталкиваются с лавинообразным и неконтролируемым ростом дискового пространства. База, которая еще вчера весила пару гигабайт, может мгновенно раздуться до десятков, а то и сотен гигабайт. При этом детальный анализ СУБД показывает, что львиную долю объема занимают скрытые таблицы с префиксом pg_toast. Стандартные процедуры обслуживания, такие как обычный VACUUM или REINDEX, не приносят желаемого результата, а размер файлов на диске продолжает расти.
В этой статье мы подробно разберем природу этого явления, выясним причины неэффективности стандартной очистки в некоторых случаях, научимся точно диагностировать проблемные таблицы СУБД, сопоставлять их с объектами метаданных 1С и рассмотрим пошаговый план решения проблемы.
Для начала проанализируем архитектуру хранения данных в PostgreSQL. Механизм TOAST (The Oversized-Attribute Storage Technique) — это встроенная технология СУБД, предназначенная для работы со сверхбольшими атрибутами. В PostgreSQL физический размер строки таблицы жестко ограничен размером страницы данных (обычно это 8 Кб). Если строка содержит длинные поля (например, неограниченные текстовые строки, двоичные данные, XML или JSON), СУБД автоматически сжимает их. Если даже после сжатия размер строки превышает установленный лимит, "лишние" данные переносятся во вспомогательную скрытую таблицу pg_toast_..., а в основной таблице остается лишь указатель на эту запись.
В контексте экосистемы 1С:Предприятие в TOAST-сегменты чаще всего отправляются следующие данные:
ВерсииОбъектов.ДвоичныеДанныеФайлов или справочниках присоединенных файлов)._DataHistoryQueue и _DataHistoryVersions), которые фиксируют транзакционные изменения.Основная проблема заключается в механизме многоверсионности (MVCC) в PostgreSQL. При частом обновлении, перезаписи документов или регламентном удалении старых версий СУБД физически не удаляет старые записи с диска. Она лишь помечает эти версии строк как "мертвые". Если настройки автовакуума СУБД слишком мягкие или в базе присутствуют зависшие транзакции, то эти неактуальные данные накапливаются в TOAST-таблицах, вызывая взрывной рост физического размера базы данных.
Многие администраторы пытаются решить проблему "в лоб", запуская команду VACUUM FULL для всей базы через утилиту pgAdmin, но обнаруживают, что размер файлов на диске совершенно не уменьшается. Выясним основные причины, почему это происходит:
VACUUM FULL требует монопольной блокировки таблицы (уровень ACCESS EXCLUSIVE). Если в этот момент к базе подключены пользователи 1С или выполняются фоновые задания, СУБД либо заблокирует выполнение очистки, либо прервет ее по таймауту.Рассмотрим процесс поиска конкретной таблицы, которая раздувает нашу базу данных. Для начала выполним диагностический SQL-запрос в pgAdmin (или через консоль psql), чтобы сопоставить загадочную таблицу pg_toast_XXXXX с реальной физической таблицей СУБД:
SELECT
c.relname AS main_table_name,
t.relname AS toast_table_name,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE t.relname LIKE 'pg_toast_%'
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 10;
Этот запрос вернет список из 10 самых объемных таблиц базы данных, выводя имя основной таблицы СУБД (например, _inforeg12345), имя связанной с ней TOAST-таблицы (например, pg_toast_54321) и их общий размер со всеми индексами.
После того как мы выяснили техническое имя проблемной таблицы (к примеру, _inforeg12345), нам нужно понять, какому объекту конфигурации 1С она соответствует. Для этого воспользуемся встроенными механизмами платформы 1С. После определения основной таблицы по SQL-запросу сопоставить её с регистром 1С поможет просмотр физических таблиц и метаданных. Эта обработка не ищет связи pg_toast сама и не заменяет проверку транзакций и очистку PostgreSQL.
Мы можем написать простую внешнюю обработку или выполнить код в табло отладчика, который выведет полную структуру хранения базы данных. Разберем пример программного кода:
СтруктураБазы = ПолучитьСтруктуруХраненияБазыДанных();
Для Каждого Таблица Из СтруктураБазы Цикл
Если Таблица.ИмяТаблицыХранения = "_inforeg12345" Тогда
Сообщить("Имя таблицы: " + Таблица.ИмяТаблицыХранения);
Сообщить("Метаданные: " + Таблица.Метаданные);
Прервать;
КонецЕсли;
КонецЦикла;
Этот код мгновенно вернет назначение таблицы. В большинстве случаев вы обнаружите, что это будет регистр сведений ВерсииОбъектов (в СУБД он часто имеет префикс _inforeg с соответствующим номером).
Просто запустить сжатие на уровне СУБД недостаточно. Если в самой 1С эти данные числятся как актуальные, СУБД не сможет их освободить. Поэтому мы должны действовать комплексно и последовательно.
Первым делом необходимо избавиться от старого балласта на уровне логики 1С. Рассмотрим, как это сделать:
Перед запуском физического сжатия таблиц на уровне PostgreSQL мы должны гарантировать отсутствие зависших транзакций, которые могут заблокировать освобождение дискового пространства. Проанализируем активные процессы СУБД с помощью следующего запроса:
SELECT pid, age(query_start), state, query
FROM pg_stat_activity
WHERE state != 'idle' AND age(query_start) > '5 minutes'::interval;
Если мы обнаружили старые зависшие транзакции со статусом idle in transaction, завершим их принудительно, используя системную команду (где вместо pid нужно указать идентификатор процесса из предыдущего запроса):
SELECT pg_terminate_backend(pid);
Когда данные в 1С очищены, а блокирующие транзакции устранены, мы можем вернуть свободное место операционной системе. Разберем три альтернативных способа решения этой задачи:
Способ 1. Точечный VACUUM FULL для конкретной таблицы (с блокировкой пользователей)
Вместо долгого и неэффективного обслуживания всей базы данных, запустим глубокую очистку и перестроение только для нашей проблемной таблицы. Это существенно сэкономит время и дисковые ресурсы сервера:
VACUUM FULL _inforeg12345;
При выполнении этой команды СУБД создаст новую чистую копию таблицы _inforeg12345 на диске, скопирует туда только "живые" записи, полностью проигнорирует "мертвые" TOAST-данные, перестроит все индексы, заменит старую таблицу новой и сразу вернет освободившийся объем операционной системе. Важно: на время выполнения этой команды таблица будет монопольно заблокирована, поэтому выполнять операцию нужно во время отсутствия пользователей в базе.
Способ 2. Фоновое сжатие "на лету" без остановки работы (утилита pg_repack)
Если ваша база работает в режиме 24/7 и вы не можете позволить себе монопольную блокировку крупных таблиц, рекомендуется воспользоваться бесплатной консольной утилитой pg_repack. Она выполняет те же действия, что и VACUUM FULL, но в фоновом режиме:
Для работы утилиты на диске должно быть свободно как минимум столько же места, сколько весит сама сжимаемая таблица.
Способ 3. Перевыгрузка базы через файл резервной копии .dt (для баз небольшого и среднего размера)
Для баз данных размером до 50 Гб самым надежным, чистым и гарантированным способом избавиться от любого технологического "мусора" в PostgreSQL является классический цикл перезаливки базы через платформу 1С. Выполним следующие действия по шагам:
.dt..dt.При загрузке из формата .dt платформа 1С заново генерирует всю физическую структуру таблиц СУБД с нуля. Данные записываются плотно, последовательно, индексы перестраиваются оптимальным образом, а все неиспользуемое и "раздутое" пространство в TOAST-сегментах исчезает полностью.
Чтобы не сталкиваться с этой проблемой регулярно, настроим автоматическую защиту нашей системы:
postgresql.conf установим параметр idle_in_transaction_session_timeout = 60000 (время в миллисекундах, в данном случае 1 минута). Это заставит PostgreSQL автоматически принудительно разрывать транзакции 1С, которые перешли в спящий режим и мешают штатной работе автовакуума.
ALTER TABLE _inforeg12345 SET (autovacuum_vacuum_scale_factor = 0.05);
Проанализировав причины раздувания pg_toast, локализовав проблемные таблицы и выполнив комплексную очистку, мы сможем вернуть драгоценное дисковое пространство на сервере и обеспечить стабильную и быструю работу нашей информационной системы 1С.