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

Which of the following two is more accurate?

select numbackends from pg_stat_database;

select count(*) from pg_stat_activity;

This question is related to sql database postgresql dbconnection

The answer is


Number of TCP connections will help you. Remember that it is not for a particular database

netstat -a -n | find /c "127.0.0.1:13306"

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;

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


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.


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;

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to postgresql

Subtracting 1 day from a timestamp date pgadmin4 : postgresql application server could not be contacted. Psql could not connect to server: No such file or directory, 5432 error? How to persist data in a dockerized postgres database using volumes input file appears to be a text format dump. Please use psql Postgres: check if array field contains value? Add timestamp column with default NOW() for new rows only Can't connect to Postgresql on port 5432 How to insert current datetime in postgresql insert query Connecting to Postgresql in a docker container from outside

Examples related to dbconnection

nodejs mysql Error: Connection lost The server closed the connection Right query to get the current number of connections in a PostgreSQL DB