Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: IO related waits
From: Christophe Pettus <xof@thebuild.com>
In-Reply-To: <CAB+=1TWZNvMhVthJ2iKs_Q4qBzMw-v_oaSz7HbFE_P_qC5jMFA@mail.gmail.com>
Date: Mon, 16 Sep 2024 13:28:55 -0700
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <FE363774-703A-4B61-8DA6-B0A917B97A0F@thebuild.com>
References: <CAB+=1TWZNvMhVthJ2iKs_Q4qBzMw-v_oaSz7HbFE_P_qC5jMFA@mail.gmail.com>
To: veem v <veema0000@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/FE363774-703A-4B61-8DA6-B0A917B97A0F%40thebuild.com>
Precedence: bulk


> On Sep 16, 2024, at 13:24, veem v <veema0000@gmail.com> wrote:
> Architecture team is suggesting to enable asynch io if possible, so =
that the streaming client will not wait for the commit confirmation from =
the database. So I want to understand , how asynch io can be enabled and =
if any downsides of doing this?=20

"Async I/O" has a specific meaning that's not currently applicable to =
PostgreSQL.  What is available is "synchronous_commit".  This setting is =
by default on.  When it's on, each commit waits until the associated WAL =
information has been flushed to disk, and then returns.  If it is turned =
off, the commit returns more or less immediately, and the WAL flush =
happens asynchronously from the commit.

The upside is that the session can proceed without waiting for the WAL =
flush.  The downside is that on a server crash, some transactions may =
not have been fully committed to the database, and will be missing when =
the database restarts.  The database won't be corrupted (as in, you try =
to use it and get errors), but it will be "earlier in time" than the =
application might expect.  It's pretty common to turn it off for =
high-ingestion-rate situations, especially where the application can =
detect and replay missing transactions on a crash.=