Re: One long transaction or multiple short transactions?

>> How many cores do you have on that machine?

Test if limiting number of simultaneous feeds, like bringing their number down to half of your normal connections has the same positive effect.

<< 

 

I am told 32 cores on a LINUX VM. The operators have tried limiting the number of threads. They feel that the number of connections is optimal. However, under the same conditions they noticed a sizable boost in performance if the same import was split into two successive imports which had shorter transactions.

 

I am just looking to see if there is any reason to think that lock contention (or anything else) over longer vs. shorter single-row-write transactions under the same conditions might explain this.

I don't think inserts can cause contention on the server. Insert do not lock tables during the transaction. You may have contention on sequence but it won't vary with transaction size.

Have you checked the resource usage (CPU,memory) on the client side ?

How do you insert rows ? Do you use plain postgres API ?

Regards,
Laurent