What is better to use to find the number of active connections to the database?

Question

PostgreSQL

Hey everyone

I saw this

postgres=> SELECT numbackends FROM pg_stat_database WHERE datname = 'my_super_db';

and this

postgres=> SELECT count(*) FROM pg_stat_activity WHERE datname = 'my_super_db;

how about your experience? PostgreSQL 12

all the best,
Vadim

Answer 1

No difference. They have the same data source. You can also call pg_stat_get_db_numbackends(oid) directly.
In production, pg_stat_activity is usually used for monitoring to immediately pick up the grouping by backend statuses, the duration of active transactions. Well, most likely only the client backend will be interesting on this chart (well, maybe with parallel workers). For autovacuum processes, it is more convenient to have a separate schedule.