Обсуждение: Postgre and AIO
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?
Hi, On Mon, Sep 29, 2025 at 8:07 PM Weck, Luis <luis.weck@pismo.io> wrote: > > 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 datainto a table with many indices. What I am specifically “complaining” is that index updating happens one at a time. Wouldit 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) whereN is a big number such as 1000 or 2000, takes a while, because (at least for versions < 18) it took a long time sequentiallyreading the index for each value. I ended up having to split the values into smaller chunks and ran multiplequeries 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? To my knowledge, AIO for index scan is still in-progress and expected to land in v19/20 or later? [1] https://wiki.postgresql.org/wiki/AIO (not stay-up-to date) [2] https://talkingpostgres.com/episodes/what-went-wrong-what-went-right-with-aio-with-andres-freund Best, Xuneng
On Mon, Sep 29, 2025 at 2:07 PM Weck, Luis <luis.weck@pismo.io> 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