Hi, On Fri, Oct 31, 2025 at 5:50 PM Shardul Borhade <[email protected]> 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 the > information I was missing, and it completely solved the mystery. > > You were absolutely correct. I followed your suggested test procedure, and 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_event > 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 the > 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 — it means a lot to me. I also think this is worth investigating further. Best, Xuneng > > On Fri, Oct 31, 2025 at 10:30 AM Xuneng Zhou <[email protected]> wrote: >> >> Hi, >> >> On Fri, Oct 31, 2025 at 4:17 PM Shardul Borhade <[email protected]> wrote: >> > >> > Hi team, >> > >> > I’m running into an issue with pg_stat_io. When I run the following 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 why >> > the stats aren’t being reflected in pg_stat_io? Do I need to enable 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 >> counters >> 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 = '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 = '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 = 'client backend'; >> >> -- Check IO worker stats (will remain zero) >> >> SELECT SUM(reads) as total_reads >> FROM pg_stat_io >> WHERE backend_type = '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
