Waiting for PostgreSQL 14 – Add pg_stat_database counters for sessions and session time

On 17th of January 2021, Magnus Hagander committed patch:

Add pg_stat_database counters for sessions and session time
 
This add counters for number of sessions, the different kind of session
termination types, and timers for how much time is spent in active vs
idle in a database to pg_stat_database.
 
Internally this also renames the parameter "force" to disconnect. This
was the only use-case for the parameter before, so repurposing it to
this mroe narrow usecase makes things cleaner than inventing something
new.
 
Author: Laurenz Albe
Reviewed-By: Magnus Hagander, Soumyadeep Chakraborty, Masahiro Ikeda
Discussion: https://postgr.es/m/b07e1f9953701b90c66ed368656f2aef40cac4fb.camel@cybertec.at

So, new columns were added to pg_stat_database system view:

  • session_time
  • active_time
  • idle_in_transaction_time
  • sessions
  • sessions_abandoned
  • sessions_fatal
  • sessions_killed

*time columns are values in miliseconds that show total duration of all sessions to given database, time spent on running queries (vs. idle), and how much time was spent in idle in transaction state.

Then, we have 4 counters which give number of sessions – first is total number of sessions, and the other three are sessions that ended in a way that isn't normal, graceful, exit:

  • sessions_abandoned – usually network connectivity issues, or client app died
  • sessions_fatal – sessions that ended because of an error with FATAL severity
  • sessions_killed – as name suggests – sessions killed by user – pg_terminate_backend() call, for example

All these stats are visible live:

$ SELECT * FROM pg_stat_database WHERE datname = 'depesz' \gx
─[ RECORD 1 ]────────────┬──────────────────────────────
datid                    │ 220513
datname                  │ depesz
numbackends              │ 1
xact_commit              │ 296
xact_rollback            │ 2
blks_read                │ 321
blks_hit                 │ 9137
tup_returned             │ 87291
tup_fetched              │ 2768
tup_inserted             │ 6
tup_updated              │ 9
tup_deleted              │ 0
conflicts                │ 0
temp_files               │ 0
temp_bytes               │ 0
deadlocks                │ 0
checksum_failures        │ [NULL]
checksum_last_failure    │ [NULL]
blk_read_time            │ 4.25
blk_write_time           │ 0
session_time             │ 3954923.837
active_time              │ 90.272
idle_in_transaction_time │ 0
sessions                 │ 14
sessions_abandoned       │ 0
sessions_fatal           │ 0
sessions_killed          │ 0
stats_reset              │ 2021-01-18 12:19:08.396411+01

And can be very useful to plot things – specifically plotting active_time changes can be very interesting.

Looks amazing, thanks a lot to all involved.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.