Thread: pg_stat_io clarifications: background worker, writes and reads

Hello list,

what is the "background worker" in the pg_stat_io statistics view? I'm
reading the documentation but can't figure this one out knowing that it is
not autovacuum or bgwriter. And I'm not aware of any extension I might
have with registered background worker.

Additionally, how can it be evictions > writes? I would expect every
eviction to cause a write.

Finally about "hits", I understand they are reads found in shared_buffers,
so they never registered into the "reads" counter. So is "reads" in
pg_stat_io the equivalent to misses, i.e. the opposite of "hits", the read
attempts not found in the shared_buffers, that needed to be fetched from
the disk (or OS buffercache)?

    backend_type    |    object     | context |  reads  |  read_time  | writes | write_time | writebacks | writeback_time | extends | extend_time | op_bytes |   hits    | evictions | reuses | fsyncs | fsync_time |          stats_reset
-------------------+---------------+---------+---------+-------------+--------+------------+------------+----------------+---------+-------------+----------+-----------+-----------+--------+--------+------------+-------------------------------
  background worker | relation      | normal  | 5139575 | 2196288.011 |  63277 |    1766.94 |          0 |              0 |       0 |           0 |     8192 | 876913705 |   5139653 |        |      0 |          0 | 2024-04-08 08:50:02.971192+00


Thank you in advance,
Dimitris

Hello list,

what is the "background worker" in the pg_stat_io statistics view? I'm
reading the documentation but can't figure this one out knowing that it is
not autovacuum or bgwriter. And I'm not aware of any extension I might
have with registered background worker.

Additionally, how can it be evictions > writes? I would expect every
eviction to cause a write.

Finally about "hits", I understand they are reads found in shared_buffers,
so they never registered into the "reads" counter. So is "reads" in
pg_stat_io the equivalent to misses, i.e. the opposite of "hits", the read
attempts not found in the shared_buffers, that needed to be fetched from
the disk (or OS buffercache)?

    backend_type    |    object     | context |  reads  |  read_time  | writes | write_time | writebacks | writeback_time | extends | extend_time | op_bytes |   hits    | evictions | reuses | fsyncs | fsync_time |          stats_reset
-------------------+---------------+---------+---------+-------------+--------+------------+------------+----------------+---------+-------------+----------+-----------+-----------+--------+--------+------------+-------------------------------
  background worker | relation      | normal  | 5139575 | 2196288.011 |  63277 |    1766.94 |          0 |              0 |       0 |           0 |     8192 | 876913705 |   5139653 |        |      0 |          0 | 2024-04-08 08:50:02.971192+00


Thank you in advance,
Dimitris

So what is this particular "background worker" I'm seeing, given that I
have no replication or extensions?

Searching the logs I found entries like the following:

LOG:  background worker "parallel worker" (PID 93384) exited with exit code 1

This got logged when I killed a simple SELECT query that took too long
doing parallel seqscans. Could it be that the entry in pg_stat_io named
"background worker" also includes the parallel workers from a SELECT
query?

Thanks,
Dimitris

On Wed, 15 May 2024, Muhammad Imtiaz wrote:

> Hi,
>
> In PostgreSQL, the pg_stat_io view provides detailed statistics on I/O operations. Background process perform maintenance tasks and other background operations essential to the functioning of the PostgreSQL database.
> They include processes such as:
>
> 1. Autovacuum Workers
> 2. WAL Writer
> 3. Background Writer
> 4. Logical Replication Workers
> 5. Custom Background Workers
>
> In the pg_stat_io view, statistics related to I/O operations performed by these background workers are recorded. 
>
> Regards,
> Imtiaz
>
>
> On Wed, 15 May 2024, 01:26 Dimitrios Apostolou, <jimis@gmx.net> wrote:
>       Hello list,
>
>       what is the "background worker" in the pg_stat_io statistics view? I'm
>       reading the documentation but can't figure this one out knowing that it is
>       not autovacuum or bgwriter. And I'm not aware of any extension I might
>       have with registered background worker.
>
>       Additionally, how can it be evictions > writes? I would expect every
>       eviction to cause a write.
>
>       Finally about "hits", I understand they are reads found in shared_buffers,
>       so they never registered into the "reads" counter. So is "reads" in
>       pg_stat_io the equivalent to misses, i.e. the opposite of "hits", the read
>       attempts not found in the shared_buffers, that needed to be fetched from
>       the disk (or OS buffercache)?
>
>           backend_type    |    object     | context |  reads  |  read_time  | writes | write_time | writebacks | writeback_time | extends | extend_time | op_bytes |   hits    | evictions | reuses | fsyncs | fsync_time |          stats_reset
>       -------------------+---------------+---------+---------+-------------+--------+------------+------------+----------------+---------+-------------+----------+-----------+-----------+--------+--------+------------+-------------------------------
>         background worker | relation      | normal  | 5139575 | 2196288.011 |  63277 |    1766.94 |          0 |              0 |       0 |           0 |     8192 | 876913705 |   5139653 |        |      0 |          0 | 2024-04-08 08:50:02.971192+00
>
>
>       Thank you in advance,
>       Dimitris
>
>
>
>
>