Re: why autocommit mode is slow?

On 8 April 2011 05:06, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:

> On Thu, Apr 7, 2011 at 2:59 PM, Szymon Guz <mabewlun(at)gmail(dot)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