PostgreSQL replication with fsync disabled?

Question

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.

• user1822: Turning off fsync does not mean WAL is not written, it's still written - just not in a reliable way.
• sivann: 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.
• Mladen Uzelac: There are unlogged tables in PostgreSQL. Did you try that?
• Colin 't Hart: You should design your application for your performance requirements. Configuring your DBMS in strange ways is probably not going to get you there.

Answer 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.

• sivann: What is a nonvolatile write buffer? You mean some kind of hardware, or application-level buffer like a queue to buffer writes?
• jjanes: Hardware. Also called BBU or NVRAM. Good RAID controllers often have them built in.
• sivann: 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.