Monitor PostgreSQL Active Sessions with pg_stat_activity

Monitoring PostgreSQL Active Sessions with pg_stat_activity Explained

As databases scale, monitoring active connections and queries in PostgreSQL becomes essential for performance tuning, troubleshooting, and ensuring stability. PostgreSQL provides a powerful system view called pg_stat_activity that contains detailed information about all current backend processes.

The following query allows database administrators to inspect active sessions:

 1SELECT
 2    pid,
 3    usename,
 4    client_addr,
 5    client_hostname,
 6    client_port,
 7    backend_start,
 8    query_start,
 9    state,
10    -- not available on PostgreSQL < 10
11    backend_type
12FROM
13    pg_stat_activity
14ORDER BY
15    -- not available on PostgreSQL < 10
16    backend_type;

Purpose of the Query

This query helps you:

• Monitor active PostgreSQL connections and their originating clients.
• Track when sessions started and how long queries have been running.
• Diagnose issues like long-running queries or idle connections consuming resources.
• Identify the backend type (such as client backend or autovacuum worker), useful for categorizing processes.

Breakdown of Each Column

• pid: Process ID of the backend process. Useful for terminating problematic queries with pg_terminate_backend(pid).
• usename: The PostgreSQL user running the query.
• client_addr: The IP address of the client application connected to the database.
• client_hostname: Hostname of the client, if available. Helpful for tracking connections from multiple servers.
• client_port: Port number from which the client connected.
• backend_start: Timestamp when the session started. Long-lived sessions could indicate connection pool misconfigurations.
• query_start: Timestamp when the query execution began. Helps in identifying long-running queries that may need attention.
• state: Current activity state. Common examples include active, idle, idle in transaction, or waiting.
• backend_type: (PostgreSQL 10+) Identifies the type of process, such as client backend, autovacuum worker, or logical replication worker.

Key Insights & Practical Uses

• Performance tuning: Quickly locate long-running queries hogging system resources.
• Connection analysis: Spot unusual connections from unexpected IPs.
• Security insight: Detect unauthorized or idle sessions that may indicate misuse.
• Maintenance aid: Identify background workers and monitor autovacuum activity.
• Operational clarity: Sort results by backend_type to distinguish between client traffic and system tasks.

Best Practices

1. Regular monitoring: Integrate this query into your monitoring dashboards (e.g., Grafana, pgAdmin, or custom scripts).
2. Kill only if necessary: Use pg_terminate_backend(pid) instead of abruptly killing processes at the OS level.
3. Combine with logging: Cross-reference query start times with PostgreSQL logs to correlate performance spikes.
4. Connection pooling: If you see many idle connections, review your connection pooling strategy (e.g., use PgBouncer).

Conclusion

The pg_stat_activity view is one of the most powerful tools at a PostgreSQL administrator’s disposal. By running this query, you gain deep visibility into client connections, query activity, and background processes. With correct interpretation, this insight helps you optimize performance, improve reliability, and troubleshoot effectively.

References

• PostgreSQL Documentation: pg_stat_activity – Official PostgreSQL documentation explaining all columns in this view.
• Managing Connections & Performance – PostgreSQL runtime configuration for managing connections.