Re: PostgreSQL Write Performance

> -----Original Message-----
> From: Yan Cheng Cheok [mailto:yccheok(at)yahoo(dot)com]
> Sent: Tuesday, January 05, 2010 10:30 PM
> To: Craig Ringer
> Cc: Dann Corbit; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] PostgreSQL Write Performance
>
> Thanks for the information. I perform benchmarking on a very simple
> table, on local database. (1 table, 2 fields with 1 is bigserial,
> another is text)
>
> ====================================================================
> INSERT INTO measurement_type(measurement_type_name) VALUES ('Hello')
> ====================================================================
>
> I turn synchronous_commit to off.
>
> To write a single row(local database), the time taken is in between
> 0.1ms and 0.5ms
>
> I try to compare this with flat text file.
>
> To write a single row(file), the time taken is in between 0.005ms and
> 0.05ms
>
> The different is big. Is this the expected result? Are you guys also
> getting the similar result?

Ten to one is not surprising. You will save quite a bit by doing a
prepare and a large number of inserts in a single transaction.

> I know there shall be some overhead to write to database compared to
> flat text file. (network communication, interpretation of SQL
> statement...) However, Is there any way to further improve so that
> PostgreSQL write performance is near to file?
>
> If not, I need to plan another strategy, to migrate my flat text file
> system, into PostgreSQL system smoothly.

Did you try the copy command? It will be very much faster.