Re: PostgreSQL Write Performance

On Tue, 2010-01-05 at 22:29 -0800, Yan Cheng Cheok wrote:
> 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

x32 improvement sounds pretty good, IMHO

> 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?
>
> 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?

Postgres provides
* data validation on input
* foreign keys to cross-validate fields in your data input
* transactions to allow roll-back of failed data
* auto-generation timestamps, if required
* auto-generated, easily restartable generation of unique keys
* crash recovery of your database changes (apart from very recent data)
* multiple concurrent writers, so multiple measurement machines can
record to just a single database
* readers do not block writers
* SQL interface to run analytical queries against data
* allows you to index data to allow fast retrieval of results
* triggers to allow you to augment your data collection application with
additional features over time
...

If you don't want these features, then yes, they are overheads.

If you're interested in funding feature development, I'd be more than
happy to investigate further optimising your specific case. I think an
auto-batching mode for INSERT statements should be possible, so that we
save up consecutive SQL statements and apply them as a single
transaction without requiring any client changes.

--
Simon Riggs www.2ndQuadrant.com