Which of the following two is more accurate?
select numbackends from pg_stat_database;
select count(*) from pg_stat_activity;
This question is related to
sql
database
postgresql
dbconnection
Number of TCP connections will help you. Remember that it is not for a particular database
netstat -a -n | find /c "127.0.0.1:13306"
The following query is very helpful
select * from
(select count(*) used from pg_stat_activity) q1,
(select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) q2,
(select setting::int max_conn from pg_settings where name=$$max_connections$$) q3;
They definitely may give different results. The better one is
select count(*) from pg_stat_activity;
It's because it includes connections to WAL sender processes which are treated as regular connections and count towards max_connections
.
See max_wal_senders
From looking at the source code, it seems like the pg_stat_database query gives you the number of connections to the current database for all users. On the other hand, the pg_stat_activity query gives the number of connections to the current database for the querying user only.
Aggregation of all postgres sessions per their status (how many are idle, how many doing something...)
select state, count(*) from pg_stat_activity where pid <> pg_backend_pid() group by 1 order by 1;
Source: Stackoverflow.com