patternsqlCritical

Number of active connections and remaining connections

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

remainingnumberactiveandconnections

Problem

I would like to get statistics about the peak number of connections over a period of time.

I know the pg_stat_activity view, like select count(*) from pg_stat_activity, but I think this method is not very smart.

Are there other views or tables that can provide the information I need?

Solution

This SQL will help you

select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal 
from 
  (select count(*) used from pg_stat_activity) t1,
  (select setting::int res_for_super from pg_settings where name=$superuser_reserved_connections$) t2,
  (select setting::int max_conn from pg_settings where name=$max_connections$) t3

Result:

max_conn | used | res_for_super | res_for_normal 
---------+------+---------------+----------------
  100    |    2 |             3 |             95
(1 row)

You can put this in shell:

#!/bin/bash
for (( c=1; c> /home/pgdba/res_data.log
     sleep 1  # once per second
done

or you can record the results into a table, then execute

postgres=# copy restbl to '/home/pgdba/res.csv' csv header;

to get result csv file.

Code Snippets

select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal 
from 
  (select count(*) used from pg_stat_activity) t1,
  (select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2,
  (select setting::int max_conn from pg_settings where name=$$max_connections$$) t3

max_conn | used | res_for_super | res_for_normal 
---------+------+---------------+----------------
  100    |    2 |             3 |             95
(1 row)

#!/bin/bash
for (( c=1; c<=3600; c++ ))
do
     gsql -U pgdba -W pgdba -p 6432 -c "sql" >> /home/pgdba/res_data.log
     sleep 1  # once per second
done

postgres=# copy restbl to '/home/pgdba/res.csv' csv header;

Context

StackExchange Database Administrators Q#161760, answer score: 70

Revisions (0)

No revisions yet.