Re: performance of insert/delete/update

On 21 Nov 2002, Wei Weng wrote:

> On Thu, 2002-11-21 at 16:23, Josh Berkus wrote:
> > Wei,
> >
> > > There had been a great deal of discussion of how to improve the
> > > performance of select/sorting on this list, what about
> > > insert/delete/update?
> > >
> > > Is there any rules of thumb we need to follow? What are the
> > > parameters
> > > we should tweak to whip the horse to go faster?
> >
> > yes, lots of rules. Wanna be more specific? You wondering about
> > query structure, hardware, memory config, what?
> I am most concerned about the software side, that is query structures
> and postgresql config.

The absolutely most important thing to do to speed up inserts and updates
is to squeeze as many as you can into one transaction. Within reason, of
course. There's no great gain in putting more than a few thousand
together at a time. If your application is only doing one or two updates
in a transaction, it's going to be slower in terms of records written per
second than an application that is updating 100 rows in a transaction.

Reducing triggers and foreign keys on the inserted tables to a minimum
helps.

Inserting into temporary holding tables and then having a regular process
that migrates the data into the main tables is sometimes necessary if
you're putting a lot of smaller inserts into a very large dataset.
Then using a unioned view to show the two tables as one.

Putting WAL (e.g. $PGDATA/pg_xlog directory) on it's own drive(s).

Putting indexes that have to be updated during inserts onto their own
drive(s).

Performing regular vacuums on heavily updated tables.

Also, if your hardware is reliable, you can turn off fsync in
postgresql.conf. That can increase performance by anywhere from 2 to 10
times, depending on your application.