public inbox for [email protected]  

help / color / mirror / Atom feed

Postgre and AIO
2+ messages / 2 participants
[nested] [flat]

* Postgre and AIO
@ 2025-09-29 12:07 Weck, Luis <[email protected]>
  2025-09-29 12:26 ` Re: Postgre and AIO Dominique Devienne <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Weck, Luis @ 2025-09-29 12:07 UTC (permalink / raw)
  To: pgsql-general

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?


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Postgre and AIO
  2025-09-29 12:07 Postgre and AIO Weck, Luis <[email protected]>
@ 2025-09-29 12:26 ` Dominique Devienne <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Dominique Devienne @ 2025-09-29 12:26 UTC (permalink / raw)
  To: Weck, Luis <[email protected]>; +Cc: pgsql-general

On Mon, Sep 29, 2025 at 2:07 PM Weck, Luis <[email protected]> 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






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2025-09-29 12:26 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-29 12:07 Postgre and AIO Weck, Luis <[email protected]>
2025-09-29 12:26 ` Dominique Devienne <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox