PostgreSQL 14
Session statistics in pg_stat_database
For anyone who suspects connections are ending badly and has only the log to prove it with.
Reference page, revised in place. Last updated .
Connections were a gauge and nothing else
Everything a cluster could say about its connections used to be instantaneous. The per-database view carried a count of backends attached right now, and the activity view listed them with their current state. Both are gauges: they tell you the situation at the moment you looked and nothing about the situation between two looks.
That leaves the interesting questions unanswerable. Whether a pool is churning connections or holding them. How much of the cluster’s time is spent with a transaction open and nobody talking to it. Whether the connection count you sampled was typical of the minute or a spike you happened to catch. And most practically, whether sessions are ending the way they should, because a connection that drops badly leaves a log line and nothing else, and log lines are not a time series.
PostgreSQL 14 added counters. How many sessions the database has seen, how long they spent connected in total, how much of that was executing, how much was sitting inside an open transaction doing nothing, and three counts of the ways a session can end other than normally. The System Views section of the release notes records the whole set as one entry, which understates it: this is seven columns that turn several gauges into rates.
Where the idle time comes from
The fixture opens a second session, starts a transaction in it, leaves it alone for a while, and commits. That is the shape of the problem these columns exist to measure, and it is invisible in a connection count.
CREATE EXTENSION dblink;CREATE EXTENSIONSELECT dblink_connect('till', 'dbname=' || current_database() || ' user=postgres');
SELECT dblink_exec('till', 'BEGIN');
SELECT pg_sleep(2);
SELECT dblink_exec('till', 'COMMIT');
SELECT dblink_disconnect('till');
SELECT pg_sleep(1);
SELECT sessions, sessions_abandoned, sessions_fatal, sessions_killed,
round(session_time::numeric / 1000, 1) AS session_s,
round(active_time::numeric / 1000, 1) AS active_s,
round(idle_in_transaction_time::numeric / 1000, 1) AS idle_in_txn_s
FROM pg_stat_database
WHERE datname = current_database(); dblink_connect
----------------
OK
(1 row)
dblink_exec
-------------
BEGIN
(1 row)
pg_sleep
----------
(1 row)
dblink_exec
-------------
COMMIT
(1 row)
dblink_disconnect
-------------------
OK
(1 row)
pg_sleep
----------
(1 row)
sessions | sessions_abandoned | sessions_fatal | sessions_killed | session_s | active_s | idle_in_txn_s
----------+--------------------+----------------+-----------------+-----------+----------+---------------
3 | 0 | 0 | 0 | 5.1 | 2.1 | 2.0
(1 row)The two seconds the second session spent holding a transaction open with nothing to do are counted, and they are counted separately from the time it spent executing. That is the number worth having on a graph, because idle-in-transaction time is the direct cause of two expensive problems at once: it holds the oldest visible transaction back, which stops vacuum reclaiming anything newer, and it holds whatever locks the transaction has taken.
Read the three time columns as a budget rather than individually. Total connected time is the denominator; executing time and idle-in-transaction time are two slices of it, and the remainder is sessions connected and idle outside a transaction, which is a pool doing its job. A database whose idle-in-transaction slice is more than a sliver has an application that opens transactions before it needs them, and no amount of pool tuning fixes that.
These are cumulative totals in milliseconds, so the useful form is a difference between two samples divided by the interval. Expressed that way the units come out as seconds of database time per second of wall time, which is directly comparable across databases of different sizes and is the form worth putting on a dashboard.
The three ways a session ends badly
The counters at the front of that output are the ones people underuse, and they distinguish three things that a connection count cannot.
SELECT dblink_connect('doomed', 'dbname=' || current_database() || ' user=postgres');
SELECT dblink_exec('doomed', 'BEGIN');
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE datname = current_database()
AND pid <> pg_backend_pid()
AND state = 'idle in transaction';
SELECT pg_sleep(1);
SELECT sessions, sessions_abandoned, sessions_fatal, sessions_killed
FROM pg_stat_database WHERE datname = current_database(); dblink_connect
----------------
OK
(1 row)
dblink_exec
-------------
BEGIN
(1 row)
pg_terminate_backend
----------------------
t
(1 row)
pg_sleep
----------
(1 row)
sessions | sessions_abandoned | sessions_fatal | sessions_killed
----------+--------------------+----------------+-----------------
5 | 0 | 0 | 1
(1 row)The killed counter moved, because that session was terminated by an administrative command. The other two count different failures: abandoned means the client went away without saying goodbye, which is what a crashed application process, a network partition or an aggressive firewall idle timeout looks like from the server’s side; fatal means the server ended the session itself because of an error, which includes running out of memory and the recovery conflicts a standby raises.
The distinction is the value. A rising abandoned count points outward, at the network or at whatever is killing application processes. A rising fatal count points inward, at the server. A rising killed count points at whatever tooling is doing the terminating, which is often a timeout somebody set and forgot. Before 14 all three arrived as log lines of different shapes and none of them as a number.
One caveat, and it is about what is not counted rather than what is. A connection refused before it was established, because the connection limit was reached or authentication failed, never becomes a session and appears in none of these columns. A cluster being hammered by a client that cannot authenticate shows nothing here at all. That belongs to the log, still.
After the upgrade
Every column on this page exists unchanged on later majors, so nothing here needs rewriting for an upgrade. Two arrived beside them later.
SELECT string_agg(attname, ', ' ORDER BY attnum) AS session_and_parallel_columns
FROM pg_attribute
WHERE attrelid = 'pg_stat_database'::regclass AND attnum > 0
AND (attname LIKE 'session%' OR attname LIKE 'active_time'
OR attname LIKE 'idle%' OR attname LIKE 'parallel%');On 14:
session_and_parallel_columns
--------------------------------------------------------------------------------------------------------------------
session_time, active_time, idle_in_transaction_time, sessions, sessions_abandoned, sessions_fatal, sessions_killed
(1 row)On 18:
session_and_parallel_columns
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
session_time, active_time, idle_in_transaction_time, sessions, sessions_abandoned, sessions_fatal, sessions_killed, parallel_workers_to_launch, parallel_workers_launched
(1 row)The two new ones count parallel workers a query asked for against parallel workers it actually got, which is the first time a cluster could report that gap as a number rather than as a suspicion. A database where the two diverge is a database whose parallel query settings promise more than the worker pool can deliver, and it belongs to the same family as the columns on this page: a thing the server always knew and had never been asked to say.
What is genuinely different after the upgrade is the confidence you can place in these counters rather than their shape. On 14 they travel through the statistics collector, so a read taken immediately after a reset can still return the pre-reset value, which is a property of this version and disappears in 15.
What the executing total does and does not include
The three time columns partition a session’s connected lifetime, and getting the boundaries right is what stops the numbers being over-read.
Executing time covers everything between the server receiving a statement and finishing with it: parsing, planning, execution, and any time the backend spent waiting on a lock or on I/O in the middle of that. So it is not processor time and it is not a measure of how hard the database worked. A cluster whose executing total tracks its connected total is a cluster where sessions are busy, not necessarily one that is short of capacity, and the two are distinguished by the wait events underneath rather than by anything in this view.
Idle-in-transaction time is the mirror: the server is holding a transaction open and has nothing to do, because the client has not sent the next statement. Every millisecond of it is a millisecond an application spent thinking, or waiting on another service, or garbage collecting, with a database transaction open. That is why it is the number that most reliably points outside the database, and why the fix is almost always in the application’s transaction boundaries rather than in a setting.
The remainder, the part of connected time that is neither of those, is a session idle outside any transaction. On a pooled deployment that number being large is the pool working correctly, and it is worth computing so that the first two can be read as shares of something meaningful rather than as raw millisecond totals nobody can calibrate.
One boundary is not in any of the three. Time spent establishing the connection, including authentication, is not attributed to the session that results, so a cluster suffering slow connection setup shows nothing here at all.
What to alert on
- Idle-in-transaction time as a share of total connected time, per database. It is the single most useful number here and a threshold on it is defensible in a way that a raw connection count never was.
- New sessions per second. A pool that is working holds connections; a pool that is broken, or an application connecting directly, produces a rate that tracks request volume.
- Abandoned sessions per hour, separately from the other two endings. It is the one that usually means something outside the database changed.
Leave the total session time alone as an alert. It rises with the connection count by construction and it will page you for growth rather than for trouble.
What it costs to collect
Nothing here needs a restart or an extension. The counters depend on track_counts, which is on by default and which almost nothing else works without, and the per-session timing is accumulated by each backend as it changes state rather than sampled.
The one real cost is the reset. These are per-database totals, and the function that clears them clears the whole database’s statistics, including every table and index counter. A monitoring routine that resets a database’s statistics to get a clean window for session timing throws away everything else at the same time, and the tables lose their vacuum history with it. Take differences between samples instead; there is no case for resetting these counters on a schedule.