Re: batch inserts are "slow"

petrilli(at)gmail(dot)com (Christopher Petrilli) writes:
> On 5/2/05, Tim Terlegård <tim(at)se(dot)linux(dot)org> wrote:
>> Howdy!
>>
>> I'm converting an application to be using postgresql instead of
>> oracle. There seems to be only one issue left, batch inserts in
>> postgresql seem significant slower than in oracle. I have about 200
>> batch jobs, each consisting of about 14 000 inserts. Each job takes
>> 1.3 seconds in postgresql and 0.25 seconds in oracle. With 200 jobs
>> this means several more minutes to complete the task. By fixing
>> this I think the application using postgresql over all would be
>> faster than when using oracle.
>
> Just as on Oracle you would use SQL*Loader for this application, you
> should use the COPY syntax for PostgreSQL. You will find it a lot
> faster. I have used it by building the input files and executing
> 'psql' with a COPY command, and also by using it with a subprocess,
> both are quite effective.

I'd suggest taking a peek at the PGForge project, pgloader
<http://pgfoundry.org/projects/pgloader/>.

This is intended to provide somewhat analagous functionality to
SQL*Loader; a particularly useful thing about it is that it will load
those records that it can, and generate a file consisting of just the
failures.

It uses COPY, internally, so it does run reasonably fast.

To the extent to which it is inadequate, it would be neat to see some
enhancements...
--
(format nil "~S(at)~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/>