MIME-Version: 1.0
References: <CAB+=1TWZNvMhVthJ2iKs_Q4qBzMw-v_oaSz7HbFE_P_qC5jMFA@mail.gmail.com>
 <3dddea5e-52ab-4075-970d-a87b0c921ae7@aklaver.com> <CAB+=1TWdRd2sBw7-vXCovH_VHLANh+aSaU-WyJ2m8tL4TkF=8g@mail.gmail.com>
In-Reply-To: <CAB+=1TWdRd2sBw7-vXCovH_VHLANh+aSaU-WyJ2m8tL4TkF=8g@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Tue, 17 Sep 2024 09:13:12 -0400
Message-ID: <CAKAnmm+FFS1fALtWcviwvm6=9h1NwqH20gZPSGrdXey0tproJA@mail.gmail.com>
Subject: Re: IO related waits
To: veem v <veema0000@gmail.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, Christophe Pettus <xof@thebuild.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c4642706225075da"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmm%2BFFS1fALtWcviwvm6%3D9h1NwqH20gZPSGrdXey0tproJA%40mail.gmail.com>
Precedence: bulk

--000000000000c4642706225075da
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Sep 16, 2024 at 11:56=E2=80=AFPM veem v <veema0000@gmail.com> wrote=
:

> So what can be the caveats in this approach, considering transactions
> meant to be ACID compliant as financial transactions.
>

Financial transactions need to be handled with care. Only you know your
business requirements, but as Christophe pointed out, disabling
synchronous commit means your application may think a particular
transaction has completed when it has not. Usually that's a big red flag
for financial applications.

we are using row by row transaction processing for inserting data into the
> postgres database and commit is performed for each row.


This is a better place to optimize. Batch many rows per transaction. Remove
unused indexes.

flushing of the WAL to the disk has to happen anyway(just that it will be
> delayed now), so can this method cause contention in the database storage
> side if the speed in which the data gets ingested from the client is not
> getting written to the disk , and if it can someway impact the data
> consistency for the read queries?
>

Not quite clear what you are asking here re data consistency. The data will
always be consistent, even if synchronous_commit is disabled. The only
danger window is on a server crash.

(Keep in mind that RDS is not Postgres, so take tuning recommendations and
advice with a grain of salt.)

Cheers,
Greg

--000000000000c4642706225075da
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Sep 16, 2024 at 11:56=E2=80=AFPM =
veem v &lt;<a href=3D"mailto:veema0000@gmail.com">veema0000@gmail.com</a>&g=
t; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,20=
4);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">So what can be the c=
aveats in this approach, considering transactions meant to be ACID complian=
t as financial transactions.</div></div></blockquote><div><br></div><div>Fi=
nancial transactions need to be handled with care. Only you know your busin=
ess requirements, but as Christophe pointed out, disabling synchronous=C2=
=A0commit means your application may think a particular transaction has com=
pleted when it has not. Usually that&#39;s a big red flag for financial app=
lications.</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"m=
argin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left=
:1ex">we are using row by row transaction processing for inserting data int=
o the postgres database and commit is performed for each row.</blockquote><=
div><br></div><div>This is a better place to optimize. Batch many rows per =
transaction. Remove unused indexes.</div><div><br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">flushing=
 of the WAL to the disk has to happen anyway(just that it will be delayed n=
ow), so can this method cause contention in the database=C2=A0storage side =
if the speed in which=C2=A0the data gets ingested from the client is not ge=
tting written to the=C2=A0disk , and if it can someway impact the data cons=
istency for the read queries?<br></div></div></blockquote><div><br></div><d=
iv>Not quite clear what you are asking here re data consistency. The data w=
ill always be consistent, even if synchronous_commit is disabled. The only =
danger window is on a server crash.</div><div><br></div><div>(Keep in mind =
that RDS is not Postgres, so take tuning recommendations and advice with a =
grain of salt.)</div><div><br></div><div>Cheers,</div><div>Greg</div><div>=
=C2=A0<br></div></div></div>

--000000000000c4642706225075da--