14.4.4. Remove Foreign Key Constraints
14.4.5. Increasemaintenance_work_mem
14.4.7. Disable WAL Archival and Streaming Replication
14.4.9. Some Notes Aboutpg_dump
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.
When using multipleINSERTs, turn off autocommit and just do one commit at the end. (In plain SQL, this means issuingBEGINat the start andCOMMITat 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,PostgreSQLis 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.
COPYUseCOPYto load all the rows in one command, instead of using a series ofINSERTcommands. TheCOPYcommand is optimized for loading large numbers of rows; it is less flexible thanINSERT, but incurs significantly less overhead for large data loads. SinceCOPYis a single command, there is no need to disable autocommit if you use this method to populate a table.
If you cannot useCOPY, it might help to usePREPAREto create a preparedINSERTstatement, and then useEXECUTEas many times as required. This avoids some of the overhead of repeatedly parsing and planningINSERT. Different interfaces provide this facility in different ways; look for“prepared statements”in the interface documentation.
Note that loading a large number of rows usingCOPYis almost always faster than usingINSERT, even ifPREPAREis used and multiple insertions are batched into a single transaction.
COPYis fastest when used within the same transaction as an earlierCREATE TABLEorTRUNCATEcommand. 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 whenwal_levelisminimalas all commands must write WAL otherwise.
If you are loading a freshly created table, the fastest method is to create the table, bulk load the table's data usingCOPY, 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.
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 benecessary, 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.
maintenance_work_memTemporarily increasing themaintenance_work_memconfiguration variable when loading large amounts of data can lead to improved performance. This will help to speed upCREATE INDEXcommands andALTER TABLE ADD FOREIGN KEYcommands. It won't do much forCOPYitself, so this advice is only useful when you are using one or both of the above techniques.
max_wal_sizeTemporarily increasing themax_wal_sizeconfiguration variable can also make large data loads faster. This is because loading a large amount of data intoPostgreSQLwill cause checkpoints to occur more often than the normal checkpoint frequency (specified by thecheckpoint_timeoutconfiguration variable). Whenever a checkpoint occurs, all dirty pages must be flushed to disk. By increasingmax_wal_sizetemporarily during bulk data loads, the number of checkpoints that are required can be reduced.
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 settingwal_leveltominimal,archive_modetooff, andmax_wal_sendersto zero. But note that changing these settings requires a server restart.
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 are designed not to write WAL at all ifwal_levelisminimal. (They can guarantee crash safety more cheaply by doing anfsyncat the end than by writing WAL.) This applies to the following commands:
CREATE TABLE AS SELECT
CREATE INDEX(and variants such asALTER TABLE ADD PRIMARY KEY)
ALTER TABLE SET TABLESPACE
CLUSTER
COPY FROM, when the target table has been created or truncated earlier in the same transaction
ANALYZEAfterwardsWhenever you have significantly altered the distribution of data within a table, runningANALYZEis strongly recommended. This includes bulk loading large amounts of data into the table. RunningANALYZE(orVACUUM 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 runANALYZEautomatically; seeSection 24.1.3andSection 24.1.6for more information.
Dump scripts generated bypg_dumpautomatically apply several, but not all, of the above guidelines. To reload apg_dumpdump 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 withpsqlor usingpg_restoreto load from apg_dumparchive file.)
By default,pg_dumpusesCOPY, 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 formaintenance_work_memandmax_wal_size.
If using WAL archiving or streaming replication, consider disabling them during the restore. To do that, setarchive_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 bothpg_dumpandpg_restoreand 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 topsqlorpg_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 usingpg_restore's--jobsoption. This allows concurrent data loading and index creation.
RunANALYZEafterwards.
A data-only dump will still useCOPY, but it does not drop or recreate indexes, and it does not normally touch foreign keys.[10]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 increasemax_wal_sizewhile loading the data, but don't bother increasingmaintenance_work_mem; rather, you'd do that while manually recreating indexes and foreign keys afterwards. And don't forget toANALYZEwhen you're done; seeSection 24.1.3andSection 24.1.6for more information.
[10]You can get the effect of disabling foreign keys by using the--disable-triggersoption — 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.