Re: Performance of batch COMMIT

On Mon, Dec 19, 2005 at 11:44:15AM -0800, Benjamin Arai wrote:
> Each week I have to update a very large database. Currently I run a commit
> about every 1000 queries. This vastly increased performance but I am
> wondering if the performance can be increased further. I could send all of
> the queries to a file but COPY doesn't support plain queries such as UPDATE,
> so I don't think that is going to help. The only time I have to run a
> commit is when I need to make a new table. The server has 4GB of memory and
> fast everything else. The only postgresql.conf variable I have changed is
> for the shared_memory.

You should probably increase work_memory and maintenance_work_memory as
well; possibly some other things.

> Would sending all of the queries in a single query string increase
> performance?

The size of the query string shouldn't make any noticible difference
unless you're trying to plow through a lot of statements.

> What is the optimal batch size for commits?

The size you need to guarantee consistency. If you're going to need to
back a bunch of work out by hand if something fails mid-way through
you're just creating extra work for yourself.

The only reason I can think of for limiting transaction size is that I
think certain operations (like AFTER triggers) can end up holding on to
a lot of memory until the transaction commits. Though I'm not certain
about that, it's possible they only take memory until the command that
fired the triggers completes.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461