One of my PostgreSQL servers hosts several (1-3) databases which receive a constant stream of data. The data is not particularly structured, it amounts to the current time and a variety of observed data for that particular instant. The data rate is fairly high; it works out to about a gigabyte a day for one database, about a tenth of that for another one. I don't expect this rate to increase. Read performance is a much lower priority and is currently acceptable.
In the logs I have this message:
LOG: checkpoints are occurring too frequently (15 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
This value is currently set to 16, which is courtesy of pgtune
.
What are the settings I should consider to improve write performance? I would prefer to keep as much safety as possible. Considering the volume of data coming in, I could accept losing some recent data in a failure as long as the bulk of the data were intact.
Edit: I'm using PostgreSQL 9.0 for now, but I plan to upgrade to 9.1. I am not posting the hardware details because while I acknowledge their importance, I ultimately will be needing to make this optimization on several machines with very diverse hardware. If the hardware is essential to the answer, please give me the general information so I can apply the answer to machines with different hardware configurations.
checkpoint_segments
as recommended? What happened?