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

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

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

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

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

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

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

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

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

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

Временное увеличение переменной конфигурации max_wal_size также может ускорить загрузку больших объемов данных. Это происходит потому, что загрузка большого объема данных в Tantor BE вызывает более частое появление контрольных точек, чем обычно (то есть чаще, чем задано переменной конфигурации 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 автоматически; см. Раздел 23.1.3 и Раздел 23.1.6 для получения дополнительной информации.

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

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

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