pg_stat_database is a statistics view showing collected statistics for each database, and from PostgreSQL 12 for cluster-wide shared objects.
pg_stat_database was added in PostgreSQL 7.2 (commit 8d80b0d9).
Definition by PostgreSQL version
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 | | |
Documentation: 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 | | |
Documentation: 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 | | |
Documentation: 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 | | |
Documentation: 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 |
Documentation: 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 |
Documentation: 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 |
Documentation: 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 |
Documentation: 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 |
Documentation: 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 |
Documentation: 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 |
Documentation: 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;
Documentation: 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;
Documentation: 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;
Documentation: pg_stat_database
Change history
Note: may not be complete
- PostgreSQL 12
- PostgreSQL 9.2
- PostgreSQL 9.1
- Following columns added:
conflicts(commit 40d9e94b)
- Following columns added:
- PostgreSQL 8.3
- Following columns added (commit 51d7741d):
tup_returnedtup_fetchedtup_insertedtup_updatedtup_deleted
- Following columns added (commit 51d7741d):
- PostgreSQL 7.2 - added (commit 8d80b0d9)
References
- PostgreSQL documentation: pg_stat_database
Useful links
- Deep dive into postgres stats: pg_stat_database - March 2017 blog article by Alexey Lesovsky / Data Egret