[PSQL - Count Number of Connections]Queries to obtain current/maximum count of connections with PostgreSQL #SQL

psql-count-connections.sql

-- tuning advise: https://www.cybertec-postgresql.com/en/tuning-max_connections-in-postgresql/
--

-- get number of connections by database instance + total
select datname, count(*) as cnt_conn, sum(count(*)) over () as tot_conn from pg_stat_activity group by datname order by datname;

-- get more details, see column numbackends for number of connections
select * from pg_stat_database order by datname;
select datname, numbackends, sum(numbackends) over() as tot_conn from pg_stat_database order by datname;

-- combine the two above
select coalesce(a.datname,b.datname) as datname, a.numbackends as cnt_conn_1, b.cnt_conn as cnt_conn_2, sum(a.numbackends) over() as tot_conn_1, b.tot_conn_2
from pg_stat_database a full outer join (select datname, count(*) as cnt_conn, sum(count(*)) over () as tot_conn_2 from pg_stat_activity s group by datname) b on a.datname = b.datname
order by coalesce(a.datname,b.datname);

-- show max number of connections, e.g. 600
show max_connections;

-- show private memory per connect, the private memory available for each operation during query execution is limited by work_mem, e.g. 4 MB
show work_mem;