Re: Best COPY Performance

Jim C. Nasby wrote:
> Well, given that perl is using an entire CPU, it sounds like you should
> start looking either at ways to remove some of the overhead from perl,
> or to split that perl into multiple processes.

I use Perl for big database copies (usually with some processing/transformation along the way) and I've never seen 100% CPU usage except for brief periods, even when copying BLOBS and such. My typical copy divides operations into blocks, for example doing

N = 0
while (more rows to go) {
begin transaction
select ... where primary_key > N order by primary_key limit 1000
while (fetch a row)
insert into ...
N = (highest value found in last block)
commit
}

Doing it like this in Perl should keep Postgres busy, with Perl using only moderate resources. If you're seeing high Perl CPU usage, I'd look first at the Perl code.

Craig