Setting Synchronous_commit to off in Postgres in AWS RDS

Question

0

We are experiencing significant delays in our PostgreSQL database due to LWLock:WALWrite and LWLock:WALSync waits, which are causing commits to pile up.

To address this, we have undertaken several tuning activities, including increasing IOPS and making code changes. Additionally, we are considering turning off synchronous commits. Our initial performance tests indicate substantial improvements, although we understand this may result in up to 0.5 seconds of data loss.

Given that we have an RPO (Recovery Point Objective) of 30 minutes, and considering that our database is hosted in AWS with Multi-AZ enabled, are there any other major concerns we should be aware of beyond the potential data loss?

Answer 1

Having some data missing after a major crash which triggered fail-over or restoration and recovery is a different thing than having data missing after some minor glitch that people might not even know happened.

If you have multiple session committing at the same time, have you looked into commit_delay and commit_siblings?

• sanj: I think commit_delay with increase the transaction latency , right ? We are trying to reduce the same. Please let me know
• jjanes: The idea is that by making the first transaction wait for more to show up, you can flush them all together in one IO. Decreasing the latency on average even though it is increased for a minority of the transactions.