При эксплуатации систем на базе 1С:Предприятие многие специалисты сталкиваются с неожиданной ситуацией: после миграции с MSSQL на PostgreSQL размер индексных таблиц (особенно для регистров накопления и бухгалтерии) вырастает в несколько раз, а иногда и в десятки раз. В этой статье мы подробно разберем, почему возникает такая колоссальная разница, как архитектура СУБД влияет на дисковое пространство и какие шаги необходимо предпринять для оптимизации хранения данных.
Для начала проанализируем фундаментальное различие в хранении данных. В MSSQL по умолчанию используется понятие Clustered Index (кластерный индекс). В этой архитектуре сама таблица и есть индекс. Данные строк хранятся непосредственно в листовых узлах B-дерева этого индекса. Когда вы смотрите на размер "данных" в MSSQL, вы видите размер этого дерева. Дополнительные (некластерные) индексы в MSSQL содержат только значения индексируемых колонок и указатель на строку в кластерном индексе.
В PostgreSQL ситуация иная. Таблица всегда представляет собой так называемую Heap-таблицу (кучу). Данные хранятся в отдельном файле в неупорядоченном виде. Любой индекс в PostgreSQL — это отдельная физическая структура, которая дублирует в себе данные индексируемых колонок. Даже если вы выполните команду CLUSTER, PostgreSQL просто переупорядочит строки в основной таблице (Heap) согласно индексу, но сам индекс при этом останется отдельным файлом. Таким образом, в PostgreSQL данные измерений регистра фактически хранятся дважды: один раз в самой таблице и второй раз — в индексном файле. Это объясняет, почему размер индекса _accumrgt_1 сопоставим с размером основной таблицы _accumrgt.
Разберем ситуацию, описанную в теме: используется PostgreSQL 11.9. Это важный момент, так как начиная с версии PostgreSQL 12, в СУБД был внедрен механизм B-tree deduplication (дедупликация индексов). Рассмотрим, как это работает:
В регистрах 1С (особенно в таблицах итогов) измерения часто содержат повторяющиеся значения. В PostgreSQL 11 и более ранних версиях каждая индексная запись для одинаковых значений — это отдельная физическая запись в дереве. Если у вас миллион записей с одним и тем же периодом или складом, в индексе будет миллион дублей этих значений. В PostgreSQL 12+ одинаковые ключи группируются, что позволяет сократить размер индексов в 1С (особенно для таблиц итогов) в 2–4 раза. Если вы видите аномально большой индекс, первое, что стоит проверить — это возможность перехода на более современную версию PostgreSQL (12, 13, 14 или 15).
Проанализируем поведение системы при выполнении регламентных операций. Часто администраторы надеются, что выполнение VACUUM FULL решит проблему размера. Однако, если база была только что загружена из файла .dt или в ней только что были пересчитаны итоги, VACUUM FULL не даст эффекта, так как данные и так упакованы максимально плотно для данной версии СУБД.
Разрастание происходит в процессе эксплуатации из-за механизма MVCC (Multi-Version Concurrency Control). Когда 1С обновляет запись в таблице итогов, PostgreSQL не перезаписывает старую строку, а помечает её как удаленную и вставляет новую. Соответственно, в индексах также появляются новые записи. Если автовакуум не успевает очищать "фантомные" записи, индекс начинает "раздуваться" (эффект Bloat). Однако в случае автора темы проблема была не в мусоре, а именно в физическом объеме полезных данных, дублируемых индексом.
Выясним, как конкретные типы данных 1С влияют на объем занимаемого места в PostgreSQL:
bytea или uuid. Они занимают фиксированные 16 байт, но при большом количестве измерений их суммарный вес в индексе становится значительным.Рассмотрим по шагам, что можно сделать для уменьшения размера индексных таблиц:
Это самый эффективный метод. Если в регистре накопления используются строки в качестве измерений, их необходимо заменить на ссылочные типы (справочники). Вместо хранения длинной строки "Очень длинное наименование услуги или товара..." в каждой строке регистра и его индекса, СУБД будет хранить компактную 16-байтную ссылку.
Проанализируем состав измерений. В 1С у каждого измерения есть свойство "Индексировать". Если оно установлено в значение "Индексировать", платформа создаст дополнительный индекс по этому полю. Рассмотрим пример из обсуждения:
// Пример из сообщения 13:
// Установка свойства "Индексировать" у первого ссылочного измерения
// привела к уменьшению размера основного индекса таблицы итогов,
// так как изменилась структура и порядок полей, но добавила
// новый отдельный индексный файл.
Нужно соблюдать баланс: избыточные индексы не только замедляют запись, но и потребляют огромное количество дискового пространства в PostgreSQL из-за отсутствия кластеризации в понимании MSSQL.
Для баз 1С на PostgreSQL крайне рекомендуется использовать версии не ниже 12. Разница в объеме индексов между 11-й и 12-й версиями на типовых конфигурациях (БП, ERP) может достигать 30–50% именно за счет дедупликации повторяющихся ключей в B-tree.
Посмотрим на параметр fillfactor. По умолчанию для индексов в PostgreSQL он равен 90. Это означает, что 10% места в каждой странице индекса резервируется под будущие вставки. Если таблица итогов регистра пересчитывается редко и в основном читается, можно попробовать увеличить этот параметр до 100 для более плотной упаковки, однако это может замедлить последующие вставки. Для изменения этого параметра через SQL можно использовать следующую конструкцию:
ALTER INDEX _accumrgt3795_1 SET (fillfactor = 100);
REINDEX INDEX _accumrgt3795_1;
Подводя итог, отметим, что разница в размерах между PostgreSQL и MSSQL обусловлена не "ошибками" систем, а их разной архитектурой. PostgreSQL платит объемом диска за гибкость Heap-таблиц и простоту реализации MVCC. Для минимизации этой разницы в среде 1С необходимо минимизировать использование строковых полей в измерениях, использовать актуальные версии PostgreSQL (12+) и внимательно следить за настройками обслуживания базы данных.