При использовании нескольких INSERTкоманд отключите автофиксацию и выполните одну общую фиксацию в конце. (В чистом SQL это означает выполнение команды BEGIN в начале и команды COMMIT в конце. Некоторые клиентские библиотеки могут выполнять это скрыто; в таком случае необходимо убедиться, что библиотека выполняет эти действия должным образом. Если фиксация каждой операции вставки выполняется отдельно, то Digital Q.DataBase система выполняет значительный объем работы для каждой добавляемой строки. Дополнительным преимуществом выполнения всех операций вставки в одной транзакции является то, что в случае ошибки при вставке одной строки будет выполнен откат всех ранее вставленных строк, что предотвратит появление частично загруженных данных.

Используйте COPY для загрузки всех строк одной командой вместо использования серии команд INSERT команды. Данная COPY команда оптимизирована для загрузки большого количества строк; она менее гибкая, чем INSERT, но сопряжена со значительно меньшими накладными расходами при загрузке больших объемов данных. Поскольку COPY является единичной командой, при использовании данного метода наполнения таблицы нет необходимости отключать автофиксацию.

Если использование команды COPYневозможно, может помочь команда PREPARE для создания подготовленного INSERT оператора с последующим вызовом команды EXECUTE необходимое количество раз. Это позволяет избежать некоторых накладных расходов на повторный синтаксический разбор и планирование INSERT. Различные интерфейсы предоставляют данную возможность разными способами; ищите раздел «подготовленные операторы» в документации по интерфейсу.

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

COPY выполняется максимально быстро при использовании в той же транзакции, что и предшествующая команда CREATE TABLE или TRUNCATE command. В таких случаях запись в журнал WAL не требуется, так как при возникновении ошибки файлы, содержащие вновь загруженные данные, в любом случае будут удалены. Однако данное соображение применимо только в том случае, если параметр wal_level имеет значение minimal , так как в противном случае все команды должны записывать данные в журнал WAL.

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

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

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

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

Временное увеличение значения maintenance_work_mem параметра конфигурации при загрузке больших объемов данных может привести к повышению производительности. Это будет способствовать ускорению выполнения команд CREATE INDEX и команд ALTER TABLE ADD FOREIGN KEY . На саму команду это существенно не повлияет, COPY поэтому данный совет применим только при использовании одного или обоих вышеуказанных методов.

Временное увеличение значения max_wal_size параметра конфигурации также может ускорить загрузку больших массивов данных. Это обусловлено тем, что загрузка большого объема данных в Digital Q.DataBase приведет к более частому выполнению контрольных точек по сравнению с обычной частотой (заданной параметром checkpoint_timeout параметром конфигурации). При каждом выполнении контрольной точки все «грязные» страницы должны быть сброшены на диск. Путем увеличения значения max_wal_size на время массовой загрузки данных можно сократить количество необходимых контрольных точек.

При загрузке больших объемов данных в систему, использующую архивирование журнала WAL или потоковую репликацию, создание новой базовой резервной копии после завершения загрузки может оказаться быстрее, чем обработка большого объема инкрементных данных WAL. Чтобы предотвратить ведение инкрементного журнала WAL во время загрузки, отключите архивирование и потоковую репликацию, установив параметр wal_level в значение minimal, archive_mode в значение off, а параметр max_wal_senders в ноль. Однако учтите, что изменение этих настроек требует перезапуска сервера и делает любые базовые резервные копии, созданные ранее, непригодными для восстановления из архива или использования на резервном сервере, что может привести к потере данных.

Помимо экономии времени на обработку данных WAL процессом архивации или процессом передачи WAL, данный подход ускоряет выполнение определенных команд, так как они вообще не записывают данные в журнал WAL, если wal_level установлен параметр minimal и текущая подтранзакция (или транзакция верхнего уровня) создала или усекла таблицу либо индекс, в которые вносятся изменения. (Они могут гарантировать отказоустойчивость с меньшими затратами, выполняя команду fsync в конце вместо записи в журнал WAL.)

В случаях существенного изменения распределения данных в таблице выполнение команды ANALYZE настоятельно рекомендуется. Это также относится к массовой загрузке больших объемов данных в таблицу. Выполнение команды ANALYZE (или VACUUM ANALYZE) гарантирует наличие у планировщика актуальной статистики о таблице. При отсутствии или устаревании статистических данных планировщик может принимать неоптимальные решения при планировании запроса, что приводит к низкой производительности при работе с любыми таблицами, имеющими недостоверную или отсутствующую статистику. Обратите внимание: если фоновый процесс автоочистки включен, он может запускать команду ANALYZE автоматически; см. Раздел 3.9.1.3 и Раздел 3.9.1.6 для получения дополнительной информации.

Сценарии выгрузки, созданные командой pg_dump автоматически применяют некоторые, но не все из вышеуказанных рекомендаций. Для восстановления pg_dump дампа в кратчайшие сроки необходимо выполнить ряд дополнительных действий вручную. (Обратите внимание, что данные положения применимы при восстановлении дампа, а не при его создании . Эти же правила действуют как при загрузке текстового дампа с помощью psql так и при использовании команды pg_restore для загрузки из pg_dump архивного файла.)

По умолчанию pg_dump использует COPY, и при генерации полного дампа схемы и данных она обеспечивает загрузку данных перед созданием индексов и внешних ключей. Таким образом, в данном случае выполнение нескольких рекомендаций обеспечивается автоматически. Вам остается выполнить следующее:

Дамп, содержащий только данные, по-прежнему будет использовать COPY, но он не удаляет и не пересоздает индексы, а также обычно не затрагивает внешние ключи. ^[14] Таким образом, при загрузке дампа, содержащего только данные, пользователь должен самостоятельно удалить и заново создать индексы и внешние ключи, если это необходимо. По-прежнему целесообразно увеличить значение max_wal_size на время загрузки данных, однако не стоит увеличивать maintenance_work_mem; напротив, это следует сделать позже, при ручном пересоздании индексов и внешних ключей. Также не забудьте выполнить ANALYZE по завершении операции; см. Раздел 3.9.1.3 и Раздел 3.9.1.6 для получения дополнительной информации.