Add per-backend AIO statistics

Hi,

On Tue, Jul 07, 2026 at 11:02:03AM +0000, Bertrand Drouvot wrote:

postgres=# select count(*),avg(length(data)) from smalldocs;
count | avg
---------+-----------------------
1024000 | 1024.0000000000000000
(1 row)

postgres=# SELECT * FROM pg_stat_get_backend_aio(pg_backend_pid());
started | executed_sync | executed_async | completed_self | completed_other | handle_waits | submitted | stats_reset
---------+---------------+----------------+----------------+-----------------+--------------+-----------+-------------------------------
3125 | 46 | 3079 | 46 | 0 | 0 | 3078 | 2026-07-07 09:28:27.412136+00

We can see that the sequential scan fully benefits from AIO.

2/ query the largedocs table:

postgres=# select count(*),avg(length(data)) from largedocs;
count | avg
-------+----------------------
1000 | 1048576.000000000000
(1 row)

postgres=# SELECT * FROM pg_stat_get_backend_aio(pg_backend_pid());
started | executed_sync | executed_async | completed_self | completed_other | handle_waits | submitted | stats_reset
---------+---------------+----------------+----------------+-----------------+--------------+-----------+-------------------------------
121154 | 121150 | 4 | 121150 | 0 | 0 | 4 | 2026-07-07 09:35:00.504872+00

We can see that the sequential scan bypasses AIO.

I was just doing some AIO experiments and was using the new pg_stat_get_backend_aio()
function.

So, while at it, sharing more examples here:

3/ pg_stat_get_backend_aio() and pg_stat_get_backend_io() correlation

postgres=# SELECT executed_sync, executed_async FROM pg_stat_get_backend_aio(pg_backend_pid());
executed_sync | executed_async
---------------+----------------
46 | 3088
(1 row)

postgres=# SELECT object, context, reads, read_bytes FROM pg_stat_get_backend_io(pg_backend_pid());
object | context | reads | read_bytes
---------------+-----------+-------+------------
relation | bulkread | 3088 | 401580032
relation | bulkwrite | 0 | 0
relation | init | 0 | 0
relation | normal | 46 | 376832
relation | vacuum | 0 | 0
temp relation | normal | 0 | 0
wal | init | |
wal | normal | 0 | 0
(8 rows)

We can see that the "executed_sync" matches the reads "normal" context and that
the "executed_async" matches the reads "bulkread" context.

4/ io_uring and multiple backends

postgres=# SELECT a.pid,
(pg_stat_get_backend_aio(a.pid)).completed_other
FROM pg_stat_activity a
WHERE a.backend_type = 'client backend';
pid | completed_other
---------+-----------------
1911889 | 245
1911892 | 511
1911912 | 147
1911933 | 161
(4 rows)

We can see that the backends completed AIO on behalf of other backends, which
makes fully sense in io_uring mode.

5/ io_max_concurrency = 4

postgres=# SELECT started, handle_waits FROM pg_stat_get_backend_aio(pg_backend_pid());
started | handle_waits
---------+--------------
3139 | 3026
(1 row)

We can see that the backend had to wait for free AIO handles on 96% of its IOs.

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

On Tue, Jul 07, 2026 at 11:02:03AM +0000, Bertrand Drouvot wrote:

1/ pg_aios that lists all AIO handles that are currently in use. That shows
what's happening right now, but not what has happened.

2/ pg_stat_get_backend_io() that shows how much IO was done, but not how it
was done. There's no way to see whether IOs ran synchronously or
asynchronously, whether a backend was stalling on handle exhaustion, or how
completions are distributed across backends.

While the information may be useful, one thing that sounds very
important to me is how this impacts workloads by default.

Andres is usually able to catch bottlenecks that everybody else is
unable to see, so perhaps checking with him the location of these
extra function calls would be a good first step. Your proposal goes
down to pgaio_io_stage(), pgaio_io_process_completion() and
pgaio_submit_staged() to track these counter increments.
--
Michael

Hi,

On Wed, Jul 08, 2026 at 03:52:20PM +0900, Michael Paquier wrote:

On Tue, Jul 07, 2026 at 11:02:03AM +0000, Bertrand Drouvot wrote:

1/ pg_aios that lists all AIO handles that are currently in use. That shows
what's happening right now, but not what has happened.

2/ pg_stat_get_backend_io() that shows how much IO was done, but not how it
was done. There's no way to see whether IOs ran synchronously or
asynchronously, whether a backend was stalling on handle exhaustion, or how
completions are distributed across backends.

While the information may be useful,

Thanks for looking at it!

Andres is usually able to catch bottlenecks that everybody else is
unable to see, so perhaps checking with him the location of these
extra function calls would be a good first step. Your proposal goes
down to pgaio_io_stage(), pgaio_io_process_completion() and
pgaio_submit_staged() to track these counter increments.

yeah, and also to 1/ confirm that I did understand this area of the AIO code
correctly and 2/ see if other counters could make sense.

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Hi,

On 2026-07-08 15:52:20 +0900, Michael Paquier wrote:

On Tue, Jul 07, 2026 at 11:02:03AM +0000, Bertrand Drouvot wrote:

1/ pg_aios that lists all AIO handles that are currently in use. That shows
what's happening right now, but not what has happened.

2/ pg_stat_get_backend_io() that shows how much IO was done, but not how it
was done. There's no way to see whether IOs ran synchronously or
asynchronously, whether a backend was stalling on handle exhaustion, or how
completions are distributed across backends.

While the information may be useful, one thing that sounds very
important to me is how this impacts workloads by default.

