PostgreSQL Connection Count Statistics: Accuracy and Performance Comparison Between pg_stat_database and pg_stat_activity

Core Mechanisms of Connection Counting

Accurately obtaining the number of active connections is a fundamental requirement in PostgreSQL database monitoring and performance tuning. The system provides multiple statistical views to support this functionality, with pg_stat_database and pg_stat_activity being the most commonly used ones.

Semantic Analysis of Original Queries

The two queries initially proposed by users are not semantically equivalent:

SELECT numbackends FROM pg_stat_database;
SELECT COUNT(*) FROM pg_stat_activity;

The first query returns a list of connection counts per database, while the second query directly returns the total connection count across all databases. This difference stems from the pg_stat_database view organizing statistical information at the database granularity, with each database corresponding to one row of records.

Implementation of Equivalent Queries

To achieve the same functionality as the COUNT(*) query, aggregation calculation on the numbackends field is required:

SELECT SUM(numbackends) FROM pg_stat_database;

This aggregation operation accumulates connection counts from individual databases, obtaining the total connection count for the entire PostgreSQL instance, functionally identical to the counting results from pg_stat_activity.

Theoretical Basis for Data Accuracy

Both methods are based on the same underlying statistical mechanism, with the PostgreSQL kernel maintaining a unified connection state tracking system. Both pg_stat_database.numbackends and the row count statistics of pg_stat_activity originate from this unified source data, therefore their statistical results possess inherent synchronization and consistency.

Microscopic Analysis of Performance Characteristics

From an execution efficiency perspective, the SUM(numbackends) query typically has a slight performance advantage:

• The number of rows in the pg_stat_database view equals the number of databases, which in typical deployments is much smaller than the number of connections
• pg_stat_activity requires counting records of all active connections, with the number of rows proportional to the current connection count
• Aggregation computation on a small number of rows incurs lower overhead than full-table counting on large tables

However, in actual production environments, this performance difference is typically at the microsecond level, with negligible impact on overall system performance.

Practical Recommendations and Application Scenarios

For routine monitoring requirements, both methods can provide accurate connection count information. The choice primarily depends on specific application scenarios:

• When analyzing connection distribution by database granularity is needed, use the pg_stat_database view
• When only concerned with total connection count, both methods are acceptable, with SUM(numbackends) being theoretically superior
• In high-frequency monitoring scenarios, the SUM aggregation solution can be prioritized

Regardless of the chosen method, it's important to note the update delay characteristics of statistical views - these data are not updated in real-time but are based on periodic sampling by the background statistics collector.