On Thu, Apr 7, 2011 at 2:59 PM, Szymon Guz <mabewlun@gmail.com> wrote: > Hi, > this is maybe a stupid question, but I don't know how to explain to my > coworkers why performing many inserts in autocommit mode is so much slower > than making all of them in one transaction. > Why is that so slow?
The real issue is that transactions have fairly high overhead. When in autocommit mode, the cost of the transaction is much higher than the individual insert, so it's relatively slow. OTOH, when inserting a dozen or a hundred or a thousand rows, the transactional overhead to build up and tear down the transaction becomes smaller and smaller in comparison to the inserts. The inserts in each instance cost the same / take just as long, but the transactional wrapping is only paid for once in the large transaction scenario, and it's paid every time in the autocommit.
The good news is postgresql has no real practical limit to transaction size, and the theoretical limit is VERY large (like 2B or so statements I believe.) So no error about running out of rollback space etc.