Comprehensive guide to PostgreSQL session metrics. Monitor connection usage, session distribution, active/idle connections, and user application patterns for optimal database performance and security.
Total Connection Usage For All Databases
What it measures: The percentage of active connections relative to the maximum allowed connections (max_connections) in a PostgreSQL instance.
Why it matters: High connection usage indicates risk of connection starvation, degraded performance, or denial of new connections. This metric helps prevent resource exhaustion and ensures your database can handle peak loads without service interruption.
Ideal value & Best Practice: Maintain connection usage below 80% of max_connections. Implement connection pooling (like PgBouncer) for applications with high connection churn. Regularly review and adjust max_connections based on actual usage patterns and available resources.
Session Distribution State For PostgreSQL Instance
What it measures: The number of PostgreSQL connections currently executing queries (state = active) versus other connection states.
Why it matters: Excessive active connections can indicate query contention, long-running transactions, or inadequate resources (CPU/RAM). Proper management ensures optimal performance and avoids resource exhaustion that could lead to database instability.
Ideal value & Best Practice: Balance active connections based on your server's CPU cores and workload. Implement query timeouts and monitor for long-running transactions. Use connection pooling to efficiently manage application connections.
Sessions Per Users For PostgreSQL Instance
What it measures: The distribution of database sessions across different user accounts.
Why it matters: Understanding user session patterns helps identify unusual activity, potential security issues, or users consuming disproportionate resources. This is crucial for access management and capacity planning.
Ideal value & Best Practice: Establish baseline patterns for normal user behavior. Investigate significant deviations from typical session counts per user. Implement role-based access control and regularly audit user permissions.
Session Per Application For PostgreSQL Instance
What it measures: The distribution of database connections across different client applications.
Why it matters: Identifies which applications are consuming database resources, helping to pinpoint problematic applications or optimize connection management for specific use cases.
Ideal value & Best Practice: Monitor for applications creating excessive connections. Implement application-specific connection policies and consider using different database users for different applications to improve monitoring and control.
Session Per Database For PostgreSQL Instance
What it measures: The distribution of connections across different databases within the PostgreSQL instance.
Why it matters: Helps identify databases with unusually high connection demands, which may indicate design issues, inefficient application logic, or the need for database partitioning or separation.
Ideal value & Best Practice: Balance connections according to database importance and workload requirements. Consider separating heavily used databases to dedicated instances if connection patterns significantly differ.
Distribution of Non-Idle Connections Per Database and Per Query
What it measures: The pattern of active connections categorized by database and query type.
Why it matters: Provides granular insight into which databases and specific queries are consuming active connection resources, helping to identify optimization opportunities and potential bottlenecks.
Ideal value & Best Practice: Identify and optimize queries that maintain connections for extended periods. Implement query timeout policies and monitor for queries that might be holding connections unnecessarily.
Active Connections Sharing The Same Host, Username, and Database
What it measures: Identifies clients with the highest number of active connections using the same host, username, and database combination.
Why it matters: Excessive active connections from a single client may indicate misconfigured connection pools, runaway processes, or inefficient query patterns that could degrade overall database performance.
Ideal value & Best Practice: Implement reasonable per-client connection limits. Review application connection management strategies and ensure proper connection pool configuration to prevent connection storms.
Idle Connections Sharing The Same Host, Username, and Database
What it measures: The number of PostgreSQL connections that are connected but not actively executing queries (state = idle) from similar sources.
Why it matters: A high number of idle connections may indicate resource waste (memory, connection slots) or application-side connection leaks. This reflects the efficiency of connection pool management and application resource cleanup.
Ideal value & Best Practice: Minimize idle connections through proper connection pool configuration. Implement application-side connection cleanup and consider using tools like PgBouncer to manage idle connection timeouts.
Same User Usage With Different Applications
What it measures: Identifies if a single database user is connected from multiple applications, exceeding a threshold of distinct applications.
Why it matters: High values may indicate security risks (overprivileged users), misconfigured connection pools, or lack of application-specific user segmentation that could complicate access control and auditing.
Ideal value & Best Practice: Implement application-specific database users where possible. Regularly review user access patterns and enforce the principle of least privilege. Monitor for unusual cross-application user activity that might indicate security issues.
Try pghealth Free Today ๐
Start your journey toward a healthier PostgreSQL with pghealth.
You can explore all features immediately with a free trial โ no installation required.
