Multi-row insert vs multiple single row inserts

Question

In my app I do multi-row inserts when I can just because it reduces the number of round trips between the db and the app.

However, I was curious, is there any other advantages? For example, if multiple rows are inserted at once like this:

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

versus:

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

and the table has an index, is the index calculated once in the first case, and twice in the second case? Or is it always once per insert? Assume that both queries are in the same transaction.

I'm using PostgreSQL.

Answer 1

I think @a_horse_with_no_name is right about that the index is updated once per statement, because if the statement has not completed its execution the data will not be visible since it is in transaction. And the definition of a statement includes having multiple values

And accoriding to the docs here index creation/update works more efficient with batches than single statements.

There is also a nice article from Peter Manis about the performance of various Insert methods which I can recommend.

Another thing to keep in mind is the FILLFACTOR of the index as it does have an impact on performance as Fabien Coelho describes here.

Answer 2

I don't be believe there are currently any optimization differences in the area of index maintenance in this regard.

In addition to network overhead, the other advantages of the multi-value statement are in the parsing, locking, etc. (Even though the table lock on tbl is taken by the first insert statement and then maintained throughout the rest of the transaction, each insert statement still has to verify that the lock is held, and this verification is not free)