Right query to get the current number of connections in a PostgreSQL DB

Question

Which of the following two is more accurate?

select numbackends from pg_stat_database;

select count(*) from pg_stat_activity;

Coniine

Better in terms of what? – Rigsdaler

Answer 1

Those two queries 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.

Lotion

Not true they are equally accurate. See my answer. – Preview

Note that when the postgres command line tool, PSQL is used for executing this query, the total number of connections is the result of this query - 1 since the psql connection made is also included as a connection – Komsomolsk

These two functions may pull from the same data set but do not always return the same values as you have written them. When I query there are null data base connections that are counted different in each. This works to get the same answer SELECT sum(numbackends) FROM pg_stat_database WHERE datname is not null; SELECT count(*) FROM pg_stat_activity WHERE datname is not null; – Ordinance

Answer 2

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;

Abnormality

can you explain what the 'used' and 'res_for_super' columns? – Goth

Hello, used are used connection, res_for_super is connections reserved for superuser access – Abnormality

This one is good as you can see at a glance how many are used at the moment, how many are reserved for superuser connection (i.e. think of a scenario when all the connections are consumed intentionally or not, then there are 3 left for admin to connect and kill some unnecessary/problematic ones), and the max possible number of connections. (as per the config). – Goldagoldarina

Answer 3

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

Preview

Answer 4

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;

Agrimony

Answer 5

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.

Zinkenite

That's incorrect. pg_stat_activity gives all connections as well, regardless of user. It then gives you a field which says which user it is, that you can filter on if you want to. It will not give you the text of the query if you're not either the same user or a superuser, but it will still show the connection. – Lotion

You're right. I didn't look closely enough at the view definition. The restriction on userid is just for the join against pg_authid. My mistake. – Zinkenite