pg_stat_database 是一个 统计视图，显示每个数据库收集的统计信息，并且从 PostgreSQL 12 开始，还显示集群范围共享对象的统计信息。
pg_stat_database 在 PostgreSQL 7.2 中添加。
按 PostgreSQL 版本定义
pg_stat_database (PostgreSQL 17)
View "pg_catalog.pg_stat_database"
Column | Type | Collation | Nullable | Default
--------------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
numbackends | integer | | |
xact_commit | bigint | | |
xact_rollback | bigint | | |
blks_read | bigint | | |
blks_hit | bigint | | |
tup_returned | bigint | | |
tup_fetched | bigint | | |
tup_inserted | bigint | | |
tup_updated | bigint | | |
tup_deleted | bigint | | |
conflicts | bigint | | |
temp_files | bigint | | |
temp_bytes | bigint | | |
deadlocks | bigint | | |
checksum_failures | bigint | | |
checksum_last_failure | timestamp with time zone | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
session_time | double precision | | |
active_time | double precision | | |
idle_in_transaction_time | double precision | | |
sessions | bigint | | |
sessions_abandoned | bigint | | |
sessions_fatal | bigint | | |
sessions_killed | bigint | | |
stats_reset | timestamp with time zone | | |
文档: pg_stat_database
pg_stat_database (PostgreSQL 16)
View "pg_catalog.pg_stat_database"
Column | Type | Collation | Nullable | Default
--------------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
numbackends | integer | | |
xact_commit | bigint | | |
xact_rollback | bigint | | |
blks_read | bigint | | |
blks_hit | bigint | | |
tup_returned | bigint | | |
tup_fetched | bigint | | |
tup_inserted | bigint | | |
tup_updated | bigint | | |
tup_deleted | bigint | | |
conflicts | bigint | | |
temp_files | bigint | | |
temp_bytes | bigint | | |
deadlocks | bigint | | |
checksum_failures | bigint | | |
checksum_last_failure | timestamp with time zone | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
session_time | double precision | | |
active_time | double precision | | |
idle_in_transaction_time | double precision | | |
sessions | bigint | | |
sessions_abandoned | bigint | | |
sessions_fatal | bigint | | |
sessions_killed | bigint | | |
stats_reset | timestamp with time zone | | |
文档: pg_stat_database
pg_stat_database (PostgreSQL 15)
View "pg_catalog.pg_stat_database"
Column | Type | Collation | Nullable | Default
--------------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
numbackends | integer | | |
xact_commit | bigint | | |
xact_rollback | bigint | | |
blks_read | bigint | | |
blks_hit | bigint | | |
tup_returned | bigint | | |
tup_fetched | bigint | | |
tup_inserted | bigint | | |
tup_updated | bigint | | |
tup_deleted | bigint | | |
conflicts | bigint | | |
temp_files | bigint | | |
temp_bytes | bigint | | |
deadlocks | bigint | | |
checksum_failures | bigint | | |
checksum_last_failure | timestamp with time zone | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
session_time | double precision | | |
active_time | double precision | | |
idle_in_transaction_time | double precision | | |
sessions | bigint | | |
sessions_abandoned | bigint | | |
sessions_fatal | bigint | | |
sessions_killed | bigint | | |
stats_reset | timestamp with time zone | | |
文档: pg_stat_database
pg_stat_database (PostgreSQL 14)
View "pg_catalog.pg_stat_database"
Column | Type | Collation | Nullable | Default
--------------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
numbackends | integer | | |
xact_commit | bigint | | |
xact_rollback | bigint | | |
blks_read | bigint | | |
blks_hit | bigint | | |
tup_returned | bigint | | |
tup_fetched | bigint | | |
tup_inserted | bigint | | |
tup_updated | bigint | | |
tup_deleted | bigint | | |
conflicts | bigint | | |
temp_files | bigint | | |
temp_bytes | bigint | | |
deadlocks | bigint | | |
checksum_failures | bigint | | |
checksum_last_failure | timestamp with time zone | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
session_time | double precision | | |
active_time | double precision | | |
idle_in_transaction_time | double precision | | |
sessions | bigint | | |
sessions_abandoned | bigint | | |
sessions_fatal | bigint | | |
sessions_killed | bigint | | |
stats_reset | timestamp with time zone | | |
文档: pg_stat_database
pg_stat_database (PostgreSQL 13)
View "pg_catalog.pg_stat_database"
Column | Type | Collation | Nullable | Default
-----------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
numbackends | integer | | |
xact_commit | bigint | | |
xact_rollback | bigint | | |
blks_read | bigint | | |
blks_hit | bigint | | |
tup_returned | bigint | | |
tup_fetched | bigint | | |
tup_inserted | bigint | | |
tup_updated | bigint | | |
tup_deleted | bigint | | |
conflicts | bigint | | |
temp_files | bigint | | |
temp_bytes | bigint | | |
deadlocks | bigint | | |
checksum_failures | bigint | | |
checksum_last_failure | timestamp with time zone | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
stats_reset | timestamp with time zone | | |
文档: pg_stat_database
pg_stat_database (PostgreSQL 12)
View "pg_catalog.pg_stat_database"
Column | Type | Collation | Nullable | Default
-----------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
numbackends | integer | | |
xact_commit | bigint | | |
xact_rollback | bigint | | |
blks_read | bigint | | |
blks_hit | bigint | | |
tup_returned | bigint | | |
tup_fetched | bigint | | |
tup_inserted | bigint | | |
tup_updated | bigint | | |
tup_deleted | bigint | | |
conflicts | bigint | | |
temp_files | bigint | | |
temp_bytes | bigint | | |
deadlocks | bigint | | |
checksum_failures | bigint | | |
checksum_last_failure | timestamp with time zone | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
stats_reset | timestamp with time zone | | |
文档: pg_stat_database
pg_stat_database (PostgreSQL 11)
View "pg_catalog.pg_stat_database"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
numbackends | integer | | |
xact_commit | bigint | | |
xact_rollback | bigint | | |
blks_read | bigint | | |
blks_hit | bigint | | |
tup_returned | bigint | | |
tup_fetched | bigint | | |
tup_inserted | bigint | | |
tup_updated | bigint | | |
tup_deleted | bigint | | |
conflicts | bigint | | |
temp_files | bigint | | |
temp_bytes | bigint | | |
deadlocks | bigint | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
stats_reset | timestamp with time zone | | |
文档: pg_stat_database
pg_stat_database (PostgreSQL 10)
View "pg_catalog.pg_stat_database"
Column | Type | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
numbackends | integer | | |
xact_commit | bigint | | |
xact_rollback | bigint | | |
blks_read | bigint | | |
blks_hit | bigint | | |
tup_returned | bigint | | |
tup_fetched | bigint | | |
tup_inserted | bigint | | |
tup_updated | bigint | | |
tup_deleted | bigint | | |
conflicts | bigint | | |
temp_files | bigint | | |
temp_bytes | bigint | | |
deadlocks | bigint | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
stats_reset | timestamp with time zone | | |
文档: pg_stat_database
pg_stat_database (PostgreSQL 9.6)
View "pg_catalog.pg_stat_database"
Column | Type | Modifiers
----------------+--------------------------+-----------
datid | oid |
datname | name |
numbackends | integer |
xact_commit | bigint |
xact_rollback | bigint |
blks_read | bigint |
blks_hit | bigint |
tup_returned | bigint |
tup_fetched | bigint |
tup_inserted | bigint |
tup_updated | bigint |
tup_deleted | bigint |
conflicts | bigint |
temp_files | bigint |
temp_bytes | bigint |
deadlocks | bigint |
blk_read_time | double precision |
blk_write_time | double precision |
stats_reset | timestamp with time zone |
文档: pg_stat_database
pg_stat_database (PostgreSQL 9.5)
View "pg_catalog.pg_stat_database"
Column | Type | Modifiers
----------------+--------------------------+-----------
datid | oid |
datname | name |
numbackends | integer |
xact_commit | bigint |
xact_rollback | bigint |
blks_read | bigint |
blks_hit | bigint |
tup_returned | bigint |
tup_fetched | bigint |
tup_inserted | bigint |
tup_updated | bigint |
tup_deleted | bigint |
conflicts | bigint |
temp_files | bigint |
temp_bytes | bigint |
deadlocks | bigint |
blk_read_time | double precision |
blk_write_time | double precision |
stats_reset | timestamp with time zone |
文档: pg_stat_database
pg_stat_database (PostgreSQL 9.4)
View "pg_catalog.pg_stat_database"
Column | Type | Modifiers
----------------+--------------------------+-----------
datid | oid |
datname | name |
numbackends | integer |
xact_commit | bigint |
xact_rollback | bigint |
blks_read | bigint |
blks_hit | bigint |
tup_returned | bigint |
tup_fetched | bigint |
tup_inserted | bigint |
tup_updated | bigint |
tup_deleted | bigint |
conflicts | bigint |
temp_files | bigint |
temp_bytes | bigint |
deadlocks | bigint |
blk_read_time | double precision |
blk_write_time | double precision |
stats_reset | timestamp with time zone |
文档: pg_stat_database
pg_stat_database (PostgreSQL 9.3)
View "pg_catalog.pg_stat_database"
Column | Type | Modifiers
----------------+--------------------------+-----------
datid | oid |
datname | name |
numbackends | integer |
xact_commit | bigint |
xact_rollback | bigint |
blks_read | bigint |
blks_hit | bigint |
tup_returned | bigint |
tup_fetched | bigint |
tup_inserted | bigint |
tup_updated | bigint |
tup_deleted | bigint |
conflicts | bigint |
temp_files | bigint |
temp_bytes | bigint |
deadlocks | bigint |
blk_read_time | double precision |
blk_write_time | double precision |
stats_reset | timestamp with time zone |
文档: pg_stat_database
pg_stat_database (PostgreSQL 9.2)
View "pg_catalog.pg_stat_database"
Column | Type | Modifiers
----------------+--------------------------+-----------
datid | oid |
datname | name |
numbackends | integer |
xact_commit | bigint |
xact_rollback | bigint |
blks_read | bigint |
blks_hit | bigint |
tup_returned | bigint |
tup_fetched | bigint |
tup_inserted | bigint |
tup_updated | bigint |
tup_deleted | bigint |
conflicts | bigint |
temp_files | bigint |
temp_bytes | bigint |
deadlocks | bigint |
blk_read_time | double precision |
blk_write_time | double precision |
stats_reset | timestamp with time zone |
文档: pg_stat_database
pg_stat_database (PostgreSQL 9.1)
View "pg_catalog.pg_stat_database"
Column | Type | Modifiers
---------------+--------------------------+-----------
datid | oid |
datname | name |
numbackends | integer |
xact_commit | bigint |
xact_rollback | bigint |
blks_read | bigint |
blks_hit | bigint |
tup_returned | bigint |
tup_fetched | bigint |
tup_inserted | bigint |
tup_updated | bigint |
tup_deleted | bigint |
conflicts | bigint |
stats_reset | timestamp with time zone |
文档: pg_stat_database
pg_stat_database (PostgreSQL 9.0)
View "pg_catalog.pg_stat_database"
Column | Type | Modifiers
---------------+---------+-----------
datid | oid |
datname | name |
numbackends | integer |
xact_commit | bigint |
xact_rollback | bigint |
blks_read | bigint |
blks_hit | bigint |
tup_returned | bigint |
tup_fetched | bigint |
tup_inserted | bigint |
tup_updated | bigint |
tup_deleted | bigint |
文档: pg_stat_database
pg_stat_database (PostgreSQL 8.4)
View "pg_catalog.pg_stat_database"
Column | Type | Modifiers
---------------+---------+-----------
datid | oid |
datname | name |
numbackends | integer |
xact_commit | bigint |
xact_rollback | bigint |
blks_read | bigint |
blks_hit | bigint |
tup_returned | bigint |
tup_fetched | bigint |
tup_inserted | bigint |
tup_updated | bigint |
tup_deleted | bigint |
View definition:
SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted
FROM pg_database d;
文档: pg_stat_database
pg_stat_database (PostgreSQL 8.3)
View "pg_catalog.pg_stat_database"
Column | Type | Modifiers
---------------+---------+-----------
datid | oid |
datname | name |
numbackends | integer |
xact_commit | bigint |
xact_rollback | bigint |
blks_read | bigint |
blks_hit | bigint |
tup_returned | bigint |
tup_fetched | bigint |
tup_inserted | bigint |
tup_updated | bigint |
tup_deleted | bigint |
View definition:
SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted
FROM pg_database d;
文档: pg_stat_database
pg_stat_database (PostgreSQL 8.2)
View "pg_catalog.pg_stat_database"
Column | Type | Modifiers
---------------+---------+-----------
datid | oid |
datname | name |
numbackends | integer |
xact_commit | bigint |
xact_rollback | bigint |
blks_read | bigint |
blks_hit | bigint |
View definition:
SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit
FROM pg_database d;
文档: pg_stat_database
更改历史记录
注意：可能不完整
- PostgreSQL 14
- 添加了以下列 (提交 960869da)
session_timeactive_timeidle_in_transaction_timesessionssessions_abandonedsessions_fatalsessions_killed
- 添加了以下列 (提交 960869da)
- PostgreSQL 12
- PostgreSQL 9.2
- PostgreSQL 9.1
- PostgreSQL 8.3
- 添加了以下列 (提交 51d7741d)
tup_returnedtup_fetchedtup_insertedtup_updatedtup_deleted
- 添加了以下列 (提交 51d7741d)
- PostgreSQL 7.2
- 添加 (提交 8d80b0d9)
示例
数据库 postgres 的 pg_stat_database 内容示例
postgres=# SELECT * FROM pg_stat_database WHERE datname='postgres'\gx -[ RECORD 1 ]---------+------------------------------ datid | 12679 datname | postgres numbackends | 1 xact_commit | 2195254 xact_rollback | 462 blks_read | 1666 blks_hit | 71012910 tup_returned | 855533284 tup_fetched | 21243092 tup_inserted | 41 tup_updated | 16 tup_deleted | 13 conflicts | 0 temp_files | 0 temp_bytes | 0 deadlocks | 0 checksum_failures | 0 checksum_last_failure | blk_read_time | 0 blk_write_time | 0 stats_reset | 2020-02-22 04:50:43.948558+01
集群范围共享对象的数据库统计信息示例 (PostgreSQL 12 及更高版本)
postgres=# SELECT * FROM pg_stat_database WHERE datid = 0\gx -[ RECORD 1 ]---------+------------------------------ datid | 0 datname | numbackends | 0 xact_commit | 0 xact_rollback | 0 blks_read | 312 blks_hit | 86292002 tup_returned | 29833418 tup_fetched | 17785095 tup_inserted | 417 tup_updated | 6 tup_deleted | 217 conflicts | 0 temp_files | 0 temp_bytes | 0 deadlocks | 0 checksum_failures | 0 checksum_last_failure | blk_read_time | 0 blk_write_time | 0 stats_reset | 2020-02-22 04:50:43.948614+01
参考文献
- PostgreSQL 文档： pg_stat_database
有用链接
- 深入探讨 postgres 统计信息：pg_stat_database - Alexey Lesovsky / Data Egret 于 2017 年 3 月撰写的博文