PostgreSQL 10 High Performance - Third Edition

Loading methods

The preferred path to get a lot of data into the database is by using the COPY command. This is the fastest way to insert a set of rows. If that's not practical and you have to use INSERT instead, you should try to include as many records as possible per commit, wrapping several into a BEGIN/COMMIT block. Most applications find that between 100 and 1,000 rows per commit gives the majority of the best performance possible in doing batch inserts, but it's still not going to be as fast as COPY.

From slowest to fastest, this is the usual ranking:

• Insert a single record at once.
• Insert larger blocks of records at one time.
• Use a single COPY at a time. This is what a standard pg_restore does.
• Multiple INSERT processes using larger ...