Re: Question about durability and postgresql.

Hi Alfred,

These questions would be better posted to the general list, but I'll
take a crack at them here:

On 2/20/15 1:18 AM, Alfred Perlstein wrote:
> We have a combination of 9.3 and 9.4 databases used for logging of data.
>
> We do not need a strong durability guarantee, meaning it is ok if on
> crash a minute or two of data is lost from our logs. (This is just
> stats for our internal tool).
>
> I am looking at this page:
> http://www.postgresql.org/docs/9.4/static/non-durability.html
>
> And it's not clear which setting I should turn on.
>
> What we do NOT want is to lose the entire table or corrupt the database.
> We do want to gain speed though by not making DATA writes durable.
>
> Which setting is appropriate for this use case?
>
> At a glance it looks like a combination of
> 1) "Turn off synchronous_commit"
> and possibly:

This is perfectly safe from a database corruption standpoint. However,
you may lose transactions that the client thinks have committed but have
not yet been flushed to disk (only if the db/system crashes, though).
You can use commit_delay to increase the amount of time before
transactions are flushed and perhaps get efficiency by flushing multiple
transactions at once.

> 2) Increase checkpoint_segments and checkpoint_timeout ; this reduces
> the frequency of checkpoints, but increases the storage requirements
> of /pg_xlog.

These settings are really safe, but affect how long you'll spend in
recovery after a database crash. My practice is to increase these
settings as much as practical (considering disk space and recovery time)
not only because there are fewer fsyncs, but also fewer full page writes
for busy pages. The full page is only written once after each checkpoint.

> 3) Turn off full_page_writes; there is no need to guard against partial
> page writes.

This setting can lead to a corrupt database on a system failure. I'd
use the checkpoint settings above to reduce full-page writes instead and
see how that works out.

--
- David Steele
david(at)pgmasters(dot)net