Subject: pg_stat_io clarifications: background worker, writes and reads

From: Dimitrios Apostolou <[email-masked]>
Date: Tue, 14 May 2024 22:26:22 +0200 (CEST)

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

---

From: Kashif Zeeshan <[email-masked]>
Date: Wed, 15 May 2024 09:03:55 +0500

Hi

background workers are used to perform tasks on db e.g. I/O, replication,
clone schema, vacuum etc, you can find more details on following links.

https://www.postgresql.org/docs/current/bgworker.html#:~:text=PostgreSQL%20can%20be%20extended%20to,linked%20to%20the%20server's%20status
.

Regards
Kashif Zeeshan
Bitnine

On Wed, May 15, 2024 at 1:26 AM Dimitrios Apostolou <[email-masked]> wrote:

>     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
>
>
>
>

---

From: Muhammad Imtiaz <[email-masked]>
Date: Wed, 15 May 2024 09:21:52 +0500

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

---

From: Dimitrios Apostolou <[email-masked]>
Date: Wed, 15 May 2024 14:59:39 +0200 (CEST)

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.
> In the pg_stat_io view, statistics related to I/O operations performed by these background workers are recorded. 
>
> Regards,
> Imtiaz
>
>

---

From: Kashif Zeeshan <[email-masked]>
Date: Wed, 15 May 2024 19:32:32 +0500

Hi

parallel worker are used for parallel execution of the queries and you can
find the help in the below link.

https://www.postgresql.org/docs/current/how-parallel-query-works.html

Its controlled by following parameters.

max_worker_processes = 6
max_parallel_workers_per_gather = 3
max_parallel_workers = 6

The limit of concurrent parallel workers for the whole cluster is
max_parallel_workers, which must be ≤ max_worker_processes. The limit of
parallel workers per query is max_parallel_workers_per_gather.

Thanks
Kashif Zeeshan
Bitnine Global

On Wed, May 15, 2024 at 5:59 PM Dimitrios Apostolou <[email-masked]> wrote:

> > In the pg_stat_io view, statistics related to I/O operations performed
> by these background workers are recorded.
> >
> > Regards,
> > Imtiaz
> >
> >
> >           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
> >
> >
> >
> >
> >
>

---

From: Dimitrios Apostolou <[email-masked]>
Date: Tue, 21 May 2024 21:20:36 +0200 (CEST)

Hi Kashif, just to clarify my question, are you saying that the I/O from
parallel workers is recorded into the "background worker" entry of
pg_stat_io?

Thanks,
Dimitris

On Wed, 15 May 2024, Kashif Zeeshan wrote:

> Hi
> parallel worker are used for parallel execution of the queries and you can find the help in the below link.
>
> The limit of concurrent parallel workers for the whole cluster is max_parallel_workers, which must be ≤ max_worker_processes. The limit of parallel workers per query is max_parallel_workers_per_gather.
>
> Thanks
> Kashif Zeeshan
> Bitnine Global
>