On Sun, Aug 31, 2014 at 10:10 AM, Peter Eisentraut <peter_e@gmx.net> wrote: > > On 8/30/14 2:26 AM, Jeff Janes wrote: > > But there cases were people use COPY in a loop with a small amount of > > data in each statement. > > What would be the reason for doing that? >
I used that to the same thing many times. In a company that I was employed we developed scripts to migrate data from one database do another.
The first version we used INSERT statements and was very very slow. Then we wrote a second version changing the INSERT by COPY statements. The performance was very better, but we believe that could be better, so in the third version we created some kind of "cache" (using arrays) to accumulate the records in memory then after N rows we build the COPY statement with the cache contents and run it. This was a really good performance improvement.
It's my use case to we have a feature to postpone the heap_sync in COPY statements. I don't know if it's a feature that a lot of people wants, but IMHO it could be nice to improve the bulk load operations.
