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 (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

Categories

Statistics / monitoring view

See also

pg_database, pg_stat_database_conflicts