Re: performance of insert/delete/update

Tim Gardner <tgardner(at)codeHorse(dot)com> writes:
>> All this means that your inserts don't block anyone else's reads as well.
>> This means that when you commit, all postgresql does is make them visible.

> Exactly the kind of explanation/understanding I was hoping for!

There's another point worth making. What Scott was pointing out is that
whether you commit or roll back a transaction costs about the same, in
Postgres, as far as tuple update processing is concerned. At the end of
a transaction, we have both new (inserted/updated) and old
(deleted/replaced) tuples laying about in the database. Commit marks
the transaction committed in pg_clog; abort marks it aborted instead;
neither one lifts a finger to touch the tuples. (Subsequent visitors
to the tuples will mark them "good" or "dead" based on consulting
pg_clog, but we don't try to do that during transaction commit.)

But having said all that, transaction commit is more expensive than
transaction abort, because we have to flush the transaction commit
WAL record to disk before we can report "transaction successfully
committed". That means waiting for the disk to spin. Transaction abort
doesn't have to wait --- that's because if there's a crash and the abort
record never makes it to disk, the default assumption on restart will be
that the transaction aborted, anyway.

So the basic reason that it's worth batching multiple updates into one
transaction is that you only wait for the commit record flush once,
not once per update. This makes no difference worth mentioning if your
updates are big, but on modern hardware you can update quite a few
individual rows in the time it takes the disk to spin once.

(BTW, if you set fsync = off, then the performance difference goes away,
because we don't wait for the commit record to flush to disk ... but
then you become vulnerable to problems after a system crash.)

regards, tom lane