Andres is usually able to catch bottlenecks that everybody else is
unable to see, so perhaps checking with him the location of these
extra function calls would be a good first step. Your proposal goes
down to pgaio_io_stage(), pgaio_io_process_completion() and
pgaio_submit_staged() to track these counter increments.

I think the overhead might be ok, but I am rather doubtful that all of this
information is actually useful. You're adding quite a few counters for each
IO, do we actually need that?

E.g. what do we gain from counting:
- started (if you want to see the number of IOs that are in progress,
cumulative stats are the wrong tool)
- executed_async (that's just the number of IOs minus executed_sync)
- completed_self (that's just the number of IOs minus executed_other)

Separately, I'm doubtful it makes sense to have only per-backend stats for
this. I think you'd almost always want the stats for exited backend
(e.g. parallel workers) too.

Unfortunately I'm pretty doubtful that pgstat_backend.c is the right
architectural direction. It'll just end up implementing all kinds of stats,
since we'll incrementally want more and more per-backend stats. I think what
we'd want is rather something where for each applicable stats kind we have a
shared counter for all exited backends and then per-backend counters for live
backends, with helpers to aggregate the exited + live stats to a total.

Greetings,

Andres Freund

Hi,

On Wed, Jul 08, 2026 at 02:08:00PM -0400, Andres Freund wrote:

Hi,

On 2026-07-08 15:52:20 +0900, Michael Paquier wrote:

On Tue, Jul 07, 2026 at 11:02:03AM +0000, Bertrand Drouvot wrote:

1/ pg_aios that lists all AIO handles that are currently in use. That shows
what's happening right now, but not what has happened.

2/ pg_stat_get_backend_io() that shows how much IO was done, but not how it
was done. There's no way to see whether IOs ran synchronously or
asynchronously, whether a backend was stalling on handle exhaustion, or how
completions are distributed across backends.

While the information may be useful, one thing that sounds very
important to me is how this impacts workloads by default.

Andres is usually able to catch bottlenecks that everybody else is
unable to see, so perhaps checking with him the location of these
extra function calls would be a good first step. Your proposal goes
down to pgaio_io_stage(), pgaio_io_process_completion() and
pgaio_submit_staged() to track these counter increments.

I think the overhead might be ok,

Thanks for the feedback.

but I am rather doubtful that all of this
information is actually useful. You're adding quite a few counters for each
IO, do we actually need that?

E.g. what do we gain from counting:
- started (if you want to see the number of IOs that are in progress,
cumulative stats are the wrong tool)
- executed_async (that's just the number of IOs minus executed_sync)
- completed_self (that's just the number of IOs minus executed_other)

Yeah, we can remove some fields (as they're derivable).

Separately, I'm doubtful it makes sense to have only per-backend stats for
this. I think you'd almost always want the stats for exited backend
(e.g. parallel workers) too.

Indeed, adding a global view would capture their activity.

Unfortunately I'm pretty doubtful that pgstat_backend.c is the right
architectural direction. It'll just end up implementing all kinds of stats,
since we'll incrementally want more and more per-backend stats. I think what
we'd want is rather something where for each applicable stats kind we have a
shared counter for all exited backends and then per-backend counters for live
backends, with helpers to aggregate the exited + live stats to a total.

That's a very nice proposal that would avoid the double counting. OTOH, that's
also a major re-design that would benefit all existing per-backend stats kinds.

I can see 2 options:

1/

step 1: Implement per-backend AIO stats (like proposed taking into account your
remark about useless, derivable fields) + a global view.
step 2: work on the re-design

2/

step 1: work on the redesign
step 2: Add AIO stats based on the re-design

The pros of 1/ is that step 1 would most probably land in 20, providing more user
visibility (+ it could be used or improved during the AIO write project). Step 2
is a much larger project that might not land in 20.

The cons, would be double counting (as there is no need to try to implement
something like [1]/messages/by-id/aNVWe2tR1jj5Tsct@ip-10-97-1-34.eu-west-3.compute.internal as we are going to re-design anyway).

I'll be tempted to vote for 1/ to provide faster added value. What do you (Andres,
Michael) think?

[1]: /messages/by-id/aNVWe2tR1jj5Tsct@ip-10-97-1-34.eu-west-3.compute.internal

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Hi,

On Thu, Jul 09, 2026 at 04:19:26AM +0000, Bertrand Drouvot wrote:

Hi,

On Wed, Jul 08, 2026 at 02:08:00PM -0400, Andres Freund wrote:

Unfortunately I'm pretty doubtful that pgstat_backend.c is the right
architectural direction. It'll just end up implementing all kinds of stats,
since we'll incrementally want more and more per-backend stats. I think what
we'd want is rather something where for each applicable stats kind we have a
shared counter for all exited backends and then per-backend counters for live
backends, with helpers to aggregate the exited + live stats to a total.

That's a very nice proposal that would avoid the double counting. OTOH, that's
also a major re-design that would benefit all existing per-backend stats kinds.

I can see 2 options:

1/

step 1: Implement per-backend AIO stats (like proposed taking into account your
remark about useless, derivable fields) + a global view.
step 2: work on the re-design

2/

step 1: work on the redesign
step 2: Add AIO stats based on the re-design

The pros of 1/ is that step 1 would most probably land in 20, providing more user
visibility (+ it could be used or improved during the AIO write project). Step 2
is a much larger project that might not land in 20.

The cons, would be double counting (as there is no need to try to implement
something like [1] as we are going to re-design anyway).

I'll be tempted to vote for 1/ to provide faster added value. What do you (Andres,
Michael) think?

Actually, there is no rush to merge the per-backend AIO stats (we still have
plenty of time for 20). So let's try option 2 and implement the new design first
and see where it goes. I'll create a dedicated thread once ready.

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com