Subject: Postgre and AIO

From: "Weck, Luis" <[email-masked]>
Date: Mon, 29 Sep 2025 12:07:09 +0000

This is more of a question of capability and to make me understand how exactly AIO work in Postgres.

Now that AIO landed in v18, I was thinking of a use case which has annoyed me sometimes, which is inserting  lots of data into a table with many indices. What I am specifically “complaining” is that index updating happens one at a time. Would it be possible/make sense to use AIO to do this?

Another thing that happens often is that an index lookup for something like SELECT y FROM tbl WHERE x IN (1,2,…N)  where N is a big number such as 1000 or 2000, takes a while, because (at least for versions < 18) it took a long time sequentially reading the index  for each value. I ended up having to split the values into smaller chunks and ran multiple queries in parallel to maintain a lower latency overall.

Anyway, does any of this make sense? Could Postgres extend the use of AIO to such cases?

---

From: Dominique Devienne <[email-masked]>
Date: Mon, 29 Sep 2025 14:26:27 +0200

On Mon, Sep 29, 2025 at 2:07 PM Weck, Luis <[email-masked]> wrote:
> Now that AIO landed in v18,
> [...] index updating happens one at a time.
> [...] it took a long time sequentially reading the index for each value

These have nothing to do with Async-IO IMHO.

For your first case, each index is its own file, so could already be
processed in parallel, in terms of IO.

For your second case, and in general, this is about the SQL processing
engine doing more in parallel. AFAIK, because of the current (mostly?)
single-threaded-process-based architecture, parallel processing means
forking worker processes, and that's not done too often.

AIO is not a magic bullet. It just means NOT blocking waiting for IO,
so you have the opportunity to do something else during that time. But
your code must be structured to be able to do something else already.
Which is not free, and tends to make the code more complex. In
single-threaded apps, like PostgreSQL backends, that means an event
loop, and while I'm sure there's one, for the client-backend socket
IO, I'm not sure the rest of the code is loop-based.

But these are comments from someone who doesn't know the code, so feel
free to ignore :). --DD