Re: performance of loading CSV data with COPY is 50 times faster thanPerl::DBI

On 1/31/20 10:24 AM, Matthias Apitz wrote:
> 
> Hello,
> 
> Since ages, we transfer data between different DBS (Informix, Sybase,
> Oracle, and now PostgreSQL) with our own written tool, based on
> Perl::DBI which produces a CSV like export in a common way, i.e. an
> export of Oracle can be loaded into Sybase and vice versa. Export and
> Import is done row by row, for some tables millions of rows.
> 
> We produced a special version of the tool to export the rows into a
> format which understands the PostgreSQL's COPY command and got to know
> that the import into PostgreSQL of the same data with COPY is 50 times
> faster than with Perl::DBI, 2.5 minutes ./. 140 minutes for around 6
> million rows into an empty table without indexes.
> 
> How can COPY do this so fast?

Well for one thing COPY does everything in a single transaction, which 
is both good and bad. The good is that it is fast, the bad is that a 
single error will rollback the entire operation.

COPY also uses it's own method for transferring data. For all the 
details see:

https://www.postgresql.org/docs/12/protocol-flow.html#PROTOCOL-COPY

> 
>     matthias
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com