On 8 April 2011 05:06, Scott Marlowe <scott.marl...@gmail.com> wrote:
> On Thu, Apr 7, 2011 at 2:59 PM, Szymon Guz <mabew...@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. > Thank you all for clarification. regards Szymon