0

I need to run PostgreSQL in-memory (for performance reasons), so I intend to disable fsync, which means that no writes will be sent to the WAL.

However, as part of my scheme to meet another requirement (that the in-memory database have somewhere to recover from when volatile memory is lost), I would like to stream or otherwise push writes to a replica. However, the PostgreSQL hot standby capability is based on the WAL. Clearly, I can't use this.

How could I achieve these goals using PostgreSQL features?

Thanks.

|improve this question
  • 2
    Turning off fsync does not mean WAL is not written, it's still written - just not in a reliable way. – a_horse_with_no_name May 20 '14 at 22:00
  • 1
    you don't state what is the original problem you're trying to solve. To use replication reliably your DB must wait for the replica to commit transactions (writes) to disk. So, writes will not be faster but limited to replica's disk performance + network lag. To increase writes per second, reduce transactions per second. Make the application write in batch, by buffering writes in some fast persistent storage like RabbitMQ and then commiting lots of them in one transaction. – sivann May 24 '14 at 8:02
  • 1
    There are unlogged tables in PostgreSQL. Did you try that? – Mladen Uzelac Nov 23 '14 at 8:38
  • You should design your application for your performance requirements. Configuring your DBMS in strange ways is probably not going to get you there. – Colin 't Hart Jan 27 '15 at 10:21
1

Turning fsync off does not prevent writes to WAL, it only prevents those writes from being explicitly synced. So you can still use this to feed hot standby. But if your database is so busy you need to do this, I wonder how well the hot standby can keep up with it.

Also, turning synchronous_commit off will get you much of the benefit without the corruption risk of turning fsync off.

Finally, anyone running such busy system should have the budget for some kind of nonvolatile write buffer, which would render fsyncs much less of a performance problem.

|improve this answer
  • What is a nonvolatile write buffer? You mean some kind of hardware, or application-level buffer like a queue to buffer writes? – sivann May 24 '14 at 7:58
  • Hardware. Also called BBU or NVRAM. Good RAID controllers often have them built in. – jjanes May 26 '14 at 18:53
  • Yes, this helps, but BBU is not always as fast as one may think: if you have a 2-controller (HA) storage, writes have to be propagated to both controllers' BBU and inter-controller data path can be slow, a few Gbps. You can disable write cache replication on some controllers but then you loose controller HA. – sivann May 27 '14 at 10:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.