Find Out What's Happening in Your PostgreSQL Database

So you want to monitor the behavior of your PostgreSQL database. Great! Let's start with a brief overview of how PostgreSQL collects statistics.

PostgreSQL has a set of statistics access functions, as well as a set of predefined statistics views. The views use the predefined statistics functions.

By default only a small number of statistics are collected. The statistics collection is controlled by the following configuration parameters:

  • track_counts - controls whether statistics are collected about table and index accesses
  • track_functions - enables tracking of user-defined functions
  • track_activities - enables monitoring of the current command being executed by any server process

To turn on statistics collection, go into your postgresql.conf file and set the following parameters:

track_activities = on
track_counts = on
track_functions = all                 # none, pl (= procedural language functions), all

Keep in mind that the statistics are not updated in real time. Processes write their statistics after a query or a transaction has been finished. Moreover, statistics are published in intervals; the standard interval is half a second. Important note: statistics available for a transaction are fetched at the beginning of the transaction and they do not change while the transaction is running.

So what can we get from reading the statistics?

Monitoring database load

The pg_stat_database view has the following columns:

 datid  |   datname   | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted 
--------+-------------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------

where

  • numbackends - number of active connections
  • xact_commit - number of commits
  • xact_rollback - number of rollbacks
  • blks_read- total block read
  • blks_hit - total buffer hits

To retrieve this information, try running a query which returns the number of connections, number of commits and rollbacks, number of reads, and percentage of buffer hits. It looks roughly (because I'm too lazy to handle division by zero correctly) like this:

SELECT numbackends as CONN, xact_commit as TX_COMM, xact as TX_RLBCK, blks_read + blks_hit as READ_TOTAL, blks_hit * 100 / (blks_read + blks_hit) as BUFFER
FROM pg_stat_database
WHERE datname = '$DB_NAME' ;

This query returns the statistics from the last reset.

To monitor the DB load:

  • reset the statistics with the function pg_stat_reset()
  • wait a period of time
  • invoke the statistics query

Monitoring buffer hits

The view pg_statio_user_tables has the following columns:

 relid  | schemaname |   relname   | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit 
--------+------------+-------------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------

 

where

  • relid - table OID
  • heap_blks_read - number of disk block reads from the table
  • heap_blks_hit- number of buffer hits for the table
  • idx_blks_read - number of disk blocks read for all indexes of the table
  • idx_blks_hit - number of buffer hits for all indexes of the table
  • toast_blks_read - number of disk block reads for the table's TOAST table
  • toast_blks_hit - number of buffer hits for the table's TOAST table
  • tidx_blks_read - number of disk block reads for the TOAST table's index
  • tidx_blks_hit - number of buffer hits for the TOAST table's index

The view pg_station_user_sequences has the following columns:

 relid  | schemaname |  relname   | blks_read | blks_hit 
--------+------------+------------+-----------+----------

 

An SQL query that computes buffer hits percentages looks roughly like this:

SELECT sum(heap_blks_read) / sum(heap_blks_hit) * 100 as TABLE, \
    sum(idx_blks_read) / sum(idx_blks_hit) * 100 as INDEX, \
    sum(toast_blks_read) / sum(toast_blks_hit) * 100 as TOAST, \
    sum(tidx_blks_read) / sum(tidx_blks_hit) as TOASTIND \
    FROM pg_statio_user_tables) tables, \
    (SELECT sum(blks_read) / sum(blks_hit) * 100 as SEQUENCE 
    FROM pg_statio_user_sequences) sequences

Invoke it in conjunction with pg_stat_reset() just like we did with the workload monitoring query in the previous section.

Do you use PostgreSQL statistics facilities?