Re: COPY vs INSERT

David Roussel wrote:
>>COPY invokes all the same logic as INSERT on the server side
>>(rowexclusive locking, transaction log, updating indexes, rules).
>>The difference is that all the rows are inserted as a single
>>transaction. This reduces the number of fsync's on the xlog,
>>which may be a limiting factor for you. You'll want to crank
>>WAL_BUFFERS and CHECKPOINT_SEGMENTS to match, though.
>>One of my streams has 6K records; I run with WB=1000, CS=128.
>
>
> So what's the difference between a COPY and a batch of INSERT
> statements.  Also, surely, fsyncs only occur at the end of a
> transaction, no need to fsync before a commit has been issued, right?

I think COPY also has the advantage that for index updates it only grabs
the lock once, rather than grabbing and releasing for each row. But I
believe you are right that fsync only happens on COMMIT.

>
> David

John
=:->