Re: why autocommit mode is slow?

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.