55.31. pg_stats_vacuum_database #

The view pg_stats_vacuum_database will contain one row for each database in the current cluster, showing statistics about vacuuming that database.

Table 55.31. pg_stats_vacuum_database Columns

Column Type

Description

dbid oid

OID of a database

total_blks_read int8

Number of database blocks read by vacuum operations performed on this database

total_blks_hit int8

Number of times database blocks were found in the buffer cache by vacuum operations performed on this database

total_blks_dirtied int8

Number of database blocks dirtied by vacuum operations performed on this database

total_blks_written int8

Number of database blocks written by vacuum operations performed on this database

wal_records int8

Total number of WAL records generated by vacuum operations performed on this database

wal_fpi int8

Total number of WAL full page images generated by vacuum operations performed on this database

wal_bytes numeric

Total amount of WAL bytes generated by vacuum operations performed on this database

blk_read_time float8

Time spent reading database blocks by vacuum operations performed on this database, in milliseconds (if track_io_timing is enabled, otherwise zero)

blk_write_time float8

Time spent writing database blocks by vacuum operations performed on this database, in milliseconds (if track_io_timing is enabled, otherwise zero)

delay_time float8

Time spent sleeping in a vacuum delay point by vacuum operations performed on this database, in milliseconds (see Section 19.4.4 for details)

system_time float8

System CPU time of vacuuming this database, in milliseconds

user_time float8

User CPU time of vacuuming this database, in milliseconds

total_time float8

Total time of vacuuming this database, in milliseconds

interrupts int4

Number of times vacuum operations performed on this database were interrupted on any errors


Note

On upgrading your server just by replacing the executables, update the pg_stats_vacuum_database view as superuser who ran initdb:

  • For each database, including template1, but not including template0, run:

    CREATE OR REPLACE VIEW pg_stats_vacuum_database AS
    SELECT
      db.oid as dboid,  stats.db_blks_read,
      stats.db_blks_hit,
      stats.total_blks_dirtied,
      stats.total_blks_written,  stats.wal_records,
      stats.wal_fpi,
      stats.wal_bytes,  stats.blk_read_time,
      stats.blk_write_time,  stats.delay_time,
      stats.system_time,
      stats.user_time,
      stats.total_time,  stats.interrupts
    FROM
      pg_database db LEFT JOIN pg_stats_vacuum_database(db.oid) stats
    ON
      db.oid = stats.dboid;
    

  • For the template0 database, run:

    \c template1
    ALTER DATABASE template0 ALLOW_CONNECTIONS on;
    \c template0
    CREATE OR REPLACE VIEW pg_stats_vacuum_database AS
    SELECT
      db.oid as dboid,  stats.db_blks_read,
      stats.db_blks_hit,
      stats.total_blks_dirtied,
      stats.total_blks_written,  stats.wal_records,
      stats.wal_fpi,
      stats.wal_bytes,  stats.blk_read_time,
      stats.blk_write_time,  stats.delay_time,
      stats.system_time,
      stats.user_time,
      stats.total_time,  stats.interrupts
    FROM
      pg_database db LEFT JOIN pg_stats_vacuum_database(db.oid) stats
    ON
      db.oid = stats.dboid;
    \c template1
    ALTER DATABASE template0 ALLOW_CONNECTIONS off;