RE: Commit Delay

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

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.

Peter Thawley
Amazon Aurora & RDS Database Service team, AWS
      
-----Original Message-----
From: Bruce Momjian <bruce(at)momjian(dot)us>
Sent: Thursday, April 30, 2020 4:34 PM
To: Naveen Sankineni <nsankineni(at)gmail(dot)com>
Cc: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: RE: [EXTERNAL] Commit Delay

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you can confirm the sender and know the content is safe.

On Thu, Apr 30, 2020 at 07:31:01PM -0400, Naveen Sankineni wrote:
> Hello ,
>
> I would need your advice on which parameter is least impacted in terms
> of Data Loss if we wanted to set .
> One of our Database is waiting on I/O when there is a batch operation
> that runs to Insert/Delete table data is impacting the small updates
> that runs on a continuous basis where it shows that wait is on walwritelock.
>
> commit_delay
> synchronous_commit

The full list is here:

https://www.postgresql.org/docs/12/non-durability.html

I would say synchronous_commit.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EnterpriseDB https://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +