What is the limit on the number of rows that can be inserted in a single insert statement with PostgreSQL 9.4?

Question

PostgreSQL 9.4 accepts multiple rows as values blocks in a single insert statement. What is the maximum number of rows that can be inserted this way?

Answer 1

It depends on how your sample "row" looks like. In general, RAM and swap of your Postgres box will be your limit before you get out-of-memory error. For bulk insert it's recommended to:

• use COPY instead of INSERT,
• disable indexes, replication if it takes place
• tune database configuration settings in postgresql.conf, increase (maintenance_work_mem,checkpoint_segments)

Full list of suggestions can be found in Populating a Database article. In addition to this, Managing Kernel Resources and Resource Consumption articles are worth checking.