In this section, we will take a look at the background writer statistics. As you may already know, database connections will, in many cases, not write blocks to disks directly. Instead, data is written by the background writer process or by the checkpointer.

To see how data is written, inspect the pg_stat_bgwriter view:

test=# d pg_stat_bgwriter  
                        View "pg_catalog.pg_stat_bgwriter"
        Column         |           Type           | Collation | Nullable | Default 
-----------------------+--------------------------+-----------+----------+---------
 checkpoints_timed     | bigint                   |           |          | 
 checkpoints_req       | bigint                   |           |          | 
 checkpoint_write_time | double precision         |           |          | 
 checkpoint_sync_time  | double precision         |           |          | 
 buffers_checkpoint    | bigint                   |           |          | 
 buffers_clean         | bigint                   |           |          | 
 maxwritten_clean      | bigint                   |           |          | 
 buffers_backend       | bigint                   |           |          | 
 buffers_backend_fsync | bigint                   |           |          | 
 buffers_alloc         | bigint                   |           |          | 
 stats_reset           | timestamp with time zone |           |          | 

What should first catch your attention here are the first two columns. Later in this book, you will learn that PostgreSQL will perform regular checkpoints, which are necessary to ensure that data has really made it to disk. If your checkpoints are too close to each other, checkpoint_req may point you in the right direction. If requested checkpoints are high, this may mean that a lot of data has been written and that checkpoints are always triggered because of high throughput. In addition to that, PostgreSQL will tell you about how much time is needed to write data during a checkpoint and the time needed to sync. In addition to that, buffers_checkpoint indicates how many buffers were written during the checkpoint and how many were written by the background writer (buffers_clean).

But there's more: maxwritten_clean tells us about the number of times the background writer stopped a cleaning scan because it had written too many buffers.

Finally, there's buffers_backend (the number of buffers directly written by a backend database connection), buffers_backend_fsync (the number of buffers flushed by a database connection), and buffers_alloc (contains the number of buffers allocated). In general, it isn't a good thing if database connections start to write their own stuff themselves.