pg_stat_bgwriter
一个显示关于检查点和磁盘写入信息的视图
pg_stat_bgwriter 是一个统计视图，显示关于磁盘写入以及（直到PostgreSQL 16）检查点的信息。请注意，与视图名称和 PostgreSQL 文档暗示的相反，这些统计信息并非仅限于后台写入器进程。
pg_stat_bgwriter 在PostgreSQL 8.3 中添加。
统计重置
可以通过执行以下命令来重置 pg_stat_bgwriter 显示的统计信息
SELECT pg_stat_reset_shared('bgwriter')
pg_stat_bgwriter 和 pg_stat_checkpointer
从PostgreSQL 17开始，以下 pg_stat_bgwriter 列已合并到 pg_stat_checkpointer 中。
checkpoints_timed→num_timedcheckpoints_req→num_requestedcheckpoint_write_time→write_timecheckpoint_sync_time→sync_timebuffers_checkpoint→buffers_written
按 PostgreSQL 版本定义
pg_stat_bgwriter (PostgreSQL 19)
View "pg_catalog.pg_stat_bgwriter"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
buffers_clean | bigint | | |
maxwritten_clean | bigint | | |
buffers_alloc | bigint | | |
stats_reset | timestamp with time zone | | |
pg_stat_bgwriter (PostgreSQL 18)
View "pg_catalog.pg_stat_bgwriter"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
buffers_clean | bigint | | |
maxwritten_clean | bigint | | |
buffers_alloc | bigint | | |
stats_reset | timestamp with time zone | | |
pg_stat_bgwriter (PostgreSQL 17)
View "pg_catalog.pg_stat_bgwriter"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
buffers_clean | bigint | | |
maxwritten_clean | bigint | | |
buffers_alloc | bigint | | |
stats_reset | timestamp with time zone | | |
pg_stat_bgwriter (PostgreSQL 16)
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 | | |
pg_stat_bgwriter (PostgreSQL 15)
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 | | |
pg_stat_bgwriter (PostgreSQL 14)
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 | | |
pg_stat_bgwriter (PostgreSQL 13)
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 | | |
pg_stat_bgwriter (PostgreSQL 12)
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 | | |
pg_stat_bgwriter (PostgreSQL 11)
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 | | |
pg_stat_bgwriter (PostgreSQL 10)
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 | | |
pg_stat_bgwriter (PostgreSQL 9.6)
View "pg_catalog.pg_stat_bgwriter"
Column | Type | Modifiers
-----------------------+--------------------------+-----------
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 |
pg_stat_bgwriter (PostgreSQL 9.5)
View "pg_catalog.pg_stat_bgwriter"
Column | Type | Modifiers
-----------------------+--------------------------+-----------
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 |
pg_stat_bgwriter (PostgreSQL 9.4)
View "pg_catalog.pg_stat_bgwriter"
Column | Type | Modifiers
-----------------------+--------------------------+-----------
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 |
pg_stat_bgwriter (PostgreSQL 9.3)
View "pg_catalog.pg_stat_bgwriter"
Column | Type | Modifiers
-----------------------+--------------------------+-----------
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 |
pg_stat_bgwriter (PostgreSQL 9.2)
View "pg_catalog.pg_stat_bgwriter"
Column | Type | Modifiers
-----------------------+--------------------------+-----------
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 |
pg_stat_bgwriter (PostgreSQL 9.1)
View "pg_catalog.pg_stat_bgwriter"
Column | Type | Modifiers
-----------------------+--------------------------+-----------
checkpoints_timed | bigint |
checkpoints_req | bigint |
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 |
pg_stat_bgwriter (PostgreSQL 9.0)
View "pg_catalog.pg_stat_bgwriter"
Column | Type | Modifiers
--------------------+--------+-----------
checkpoints_timed | bigint |
checkpoints_req | bigint |
buffers_checkpoint | bigint |
buffers_clean | bigint |
maxwritten_clean | bigint |
buffers_backend | bigint |
buffers_alloc | bigint |
pg_stat_bgwriter (PostgreSQL 8.4)
View "pg_catalog.pg_stat_bgwriter"
Column | Type | Modifiers
--------------------+--------+-----------
checkpoints_timed | bigint |
checkpoints_req | bigint |
buffers_checkpoint | bigint |
buffers_clean | bigint |
maxwritten_clean | bigint |
buffers_backend | bigint |
buffers_alloc | bigint |
View definition:
SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_alloc() AS buffers_alloc;
pg_stat_bgwriter (PostgreSQL 8.3)
View "pg_catalog.pg_stat_bgwriter"
Column | Type | Modifiers
--------------------+--------+-----------
checkpoints_timed | bigint |
checkpoints_req | bigint |
buffers_checkpoint | bigint |
buffers_clean | bigint |
maxwritten_clean | bigint |
buffers_backend | bigint |
buffers_alloc | bigint |
View definition:
SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_alloc() AS buffers_alloc;
变更历史
- PostgreSQL 17
- 以下列已被移除 (commit 74604a37)
buffers_backendbuffers_backend_fsync
- 在引入
pg_stat_checkpointer后，以下列已被移除 (commit 96f05261)checkpoints_timedcheckpoints_reqcheckpoint_write_timecheckpoint_sync_timebuffers_checkpoint
- 以下列已被移除 (commit 74604a37)
- PostgreSQL 9.2
- PostgreSQL 9.1
- PostgreSQL 8.3
- 已添加 (commit 335feca4)
示例
pg_stat_bgwriter 的示例内容
postgres=# SELECT * FROM pg_stat_bgwriter\gx -[ RECORD 1 ]---------+------------------------------ checkpoints_timed | 38447 checkpoints_req | 77 checkpoint_write_time | 278878329 checkpoint_sync_time | 266106 buffers_checkpoint | 3799216 buffers_clean | 1603 maxwritten_clean | 14 buffers_backend | 1153320 buffers_backend_fsync | 0 buffers_alloc | 1832554 stats_reset | 2020-02-22 04:50:43.931931+01
参考资料
- PostgreSQL 文档: pg_stat_bgwriter 视图
有用链接
- 深入探讨 postgres 统计信息: pg_stat_bgwriter - Alexey Lesovsky 的博客文章，2017-03-07