pg_stat_database
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.
Definition by PostgreSQL version
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 | | |
Documentation: 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 | | |
Documentation: 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 | | |
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 14
- following columns added (commit 960869da):
session_time
active_time
idle_in_transaction_time
sessions
sessions_abandoned
sessions_fatal
sessions_killed
- following columns added (commit 960869da):
- PostgreSQL 12
- PostgreSQL 9.2
- PostgreSQL 9.1
- column
conflicts
added (commit 40d9e94b)
- column
- PostgreSQL 8.3
- Following columns added (commit 51d7741d):
tup_returned
tup_fetched
tup_inserted
tup_updated
tup_deleted
- Following columns added (commit 51d7741d):
- PostgreSQL 7.2
- added (commit 8d80b0d9)
Examples
Example pg_stat_database
contents for the postgres
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
Example database statistics for cluster-wide shared objects (PostgreSQL 12 and later):
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
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