Семь практических советов по массовой загрузке данных в PostgreSQL

Вольный перевод статьи «7 Best Practice Tips for PostgreSQL Bulk Data Loading»

Иногда возникает необходимость в несколько простых шагов загрузить в БД PostgreSQL большой объём данных. Данная практика обычно называется массовым импортом, когда в качестве источника данных служит один или несколько больших файлов. Данный процесс иногда может проходить неприемлемо медленно. Существует несколько причин такой низкой производительности. Вызывать задержки могут индексы, триггеры, внешние и первичные ключи или даже запись WAL – файлов.

В этой статье мы дадим несколько практических советов по массовому импорту данных в БД PostgreSQL. Однако, могут возникнуть ситуации, когда ни один из них не станет эффективным решением проблемы. Мы рекомендуем читателям рассмотреть достоинства и недостатки любого метода, прежде чем применить его.

Совет 1. Перевод целевой таблицы в нежурналируемый режим

В PostgreSQL9.5 и выше целевую таблицу можно перевести в нежурналируемый режим, а после загрузки данных вернуть в журналируемый.

ALTER TABLE <target table> SET UNLOGGED;
<bulk data insert operations…>
ALTER TABLE <target table> LOGGED;

Нежурналируемый режим гарантирует, что PostgreSQL не будет отправлять операции загрузки в таблицу в журнал предзаписи (WAL). Это может значительно ускорить процесс загрузки. Однако, раз эти операции не логируются, данные не смогут быть восстановлены в случае сбоя или незапланированного падения сервиса PostgreSQL вовремя заливки. PostgreSQL автоматически странкейтит любую нежурналируемую таблицу после рестарта.

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

Мы рекомендуем следующие подходы для заливки данных в нежурналируемые таблицы:

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

Совет 2. Удаление и пересоздание индексов

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

Мы рекомендуем, по-возможности, удалять индексы на целевой таблице перед запуском массового импорта и восстанавливать их после того, как загрузка будет завершена. Несмотря на то, что создание индексов на больших таблицах может занять много времени, в целом, это будет быстрее, чем обновлять индексы во время загрузки.

DROP INDEX <index_name1>, <index_name2> … <index_name_n>
<bulk data insert operations…>
CREATE INDEX <index_name> ON <target_table>(column1, …,column n)

Непосредственно перед созданием индексов может оказаться целесообразным временно увеличить конфигурационный параметр maintenance_work_mem. Выделение дополнительной рабочей памяти может помочь создать индексы быстрее.

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

Совет 3. Удаление и пересоздание внешних ключей

Так же, как и индексы, ограничения внешнего ключа могут оказать влияние на производительность массового импорта из – за того, что каждый внешний ключ в каждой вставленной строке должен быть проверен на наличие соответствующего первичного ключа. Неявно PostgreSQL использует специальный триггер для проведения этой проверки.

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

ALTER TABLE <target_table> 
    DROP CONSTRAINT <foreign_key_constraint>;
BEGIN TRANSACTION;
    <bulk data insert operations…>
COMMIT;
ALTER TABLE <target_table> 
    ADD CONSTRAINT <foreign key constraint>  
    FOREIGN KEY (<foreign_key_field>) 
    REFERENCES <parent_table>(<primary key field>)...;

Опять же, увеличение параметра maintenance_work_mem поможет улучшить производительность при пересоздании внешних ключей.

Совет 4. Деактивация триггеров

Триггеры на INSERT или DELETE (если процесс загрузки также содержит удаление записей из целевой таблицы) могут стать причиной замедления в процессе массового импорта данных. Это связано с тем, что каждый триггер содержит программную логику, которую нужно проверить и операции, которые нужно выполнить сразу после каждой вставки или удаления строки.

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

ALTER TABLE <target table> DISABLE TRIGGER ALL;
<bulk data insert operations…>
ALTER TABLE <target table> ENABLE TRIGGER ALL;

Совет 5. Используйте команду COPY

Мы рекомендуем использовать штатную команду PostgreSQL – COPY для загрузки данных из одного или нескольких файлов. COPY оптимизирована для массовой загрузки данных. Это более эффективно, нежели запуск большого количества операторов INSERT и даже одного INSERT-а с множественным включением выражения VALUE

COPY <target table> [( column1>, … , <column_n>)]
    FROM  '<file_name_and_path>' 
    WITH  (<option1>, <option2>, … , <option_n>)

Среди других преимуществ использования команды COPY:

• поддержка импорта, как из текстовых, так и из двоичных файлов;
• работа в транзакционном режиме;
• возможность указать структуру исходных файлов;
• возможность ограничить выборку загружаемых данных использованием выражения WHERE.

Совет 6. Используйте оператор INSERT с множественным выражением VALUE

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

Использование оператора INSERT с множественным включением выражения VALUE поможет избежать этих накладных расходов.

INSERT INTO <target_table> (<column1>, <column2>, …, <column_n>) 
VALUES 
    (<value a>, <value b>, …, <value x>),
    (<value 1>, <value 2>, …, <value n>),
    (<value A>, <value B>, …, <value Z>),
    (<value i>, <value ii>, …, <value L>),
    ...;

На производительность INSERTа с множественным VALUES влияют существующие индексы. Мы рекомендуем удалить индексы до запуска команды и пересоздать потом.

Ещё один аспект, который следует учесть, это общий объём оперативный памяти, доступный PostgreSQL для запуска INSERTа с множественным VALUES. Когда запускается такой INSERT, большое количество входных значений должно уместиться в RAM и если доступной памяти недостаточно, процесс может упасть с ошибкой.

Мы рекомендуем выставить параметр effective_cache_size в значение 50%, а параметр shared_buffer в значение 25% от общего объёма оперативной памяти компьютера. Также, в целях безопасности, при запуске серии INSERTов с множественным VALUES, каждый оператор будет запущен с ограничением в 1000 строк.

Совет 7. Запуск ANALYZE

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

Заключение

Массовый импорт данных для приложений БД происходит не каждый день, но его работа влияет на производительность запросов. Вот почему необходимо, насколько это возможно, сократить время загрузки. Есть одна вещь, которые администраторы БД могут сделать, чтобы минимизировать возможность появления любых неожиданностей – провести оптимизацию загрузки в тестовой среде с аналогичным сервером и подобным образом сконфигурированным PostgreSQL. Есть различные сценарии загрузки данных и будет лучше испробовать каждый метод и выбрать один, который хорошо работает.