pg_stat_database

A statistics view showing collected statistics for each 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 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

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

Categories

Statistics / monitoring view

See also

pg_database, pg_stat_database_conflicts