Question about durability and postgresql.

From: Alfred Perlstein <bright(at)mu(dot)org>
To: PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org>
Subject: Question about durability and postgresql.
Date: 2015-02-20 06:09:42
Message-ID: 1F8EECD4-689E-4F81-ABA8-DCB51A06E26A@mu.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

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:
2) Increase checkpoint_segments and checkpoint_timeout ; this reduces the frequency of checkpoints, but increases the storage requirements of /pg_xlog.
3) Turn off full_page_writes; there is no need to guard against partial page writes.

The point here is to never get a corrupt database, but in case of crash we might lose a few minutes of last transactions.

Any suggestions please?

thank you,
-Alfred

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2015-02-20 06:10:29 Re: POLA violation with \c service=
Previous Message Michael Paquier 2015-02-20 06:00:12 Re: Strange assertion using VACOPT_FREEZE in vacuum.c