Re: why autocommit mode is slow?

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Szymon Guz <mabewlun(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: why autocommit mode is slow?
Date: 2011-04-08 03:06:27
Message-ID: BANLkTinPtLLvDjzvgcsGv2D8bWqHCrP9Hw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Szymon Guz 2011-04-08 07:41:32 Re: why autocommit mode is slow?
Previous Message Craig Ringer 2011-04-08 02:05:13 Re: why autocommit mode is slow?