On Thu, May 16, 2013 at 7:46 AM, Cuong Hoang <climbingrose@gmail.com> wrote:
> For our application, a few seconds of data loss is acceptable.
If a few seconds of data loss is acceptable, I would seriously look at
the synchronous_commit setting and think about turning that off rather
than risk silent corruption with non-enterprise SSDs.
http://www.postgresql.org/docs/9.2/interactive/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT
"Unlike fsync, setting this parameter to off does not create any risk
of database inconsistency: an operating system or database crash might
result in some recent allegedly-committed transactions being lost, but
the database state will be just the same as if those transactions had
been aborted cleanly. So, turning synchronous_commit off can be a
useful alternative when performance is more important than exact
certainty about the durability of a transaction."
With a default wal_writer_delay setting of 200ms, you will only be at
risk of losing at most 600ms of transactions in the event of an
unexpected crash or power loss, but write performance should go up a
huge amount, especially if they are a lot of small writes as you
describe.
-Dave