Re: Performance of batch COMMIT - Mailing list pgsql-general

From Jim C. Nasby
Subject Re: Performance of batch COMMIT
Date
Msg-id 20051219223115.GU28771@pervasive.com
Whole thread Raw
In response to Performance of batch COMMIT  ("Benjamin Arai" <barai@cs.ucr.edu>)
List pgsql-general
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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

pgsql-general by date:

Previous
From: "Jim C. Nasby"
Date:
Subject: Re: Versioning Schema/Stored Procedures
Next
From: "Karl O. Pinc"
Date:
Subject: Re: Converting seconds past midnight to a time