Right query to get the current number of connections in a PostgreSQL DB
SqlDatabasePostgresqlDbconnectionSql Problem Overview
Which of the following two is more accurate?
select numbackends from pg_stat_database;
select count(*) from pg_stat_activity;
Sql Solutions
Solution 1 - Sql
Those two requires aren't equivalent. The equivalent version of the first one would be:
SELECT sum(numbackends) FROM pg_stat_database;
In that case, I would expect that version to be slightly faster than the second one, simply because it has fewer rows to count. But you are not likely going to be able to measure a difference.
Both queries are based on exactly the same data, so they will be equally accurate.
Solution 2 - Sql
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;
Solution 3 - Sql
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
Solution 4 - Sql
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;
Solution 5 - Sql
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.