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://firstname.lastname@example.org
So, new columns were added to pg_stat_database system view:
*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.