Как работает COPY и почему он намного быстрее, чем INSERT?

Здесь действует ряд факторов:

• Сетевая задержка и двусторонняя задержка
• Накладные расходы на оператор в PostgreSQL
• Переключение контекста и задержки планировщика
• COMMIT стоит, если для людей, делающих одну фиксацию на вставку (у вас нет)
• COPY специальные оптимизации для массовой загрузки

Сетевая задержка

Если сервер удален, вы можете «платить» фиксированную «цену» за каждый оператор, скажем, 50 мс (1/20 секунды). Или многое другое для некоторых облачных баз данных. Поскольку следующая вставка не может начаться до тех пор, пока последняя не завершится успешно, это означает, что ваша максимальная частота вставок составляет 1000 / обратная-задержка-в мс строк в секунду. При задержке 50 мс («время пинга») это 20 строк в секунду. Даже на локальном сервере эта задержка отлична от нуля. Принимая во внимание, что COPY просто заполняет окна отправки и получения TCP и передает строки так быстро, как БД может их записывать, а сеть может их передавать. На него не сильно влияет задержка, и он может вставлять тысячи строк в секунду по одному и тому же сетевому каналу.

Затраты на оператор в PostgreSQL

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

Затраты на переключение задачи/контекста

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

Отсутствие оптимизации COPY

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

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

Совершить расходы

И последнее, что нужно учитывать, — это затраты на коммит. Это, вероятно, не проблема для вас, потому что psycopg2 по умолчанию открывает транзакцию и не фиксирует ее, пока вы не скажете об этом. Если вы не сказали использовать автокоммит. Но для многих драйверов БД автофиксация используется по умолчанию. В таких случаях вы будете делать одну фиксацию для каждого INSERT. Это означает одну очистку диска, при которой сервер записывает все данные из памяти на диск и сообщает дискам о необходимости записи собственных кэшей в постоянное хранилище. Это может занять длительное время и зависит от аппаратного обеспечения. Мой ноутбук NVMe BTRFS на базе SSD может выполнять только 200 операций синхронизации в секунду по сравнению с 300 000 несинхронизированных операций записи в секунду. Таким образом, он будет загружать только 200 строк в секунду! Некоторые серверы могут выполнять только 50 синхронизаций в секунду. Некоторые могут сделать 20000. Поэтому, если вам нужно регулярно выполнять коммиты, старайтесь загружать и коммитить пакетами, делать многострочные вставки и т. д. Поскольку COPY делает только один коммит в конце, затраты на коммит незначительны. Но это также означает, что COPY не может восстановиться после ошибок на полпути к данным; это отменяет всю объемную нагрузку.

При копировании используется массовая загрузка, что означает, что он вставляет несколько строк каждый раз, тогда как простая вставка выполняет вставку по одной за раз, однако вы можете вставлять несколько строк с помощью вставки, следуя синтаксису:

insert into table_name (column1, .., columnn) values (val1, ..valn), ..., (val1, ..valn)

для получения дополнительной информации об использовании массовой загрузки см., например, Самый быстрый способ загрузить 1 млн строк в postgresql Даниэля Вестерманна.

вопрос о том, сколько строк вы должны вставлять за раз, зависит от длины строки, хорошее эмпирическое правило - вставлять 100 строк на оператор вставки.

Делайте INSERT в транзакции для ускорения.

Тестирование в bash без транзакции:

>  time ( for((i=0;i<100000;i++)); do echo 'INSERT INTO testtable (value) VALUES ('$i');'; done ) | psql root | uniq -c
 100000 INSERT 0 1

real    0m15.257s
user    0m2.344s
sys     0m2.102s

И с транзакцией:

> time ( echo 'BEGIN;' && for((i=0;i<100000;i++)); do echo 'INSERT INTO testtable (value) VALUES ('$i');'; done && echo 'COMMIT;' ) | psql root | uniq -c
      1 BEGIN
 100000 INSERT 0 1
      1 COMMIT

real    0m7.933s
user    0m2.549s
sys     0m2.118s