Search Postgresql Archives

Re: why autocommit mode is slow?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On 8 April 2011 05:06, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:
On Thu, Apr 7, 2011 at 2:59 PM, Szymon Guz <mabewlun@xxxxxxxxx> 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux