Re: performance of insert/delete/update

On Fri, 22 Nov 2002, Josh Berkus wrote:

> Scott,
>
> > > > The absolutely most important thing to do to speed up inserts and
> > > > updates
> > > > is to squeeze as many as you can into one transaction.
>
> I was discussing this on IRC, and nobody could verify this assertion.
> Do you have an example of bunlding multiple writes into a transaction
> giving a performance gain?

Yes, my own experience.

It's quite easy to test if you have a database with a large table to play
with, use pg_dump to dump a table with the -d switch (makes the dump use
insert statements.) Then, make two versions of the dump, one which has a
begin;end; pair around all the inserts and one that doesn't, then use psql
-e to restore both dumps. The difference is HUGE. Around 10 to 20 times
faster with the begin end pairs.

I'd think that anyone who's used postgresql for more than a few months
could corroborate my experience.