获取PostgreSQL数据库中当前连接数的正确查询[英] Right query to get the current number of connections in a PostgreSQL DB

本文是小编为大家收集整理的关于获取PostgreSQL数据库中当前连接数的正确查询的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

以下两个更准确?

select numbackends from pg_stat_database;

select count(*) from pg_stat_activity;

推荐答案

这两个要求并不等效.第一个版本的等效版本是:

SELECT sum(numbackends) FROM pg_stat_database;

在这种情况下,我希望该版本比第二个版本要快一些,仅仅是因为它的数量较少.但是您不太可能能够衡量差异.

两个查询都是基于完全相同的数据,因此它们将同样准确.

其他推荐答案

以下查询非常有用

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;

其他推荐答案

他们肯定会带来不同的结果.更好的是

select count(*) from pg_stat_activity;

这是因为它包括与Wal Sender流程的连接,这些连接被视为常规连接并计算max_connections.

参见 max_wal_senders

本文地址:https://www.itbaoku.cn/post/597417.html

问题描述

Which of the following two is more accurate?

select numbackends from pg_stat_database;

select count(*) from pg_stat_activity;

推荐答案

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.

其他推荐答案

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