14.4. Наполнение базы данных #
- 14.4.1. Отключите автофиксацию транзакций
- 14.4.2. Используйте
COPY- 14.4.3. Удалите индексы
- 14.4.4. Удалите ограничения внешних ключей
- 14.4.5. Увеличьте
maintenance_work_mem- 14.4.6. Увеличьте
max_wal_size- 14.4.7. Отключите архивацию WAL и потоковую репликацию
- 14.4.8. Выполните в конце
ANALYZE- 14.4.9. Несколько замечаний относительно pg_dump
- 14.4.2. Используйте
Довольно часто в начале или в процессе использования базы данных возникает необходимость загрузить в неё большой объём данных. В этом разделе приведены рекомендации, которые помогут сделать это максимально эффективно.
14.4.1. Отключите автофиксацию транзакций #
Выполняя серию команд INSERT, выключите автофиксацию транзакций и зафиксируйте транзакцию только один раз в самом конце. (В обычном SQL это означает, что нужно выполнить BEGIN до, и COMMIT после этой серии. Некоторые клиентские библиотеки могут делать это автоматически, в таких случаях нужно убедиться, что это так.) Если вы будете фиксировать каждое добавление по отдельности, Postgres Pro придётся проделать много действий для каждой добавляемой строки. Выполнять все операции в одной транзакции хорошо ещё и потому, что в случае ошибки добавления одной из строк произойдёт откат к исходному состоянию и вы не окажетесь в сложной ситуации с частично загруженными данными.
14.4.2. Используйте COPY #
Используйте COPY, чтобы загрузить все строки одной командой вместо серии INSERT. Команда COPY оптимизирована для загрузки большого количества строк; хотя она не так гибка, как INSERT, но при загрузке больших объёмов данных она влечёт гораздо меньше накладных расходов. Так как COPY — это одна команда, применяя её, нет необходимости отключать автофиксацию транзакций.
В случаях, когда COPY не подходит, может быть полезно создать подготовленный оператор INSERT с помощью PREPARE, а затем выполнять EXECUTE столько раз, сколько потребуется. Это позволит избежать накладных расходов, связанных с разбором и анализом каждой команды INSERT. В разных интерфейсах это может выглядеть по-разному; за подробностями обратитесь к описанию «подготовленных операторов» в документации конкретного интерфейса.
Заметьте, что с помощью COPY большое количество строк практически всегда загружается быстрее, чем с помощью INSERT, даже если используется PREPARE и серия операций добавления заключена в одну транзакцию.
COPY работает быстрее всего, если она выполняется в одной транзакции с командами CREATE TABLE или TRUNCATE. В таких случаях записывать WAL не нужно, так как в случае ошибки файлы, содержащие загружаемые данные, будут всё равно удалены. Однако это замечание справедливо, только когда параметр wal_level равен minimal, так как в противном случае все команды должны записывать свои изменения в WAL.
14.4.3. Удалите индексы #
Если вы загружаете данные в только что созданную таблицу, быстрее всего будет загрузить данные с помощью COPY, а затем создать все необходимые для неё индексы. На создание индекса для уже существующих данных уйдёт меньше времени, чем на последовательное его обновление при добавлении каждой строки.
Если вы добавляете данные в существующую таблицу, может иметь смысл удалить индексы, загрузить таблицу, а затем пересоздать индексы. Конечно, при этом надо учитывать, что временное отсутствие индексов может отрицательно повлиять на скорость работы других пользователей. Кроме того, следует дважды подумать, прежде чем удалять уникальные индексы, так как без них соответствующие проверки ключей не будут выполняться.
14.4.4. Удалите ограничения внешних ключей #
Как и с индексами, проверки, связанные с ограничениями внешних ключей, выгоднее выполнять «массово», а не для каждой строки в отдельности. Поэтому может быть полезно удалить ограничения внешних ключей, загрузить данные, а затем восстановить прежние ограничения. И в этом случае тоже приходится выбирать между скоростью загрузки данных и риском допустить ошибки в отсутствие ограничений.
Более того, когда вы загружаете данные в таблицу с существующими ограничениями внешнего ключа, для каждой новой строки добавляется запись в очередь событий триггера (так как именно срабатывающий триггер проверяет такие ограничения для строки). При загрузке многих миллионов строк очередь событий триггера может занять всю доступную память, что приведёт к недопустимой нагрузке на файл подкачки или даже к сбою команды. Таким образом, загружая большие объёмы данных, может быть не просто желательно, а необходимо удалять, а затем восстанавливать внешние ключи. Если же временное отключение этого ограничения неприемлемо, единственно возможным решением может быть разделение всей операции загрузки на меньшие транзакции.
14.4.5. Увеличьте maintenance_work_mem #
Ускорить загрузку больших объёмов данных можно, увеличив параметр конфигурации maintenance_work_mem на время загрузки. Это приведёт к увеличению быстродействия CREATE INDEX и ALTER TABLE ADD FOREIGN KEY. На скорость самой команды COPY это не повлияет, так что этот совет будет полезен, только если вы применяете какой-либо из двух вышеописанных приёмов.
14.4.6. Увеличьте max_wal_size #
Также массовую загрузку данных можно ускорить, изменив на время загрузки параметр конфигурации max_wal_size. Загружая большие объёмы данных, Postgres Pro вынужден увеличивать частоту контрольных точек по сравнению с обычной (которая задаётся параметром checkpoint_timeout), а значит и чаще сбрасывать «грязные» страницы на диск. Временно увеличив max_wal_size, можно уменьшить частоту контрольных точек и связанных с ними операций ввода-вывода.
14.4.7. Отключите архивацию WAL и потоковую репликацию #
Для загрузки больших объёмов данных в среде, где используется архивация WAL или потоковая репликация, быстрее будет сделать копию базы данных после загрузки данных, чем обрабатывать множество операций изменений в WAL. Чтобы отключить передачу изменений через WAL в процессе загрузки, отключите архивацию и потоковую репликацию, назначьте параметру wal_level значение minimal, archive_mode — off, а max_wal_senders — 0. Но имейте в виду, что изменённые параметры вступят в силу только после перезапуска сервера, а все ранее созданные резервные копии не будут пригодны для резервного сервера и восстановления архива, что может привести к потере данных.
Это не только поможет сэкономить время архивации и передачи WAL, но и непосредственно ускорит некоторые команды, потому что они не записывают в WAL ничего, если в wal_level установлен уровень minimal и текущая подтранзакция (или транзакция верхнего уровня) создала и опустошила таблицу или индекс, куда затем вносятся изменения. (Они могут гарантировать безопасность данных при сбое, не записывая их в WAL, а только выполнив fsync в конце, что будет гораздо дешевле.)
14.4.8. Выполните в конце ANALYZE #
Всякий раз, когда распределение данных в таблице значительно меняется, настоятельно рекомендуется выполнять ANALYZE. Эта рекомендация касается и загрузки в таблицу большого объёма данных. Выполнив ANALYZE (или VACUUM ANALYZE), вы тем самым обновите статистику по данной таблице для планировщика. Когда планировщик не имеет статистики или она не соответствует действительности, он не сможет правильно планировать запросы, что приведёт к снижению быстродействия при работе с соответствующими таблицами. Заметьте, что если включён демон автоочистки, он может запускать ANALYZE автоматически; подробнее об этом можно узнать в Подразделе 24.1.3 и Подразделе 24.1.6.
14.4.9. Несколько замечаний относительно pg_dump #
В скриптах загрузки данных, которые генерирует pg_dump, автоматически учитываются некоторые, но не все из этих рекомендаций. Чтобы загрузить данные, которые выгрузил pg_dump, максимально быстро, вам нужно будет выполнить некоторые дополнительные действия вручную. (Заметьте, что эти замечания относятся только к восстановлению данных, но не к выгрузке их. Следующие рекомендации применимы вне зависимости от того, загружается ли архивный файл pg_dump в psql или в pg_restore.)
По умолчанию pg_dump использует команду COPY и когда она выгружает полностью схему и данные, в сгенерированном скрипте она сначала предусмотрительно загружает данные, а потом создаёт индексы и внешние ключи. Так что в этом случае часть рекомендаций выполняется автоматически. Вам остаётся учесть только следующие:
Установите подходящие (то есть превышающие обычные) значения для
maintenance_work_memиmax_wal_size.Если вы используете архивацию WAL или потоковую репликацию, по возможности отключите их на время восстановления. Для этого перед загрузкой данных, присвойте параметру
archive_modeзначениеoff,wal_level—minimal, аmax_wal_senders— 0. Закончив восстановление, верните их обычные значения и сделайте свежую базовую резервную копию.Поэкспериментируйте с режимами параллельного копирования и восстановления команд pg_dump и pg_restore, и подберите оптимальное число параллельных заданий. Параллельное копирование и восстановление данных, управляемое параметром
-j, должно дать значительный выигрыш в скорости по сравнению с последовательным режимом.Если это возможно в вашей ситуации, восстановите все данные в рамках одной транзакции. Для этого передайте параметр
-1или--single-transactionкоманде psql или pg_restore. Но учтите, что в этом режиме даже незначительная ошибка приведёт к откату всех изменений и часы восстановления будут потрачены зря. В зависимости от того, насколько взаимосвязаны данные, предпочтительнее может быть вычистить их вручную. КомандыCOPYбудут работать максимально быстро, когда они выполняются в одной транзакции и архивация WAL выключена.Если на сервере баз данных установлено несколько процессоров, полезным может оказаться параметр
--jobsкоманды pg_restore. С его помощью можно выполнить загрузку данных и создание индексов параллельно.После загрузки данных запустите
ANALYZE.
При выгрузке данных без схемы тоже используется команда COPY, но индексы, как обычно и внешние ключи, при этом не удаляются и не пересоздаются. [14] Поэтому, загружая только данные, вы сами должны решить, нужно ли для ускорения загрузки удалять и пересоздавать индексы и внешние ключи. При этом будет так же полезно увеличить параметр max_wal_size, но не maintenance_work_mem; его стоит менять, только если вы впоследствии пересоздаёте индексы и внешние ключи вручную. И не забудьте выполнить ANALYZE после; подробнее об этом можно узнать в Подразделе 24.1.3 и Подразделе 24.1.6.
[14] Вы можете отключить внешние ключи, используя параметр --disable-triggers — но при этом нужно понимать, что тем самым вы не просто отложите, а полностью выключите соответствующие проверки, что позволит вставить недопустимые данные.
14.4. Populating a Database #
One might need to insert a large amount of data when first populating a database. This section contains some suggestions on how to make this process as efficient as possible.
14.4.1. Disable Autocommit #
When using multiple INSERTs, turn off autocommit and just do one commit at the end. (In plain SQL, this means issuing BEGIN at the start and COMMIT at the end. Some client libraries might do this behind your back, in which case you need to make sure the library does it when you want it done.) If you allow each insertion to be committed separately, Postgres Pro is doing a lot of work for each row that is added. An additional benefit of doing all insertions in one transaction is that if the insertion of one row were to fail then the insertion of all rows inserted up to that point would be rolled back, so you won't be stuck with partially loaded data.
14.4.2. Use COPY #
Use COPY to load all the rows in one command, instead of using a series of INSERT commands. The COPY command is optimized for loading large numbers of rows; it is less flexible than INSERT, but incurs significantly less overhead for large data loads. Since COPY is a single command, there is no need to disable autocommit if you use this method to populate a table.
If you cannot use COPY, it might help to use PREPARE to create a prepared INSERT statement, and then use EXECUTE as many times as required. This avoids some of the overhead of repeatedly parsing and planning INSERT. Different interfaces provide this facility in different ways; look for “prepared statements” in the interface documentation.
Note that loading a large number of rows using COPY is almost always faster than using INSERT, even if PREPARE is used and multiple insertions are batched into a single transaction.
COPY is fastest when used within the same transaction as an earlier CREATE TABLE or TRUNCATE command. In such cases no WAL needs to be written, because in case of an error, the files containing the newly loaded data will be removed anyway. However, this consideration only applies when wal_level is minimal as all commands must write WAL otherwise.
14.4.3. Remove Indexes #
If you are loading a freshly created table, the fastest method is to create the table, bulk load the table's data using COPY, then create any indexes needed for the table. Creating an index on pre-existing data is quicker than updating it incrementally as each row is loaded.
If you are adding large amounts of data to an existing table, it might be a win to drop the indexes, load the table, and then recreate the indexes. Of course, the database performance for other users might suffer during the time the indexes are missing. One should also think twice before dropping a unique index, since the error checking afforded by the unique constraint will be lost while the index is missing.
14.4.4. Remove Foreign Key Constraints #
Just as with indexes, a foreign key constraint can be checked “in bulk” more efficiently than row-by-row. So it might be useful to drop foreign key constraints, load data, and re-create the constraints. Again, there is a trade-off between data load speed and loss of error checking while the constraint is missing.
What's more, when you load data into a table with existing foreign key constraints, each new row requires an entry in the server's list of pending trigger events (since it is the firing of a trigger that checks the row's foreign key constraint). Loading many millions of rows can cause the trigger event queue to overflow available memory, leading to intolerable swapping or even outright failure of the command. Therefore it may be necessary, not just desirable, to drop and re-apply foreign keys when loading large amounts of data. If temporarily removing the constraint isn't acceptable, the only other recourse may be to split up the load operation into smaller transactions.
14.4.5. Increase maintenance_work_mem #
Temporarily increasing the maintenance_work_mem configuration variable when loading large amounts of data can lead to improved performance. This will help to speed up CREATE INDEX commands and ALTER TABLE ADD FOREIGN KEY commands. It won't do much for COPY itself, so this advice is only useful when you are using one or both of the above techniques.
14.4.6. Increase max_wal_size #
Temporarily increasing the max_wal_size configuration variable can also make large data loads faster. This is because loading a large amount of data into Postgres Pro will cause checkpoints to occur more often than the normal checkpoint frequency (specified by the checkpoint_timeout configuration variable). Whenever a checkpoint occurs, all dirty pages must be flushed to disk. By increasing max_wal_size temporarily during bulk data loads, the number of checkpoints that are required can be reduced.
14.4.7. Disable WAL Archival and Streaming Replication #
When loading large amounts of data into an installation that uses WAL archiving or streaming replication, it might be faster to take a new base backup after the load has completed than to process a large amount of incremental WAL data. To prevent incremental WAL logging while loading, disable archiving and streaming replication, by setting wal_level to minimal, archive_mode to off, and max_wal_senders to zero. But note that changing these settings requires a server restart, and makes any base backups taken before unavailable for archive recovery and standby server, which may lead to data loss.
Aside from avoiding the time for the archiver or WAL sender to process the WAL data, doing this will actually make certain commands faster, because they do not to write WAL at all if wal_level is minimal and the current subtransaction (or top-level transaction) created or truncated the table or index they change. (They can guarantee crash safety more cheaply by doing an fsync at the end than by writing WAL.)
14.4.8. Run ANALYZE Afterwards #
Whenever you have significantly altered the distribution of data within a table, running ANALYZE is strongly recommended. This includes bulk loading large amounts of data into the table. Running ANALYZE (or VACUUM ANALYZE) ensures that the planner has up-to-date statistics about the table. With no statistics or obsolete statistics, the planner might make poor decisions during query planning, leading to poor performance on any tables with inaccurate or nonexistent statistics. Note that if the autovacuum daemon is enabled, it might run ANALYZE automatically; see Section 24.1.3 and Section 24.1.6 for more information.
14.4.9. Some Notes about pg_dump #
Dump scripts generated by pg_dump automatically apply several, but not all, of the above guidelines. To restore a pg_dump dump as quickly as possible, you need to do a few extra things manually. (Note that these points apply while restoring a dump, not while creating it. The same points apply whether loading a text dump with psql or using pg_restore to load from a pg_dump archive file.)
By default, pg_dump uses COPY, and when it is generating a complete schema-and-data dump, it is careful to load data before creating indexes and foreign keys. So in this case several guidelines are handled automatically. What is left for you to do is to:
Set appropriate (i.e., larger than normal) values for
maintenance_work_memandmax_wal_size.If using WAL archiving or streaming replication, consider disabling them during the restore. To do that, set
archive_modetooff,wal_leveltominimal, andmax_wal_sendersto zero before loading the dump. Afterwards, set them back to the right values and take a fresh base backup.Experiment with the parallel dump and restore modes of both pg_dump and pg_restore and find the optimal number of concurrent jobs to use. Dumping and restoring in parallel by means of the
-joption should give you a significantly higher performance over the serial mode.Consider whether the whole dump should be restored as a single transaction. To do that, pass the
-1or--single-transactioncommand-line option to psql or pg_restore. When using this mode, even the smallest of errors will rollback the entire restore, possibly discarding many hours of processing. Depending on how interrelated the data is, that might seem preferable to manual cleanup, or not.COPYcommands will run fastest if you use a single transaction and have WAL archiving turned off.If multiple CPUs are available in the database server, consider using pg_restore's
--jobsoption. This allows concurrent data loading and index creation.Run
ANALYZEafterwards.
A data-only dump will still use COPY, but it does not drop or recreate indexes, and it does not normally touch foreign keys. [14] So when loading a data-only dump, it is up to you to drop and recreate indexes and foreign keys if you wish to use those techniques. It's still useful to increase max_wal_size while loading the data, but don't bother increasing maintenance_work_mem; rather, you'd do that while manually recreating indexes and foreign keys afterwards. And don't forget to ANALYZE when you're done; see Section 24.1.3 and Section 24.1.6 for more information.
[14] You can get the effect of disabling foreign keys by using the --disable-triggers option — but realize that that eliminates, rather than just postpones, foreign key validation, and so it is possible to insert bad data if you use it.
