PostgreSQL replication with fsync disabled

memorypostgresqlreplication

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.

Best Answer

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.