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 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 CREATE INDEX and ALTER TABLE ADD FOREIGN KEY, presuming you've followed the advice mentioned earlier to do those in a batch after loading.
• checkpoint_segments: Much higher values than that would normally be safe are acceptable here to spread out checkout I/O, because crash recovery time and disk space aren't so important before the server goes live. 128-256 are common values for...