MIME-Version: 1.0
References: 
 <CA+xwDujmUnm_vbojvvRz87tuwTA90CZrC8HGor99Nv9RtJERRg@mail.gmail.com>
 <CABPTF7WOQfPk-eCDAZXfqTPSRTg6AbqNYbRjtauYDYbhrAbSWQ@mail.gmail.com>
 <CA+xwDuiMi1zDQOhHjfKT1d9nUwkX2g=5Fyizc2GaVvA2BYw1Mg@mail.gmail.com>
In-Reply-To: 
 <CA+xwDuiMi1zDQOhHjfKT1d9nUwkX2g=5Fyizc2GaVvA2BYw1Mg@mail.gmail.com>
From: Xuneng Zhou <xunengzhou@gmail.com>
Date: Sat, 1 Nov 2025 08:41:33 +0800
Message-ID: 
 <CABPTF7WGK=sSFnJxCQGE6JFR9ceKrtHVsrxcb=BUoLUBZv8KEw@mail.gmail.com>
Subject: Re: Question on pg_stat_io showing zero reads/writes for I/O workers
To: Shardul Borhade <shardul@dbtune.com>
Cc: pgsql-admin@lists.postgresql.org,
	pgsql-hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CABPTF7WGK%3DsSFnJxCQGE6JFR9ceKrtHVsrxcb%3DBUoLUBZv8KEw%40mail.gmail.com>
Precedence: bulk

Hi,

On Fri, Oct 31, 2025 at 5:50=E2=80=AFPM Shardul Borhade <shardul@dbtune.com=
> wrote:
>
> Hi Xuneng,
>
> Thank you so much for the incredibly clear and detailed explanation. Your=
 analysis of how I/O stats are attributed at submission time was exactly th=
e information I was missing, and it completely solved the mystery.
>
> You were absolutely correct. I followed your suggested test procedure, an=
d it confirmed that AIO is indeed working as intended.
>
> The key confirmation for me was checking pg_stat_activity while running a=
 heavy, cache-cleared sequential scan. Just as you predicted, the wait_even=
t for my client backend process was AioIoCompletion
>
>  pid | backend_type | wait_event_type | wait_event
>
> ---------+----------------+-----------------+-----------------
> 3052099 | client backend | IO | AioIoCompletion.
>
> This was the "smoking gun" that proved the main process was delegating th=
e I/O and waiting for the workers, rather than doing the reads itself.
>
> Thanks again for your help and for the excellent work on PostgreSQL.

Thanks for your question and kind words =E2=80=94 it means a lot to me. I a=
lso
think this is worth investigating further.

Best,
Xuneng

>
> On Fri, Oct 31, 2025 at 10:30=E2=80=AFAM Xuneng Zhou <xunengzhou@gmail.co=
m> wrote:
>>
>> Hi,
>>
>> On Fri, Oct 31, 2025 at 4:17=E2=80=AFPM Shardul Borhade <shardul@dbtune.=
com> wrote:
>> >
>> > Hi team,
>> >
>> > I=E2=80=99m running into an issue with pg_stat_io. When I run the foll=
owing query:
>> >
>> > SELECT backend_type, reads, writes, read_time
>> > FROM pg_stat_io
>> > WHERE backend_type LIKE '%io%';
>> >
>> > I consistently get:
>> >
>> >  backend_type | reads | writes | read_time
>> > --------------+-------+--------+-----------
>> >  io worker    |     0 |      0 |         0
>> >  io worker    |     0 |      0 |         0
>> >  io worker    |     0 |      0 |         0
>> >  io worker    |     0 |      0 |         0
>> >  io worker    |     0 |      0 |         0
>> >  io worker    |     0 |      0 |         0
>> >  io worker    |       |      0 |
>> >  io worker    |     0 |      0 |         0
>> > (8 rows)
>> >
>> > I tried running a heavy sequential scan on a 55 GB table as well as a =
bitmap heap scan, but the reads and writes columns still show zero.
>> >
>> > However, I can clearly observe performance differences when I tune the=
 io_workers configuration, so I believe they are active.
>> >
>> > Am I missing something here? Could someone please help me understand w=
hy the stats aren=E2=80=99t being reflected in pg_stat_io? Do I need to ena=
ble any other server parameter to log this information?
>>
>> When your client backend issues a query:
>>
>> 1. Your backend calls `AsyncReadBuffers()` and counts the IO stats
>> 2. The IO worker performs the actual I/O but doesn't increment its own c=
ounters
>> 3. Stats accumulate under "client backend", not "io worker"
>>
>> Check your *client backend* stats instead:
>>
>> ```sql
>> You should see reads/writes here:
>>
>> SELECT backend_type, context, object, reads, writes
>> FROM pg_stat_io
>> WHERE backend_type =3D 'client backend'
>>   AND reads > 0
>> ORDER BY reads DESC;
>>
>> -- Verify IO workers exist:
>>
>> SELECT pid, backend_type, wait_event
>> FROM pg_stat_activity
>> WHERE backend_type =3D 'io worker';
>>
>>
>> Quick Test
>>
>> -- Reset stats
>>
>> SELECT pg_stat_reset_shared('io');
>>
>> -- Run your heavy scan
>>
>> SELECT COUNT(*) FROM your_large_table;
>>
>> -- Check client backend stats (should increase)
>>
>> SELECT SUM(reads) as total_reads
>> FROM pg_stat_io
>> WHERE backend_type =3D 'client backend';
>>
>> -- Check IO worker stats (will remain zero)
>>
>> SELECT SUM(reads) as total_reads
>> FROM pg_stat_io
>> WHERE backend_type =3D 'io worker';
>>
>> See source code:
>>
>> - `src/backend/storage/buffer/bufmgr.c` (AsyncReadBuffers, line 1938)
>> - stats counted at submission
>> - `src/backend/utils/activity/pgstat_io.c` (pgstat_count_io_op, line
>> 74) - uses submitter's MyBackendType
>> - `src/backend/storage/aio/aio_io.c` (pgaio_io_perform_synchronously)
>> - IO worker doesn't call stat functions
>>
>> However, this behavior seems not great here. If the above analysis is
>> sound, should we add something like this to not track i/o worker
>> // In pgstat_tracks_io_bktype()
>> case B_IO_WORKER:
>>     return false;  // Don't show zero-value rows
>> or track actual IO worker stats?
>>
>> Best,
>> Xuneng