Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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
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.
