Re: Postgres batch write very slow - what to do

femski wrote:
> Folks !
>
> I have a batch application that writes approx. 4 million rows into a narrow
> table. I am using JDBC addBatch/ExecuteBatch with auto commit turned off.
> Batch size is 100. So far I am seeing Postgres take roughly five times the
> time it takes to do this in the Oracle.

If you are using 8.2 could you try with the multi value inserts?

insert into foo(bar) values (bang) (bong) (bing) ...?

>
> I have played with many parameters. Only one that seems to have any affect
> is fsync - but thats only 10% or so.
> Initially I got the warning that checkpoints were happening too often so I
> increased the segments to 24. Warnings stopped, but no real improvement in
> performance.
>
> Is postgres really that slow ? What am I missing ?
>
> Here are the changes to my postgressql.cong file.
>
> shared_buffers = 768MB
> work_mem = 256MB
> maintenance_work_mem = 128MB
> fsync = off
>
> checkpoint_segments = 24
> autovacuum = on
>
> Thank you,
>
> -Sanjay

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/