SQL 正确查询以获取 PostgreSQL 数据库中的当前连接数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5267715/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Right query to get the current number of connections in a PostgreSQL DB
提问by Murali VP
Which of the following two is more accurate?
下面两个哪个更准确？
select numbackends from pg_stat_database;
select count(*) from pg_stat_activity;
回答by Magnus Hagander
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.
这两个查询都基于完全相同的数据，因此它们将同样准确。
回答by tbo
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;
回答by gargii
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.
这是因为它包括与 WAL 发送方进程的连接，这些连接被视为常规连接并计入max_connections.
See max_wal_senders
回答by ?obo
Aggregation of all postgres sessions per their status (how many are idle, how many doing something...)
根据状态汇总所有 postgres 会话（有多少空闲，有多少在做某事......）
select state, count(*) from pg_stat_activity where pid <> pg_backend_pid() group by 1 order by 1;
回答by Sureshkumar Pachamuthu
Number of TCP connections will help you. Remember that it is not for a particular database
TCP 连接数将帮助您。请记住，它不适用于特定的数据库
netstat -a -n | find /c "127.0.0.1:13306"
回答by Brian L
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.
从查看源代码来看，似乎 pg_stat_database 查询为您提供了所有用户到当前数据库的连接数。另一方面， pg_stat_activity 查询仅为查询用户提供与当前数据库的连接数。
