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@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
