On Fri, May 1, 2020 at 9:27 AM Thawley, Peter <pthawley@xxxxxxxxxx> wrote:
Hi Naveen
I was a little confused by this question so perhaps my interpretation is not accurate but given we are talking about data loss, I felt compelled to respond.
Yeah, the problem does seem under-specified. Knowing the form of these bulk operations helps greatly when trying to tune based upon their presence (or, in some cases, rewrite them to a better form).
Turning off "synchronous commit" means that the application session will get a success back from the API call before the physical i/o is acknowledged. This does risk data loss and should only be done in application use cases where it really makes sense.
For some definitions of data loss. Specifically, a server that crashes before the data is full written to disk would end up in the exact same state (relative to the transaction) whether synchronous commit is on or off - the difference being whether the session is still waiting when the crash happens, or has moved on. When the server comes back up it, and possibly other components, may now think differently about the present state than what the database thinks. Dealing with the potential discrepancy is the the trade-off for not having to wait.
If you want to improve performance of write throughput in wal operations but not risk potential data loss, you should probably increase "commit_delay" to encourage more concurrent write sessions to group their commits into fewer writes. The writer sessions will still wait for the synchronous write operation so latency will increase slightly but you may see decreased writes.
Agreed, this is probably a better first avenue to approach.
David J.
