3

In this answer to the question Right query to get the current number of connections in a PostgreSQL DB the poster implies that

SELECT sum(numbackends) FROM pg_stat_database;

and 

SELECT count(*) FROM pg_stat_activity;

give the same results.

However, if I do this on my db the first one says 119 and the second one 30.

This is the difference as shown by summing numbackends and counting:

+------+-------------+-------+
|      | numbackends | count |
+------+-------------+-------+
| db1  |           1 |     1 |
| db2  |           1 |     1 |
| db3  |           1 |     1 |
| db4  |           1 |     1 |
| db5  |           2 |     2 |
| db6  |           2 |     2 |
| db7  |          12 |     3 | <--
| db8  |           4 |     4 |
| db9  |           5 |     5 |
| db10 |          78 |    35 | <--
+------+-------------+-------+
  • Why does this difference exist?
  • How can I list each of the 119-30=89 backends not shown in pg_stat_activity?
salient
  • 2,316
  • 6
  • 28
  • 43
  • the difference is constantly big? it is not a temporary change due to a batch running... – wargre Aug 29 '17 at 11:59
  • yep. it's constantly big. why would a batch running cause it? – salient Aug 29 '17 at 12:00
  • I was thinking of a temporary process that open a big number of backend, but not really your case. you can compare `select datname,numbackends from pg_stat_database where numbackends<>0;` and `select datname, count(*) from pg_stat_activity group by datname;` to check the database that has a big difference. Your database is running for a long time ? can it be some backend process that has crashed ? what's happen if you restart ? is the number start to change quickly ? – wargre Aug 29 '17 at 12:06
  • good idea. edited the question with the output. from what I can see there are two dbs with diffs. and one with a huge diff. so if I understand you correctly `process != backend != connection`? – salient Aug 29 '17 at 13:42
  • 1
    1 connection create one process on the server ( process = backend). Nothing specific on those 2 DBs? – wargre Aug 29 '17 at 13:59
  • nope, not really, more than that they are used more than the other dbs + we recently had a `too many connections`-problem which is the reason I'm trying to debug the conns and hit this – salient Aug 29 '17 at 14:00
  • I don't have a real idea on that, except that could be from some funny stuff on counter because of those error. You will only know if you restart the db and check counter are ok when there is no error. (I try to replicate too many connections error, but did not got your issue). – wargre Aug 29 '17 at 14:14
  • 1
    I don't have an answer but both queries are obsolete to find the number of connections due do intra-query parallelism and background workers which did not exist at the time of the linked answer. – Daniel Vérité Aug 29 '17 at 15:28
  • so if I understand correctly backends ≈ the total number of "concurrent processes", while `pg_stat_activity` basically lists one per process. The plot thickens: it looks like you can get a list of all the backends with `SELECT pg_stat_get_activity(NULL)` but it'd be very nice to actually be able to tie these back down to the "parent" system processes. – salient Aug 29 '17 at 15:54
  • 1
    I just checked, and background workers for parallel queries are counted in both queries. Also, both queries call `pgstat_fetch_stat_numbackends()` from `backend/postmaster/pgstat.c` to get the total backend count, so I can't imagine what the difference might me. Mysterious. – Laurenz Albe Sep 22 '17 at 08:18

0 Answers0