MIME-Version: 1.0
References: <IA3PR10MB8113DB0A2CA1B6B0DE3831078D1BA@IA3PR10MB8113.namprd10.prod.outlook.com>
In-Reply-To: <IA3PR10MB8113DB0A2CA1B6B0DE3831078D1BA@IA3PR10MB8113.namprd10.prod.outlook.com>
From: Xuneng Zhou <xunengzhou@gmail.com>
Date: Mon, 29 Sep 2025 20:25:46 +0800
Message-ID: <CABPTF7U2XgofHH1sPBw46tfnWWGua22NOZA98o2o2PaYEXfStw@mail.gmail.com>
Subject: Re: Postgre and AIO
To: "Weck, Luis" <luis.weck@pismo.io>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CABPTF7U2XgofHH1sPBw46tfnWWGua22NOZA98o2o2PaYEXfStw%40mail.gmail.com>
Precedence: bulk

Hi,

On Mon, Sep 29, 2025 at 8:07=E2=80=AFPM Weck, Luis <luis.weck@pismo.io> wro=
te:
>
> This is more of a question of capability and to make me understand how ex=
actly AIO work in Postgres.
>
> Now that AIO landed in v18, I was thinking of a use case which has annoye=
d me sometimes, which is inserting  lots of data into a table with many ind=
ices. What I am specifically =E2=80=9Ccomplaining=E2=80=9D is that index up=
dating 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 li=
ke SELECT y FROM tbl WHERE x IN (1,2,=E2=80=A6N)  where N is a big number s=
uch 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 u=
p having to split the values into smaller chunks and ran multiple queries i=
n 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-wi=
th-aio-with-andres-freund

Best,
Xuneng