IronPort-SDR: 
 Mqixm7fp02wZHOD+r/BM87ek4rzs2FSzGK3ylxeHxBhjgCD6thMiA4iYiTY/AJpy0yoPTIxo39
 JrUXQFePsZWg==
Subject: RE: Commit Delay
Thread-Topic: Commit Delay
From: "Thawley, Peter" <pthawley@amazon.com>
To: Bruce Momjian <bruce@momjian.us>, Naveen Sankineni <nsankineni@gmail.com>
CC: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Thread-Index: AQHWH0eNrryXe5c1u06E4idjqg7n2qiSURAAgAEXPBA=
Date: Fri, 1 May 2020 16:26:59 +0000
Message-ID: <7cee0732a2ac4a479b5da94a6cb2693f@EX13D14UWC004.ant.amazon.com>
References: 
 <CAOOQyJKp-zPNCPLvYoL+GES22iRv8uXLK3Jh7FsvoH2MDK9rBQ@mail.gmail.com>
 <20200430233354.GB31272@momjian.us>
In-Reply-To: <20200430233354.GB31272@momjian.us>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Precedence: bulk

Hi Naveen

I was a little confused by this question so perhaps my interpretation is no=
t accurate but given we are talking about data loss, I felt compelled to re=
spond.  Turning off "synchronous commit" means that the application session=
 will get a success back from the API call before the physical i/o is ackno=
wledged.  This does risk data loss and should only be done in application u=
se cases where it really makes sense. =20

If you want to improve performance of write throughput in wal operations bu=
t not risk potential data loss, you should probably increase "commit_delay"=
 to encourage more concurrent write sessions to group their commits into fe=
wer writes.  The writer sessions will still wait for the synchronous write =
operation so latency will increase slightly but you may see decreased write=
s.

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

CAUTION: This email originated from outside of the organization. Do not cli=
ck 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=20
> of Data Loss if we wanted to set .
> One of our Database is waiting on I/O when there is a batch operation=20
> that runs to Insert/Delete table data is impacting the small updates=20
> that runs  on a continuous basis where it shows that wait is on walwritel=
ock.
>
> 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@momjian.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 +