Re: performance of insert/delete/update

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Under MVCC, then, I am not convinced that bundling a bunch of writes into one
> transaction is faster until I see it demonstrated. I certainly see no
> performance gain on my system.

Are you running with fsync off?

The main reason for bundling updates into larger transactions is that
each transaction commit requires an fsync on the WAL log. If you have
fsync enabled, it is physically impossible to commit transactions faster
than one per revolution of the WAL disk, no matter how small the
transactions. (*) So it pays to make the transactions larger, not smaller.

On my machine I see a sizable difference (more than 2x) in the rate at
which simple INSERT statements are processed as separate transactions
and as large batches --- if I have fsync on. With fsync off, nearly no
difference.

regards, tom lane

(*) See recent proposals from Curtis Faith in pgsql-hackers about how
we might circumvent that limit ... but it's there today.