PostgreSQL 10 High Performance - Third Edition

Tuning for bulk loads

The most important thing to do in order to speed up bulk loads is to turn off any indexes or foreign key constraints on the table. It's more efficient to build indexes in bulk and the result will be less fragmented. Constraint checks are much faster to check in bulk, too.

There are a few postgresql.conf values that you can set outside of their normal range specifically to accelerate bulk loads:

• maintenance_work_mem: Increase this to a much larger value than you'd normally run your server with. 1 GB is not unreasonable on a server with >16 GB of RAM nowadays. This speeds up CREATE INDEX and ALTER TABLE ADD FOREIGN KEY, presuming you've followed the advice mentioned earlier to do those in a batch after loading.
• max_wal_size ...