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.
Would sending all of the queries in a single query string increase performance?
What is the optimal batch size for commits?
Are there any postgresql.conf variable that should be tweaked?
Anybody have any suggestions?