Re: performance of insert/delete/update

On Mon, 25 Nov 2002, Josh Berkus wrote:

> Scott,
>
> > 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.
>
> Ouch!
>
> No need to get testy about it.
>
> Your test works as you said; the way I tried testing it before was different.
> Good to know. However, this approach is only useful if you are doing
> rapidfire updates or inserts coming off a single connection. But then it is
> *very* useful.

I didn't mean that in a testy way, it's just that after you've sat through
a fifteen minute wait while a 1000 records are inserted, you pretty
quickly switch to the method of inserting them all in one big
transaction. That's all.

Note that the opposite is what really gets people in trouble. I've seen
folks inserting rather large amounts of data, say into ten or 15 tables,
and their web servers were crawling under parallel load. Then, they put
them into a single transaction and they just flew.

The funny thing it, they've often avoided transactions because they
figured they'd be slower than just inserting the rows, and you kinda have
to make them sit down first before you show them the performance increase
from putting all those inserts into a single transaction.

No offense meant, really. It's just that you seemed to really doubt that
putting things into one transaction helped, and putting things into one
big transaction if like the very first postgresql lesson a lot of
newcomers learn. :-)