8

On Postgres (>10) is there any performance gain on using multi-row insert:

insert into tbl (c1, c2) values
(v1, v2)
(v3, v4);

over:

begin;
insert into tbl (c1, c2) values (v1, v2);
insert into tbl (c1, c2) values (v3, v4);
commit;

I've shamelessly copied the code snippet from the below question, but this question is different as it is about transactions Multi-row insert vs multiple single row inserts

1
  • The first one will be faster (at least if you have substantially more than two rows)
    – user1822
    Dec 14, 2018 at 11:28

1 Answer 1

11

Yes, it can make a huge difference.

For example, I did a single-statement insert with one million value lists, and it took 7 seconds. I did one million single-row inserts all in one transaction, and it took 110 seconds. That is a 15-fold difference.

This is for a case where the table had no constraints or indexes. Having a single index narrowed the gap down to only be 10 times faster, by slowing both approaches down by few seconds. Once the index is large enough that it can't be cached in RAM, then both methods will get so much slower that the difference between them may become relatively insignificant.

Note that the accepted answer to the related question you linked is not correct. Index maintenance is done the same way whether it is a multi-valued insert or a bunch of single-valued inserts--at least for BTree indexes. There has been talk of optimizing the index maintenance, but if the optimizations are implemented they will probably be done in such a way that it applies to both types of INSERT.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.