You can use COPY over DBI. https://metacpan.org/pod/DBD::Pg#COPY-support
On Fri, Jan 31, 2020 at 2:03 PM Pavel Stehule <pavel.steh...@gmail.com> wrote: > > > pá 31. 1. 2020 v 19:25 odesílatel Matthias Apitz <g...@unixarea.de> > napsal: > >> >> 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? >> > > Probably there are more reasons > > 1. probably DBI implementation is not too effective (for Postgres), maybe > because COPY is not fault tolerant > 2. postgres has not implicit plan cache, so every INSERT planned again and > again > 3. COPY bypass planner and executor and it has very effective network > communication > 4. with COPY you have a sure so autocommit is disabled. > > Regards > > Pavel > > > >> >> matthias >> >> -- >> Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ >> +49-176-38902045 >> Public GnuPG key: http://www.unixarea.de/key.pub >> >> >>
