Re: PostgreSQL Write Performance
От | Simon Riggs |
---|---|
Тема | Re: PostgreSQL Write Performance |
Дата | |
Msg-id | 1262770673.19367.60781.camel@ebony обсуждение исходный текст |
Ответ на | Re: PostgreSQL Write Performance (Yan Cheng Cheok <yccheok@yahoo.com>) |
Ответы |
Optimized Select Statement
|
Список | pgsql-general |
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 1is 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, interpretationof SQL statement...) However, Is there any way to further improve so that PostgreSQL write performance is nearto 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
В списке pgsql-general по дате отправления: