219

Which of the following two is more accurate?

select numbackends from pg_stat_database;

select count(*) from pg_stat_activity;
Matthieu
  • 2,736
  • 4
  • 57
  • 87
Murali VP
  • 6,198
  • 4
  • 28
  • 36

5 Answers5

331

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.

informatik01
  • 16,038
  • 10
  • 74
  • 104
Magnus Hagander
  • 23,890
  • 5
  • 56
  • 43
  • 2
    Not true they are equally accurate. See my answer. – gargii Jun 28 '19 at 11:21
  • 7
    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 – neonidian Dec 13 '19 at 08:05
  • 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;` – blindguy Feb 24 '21 at 01:17
66

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;
tbo
  • 9,398
  • 8
  • 40
  • 51
  • 2
    can you explain what the 'used' and 'res_for_super' columns? – bet Feb 04 '19 at 21:41
  • Hello, used are used connection, res_for_super is connections reserved for superuser access – tbo Feb 07 '19 at 13:05
  • 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). – vadimbog Nov 12 '21 at 15:45
34

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

gargii
  • 1,010
  • 8
  • 16
15

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;
ďobo
  • 326
  • 2
  • 4
-4

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.

Brian L
  • 10,757
  • 5
  • 19
  • 17
  • 2
    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. – Magnus Hagander Mar 11 '11 at 08:51
  • 5
    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. – Brian L Mar 11 '11 at 18:45