PostgreSQL 9.3.2 Documentation Chapter 14. Performance Tips 14.4. Populating a Database
The chapter shows many options, but I am paying attention to first two.
PostgreSQL commits each statement automatically. That means if you run an INSERT statement, it runs like this for each statement:
- Open a transaction
- Insert data
- Close the transaction
It is redundant. In this case, it becomes faster if you use autocommit like this:
BEGIN; -- the beginning of the transaction
END; -- the end of the transaction
PostgreSQL does not commit the statements between BEGIN and END.
COPY is also to use populate data, but the different point is that COPY does not offer autocommit. It means that PostgreSQL commit after all data is populated on a database.
Please refer to the document about how to use COPY statement.