Re: IO related waits

> On Sep 16, 2024, at 13:24, veem v <veema0000(at)gmail(dot)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?

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