Date: Wed, 8 Jul 2026 06:00:22 +0000
From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
To: pgsql-hackers@lists.postgresql.org
Subject: Re: Add per-backend AIO statistics
Message-ID: <ak3ndoKYC2gCDoqY@bdtpg>
References: <akzcq9jtWK5C92g1@bdtpg>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <akzcq9jtWK5C92g1@bdtpg>
Archived-At: <https://www.postgresql.org/message-id/ak3ndoKYC2gCDoqY%40bdtpg>
Precedence: bulk

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