Monitoring and Managing Active Connections in PostgreSQL: Deep Dive into pg_stat_activity System View

Fundamentals of PostgreSQL Connection Monitoring

In PostgreSQL database administration, monitoring active connections is a crucial daily operational task. Understanding current connection states becomes essential when needing to drop databases or troubleshoot performance issues. PostgreSQL provides the powerful pg_stat_activity system view for real-time database connection monitoring.

Core System View: pg_stat_activity

The pg_stat_activity view contains detailed information about all active backend processes. By querying this view, you can obtain critical information such as process IDs, usernames, database names, client addresses, and more. The basic query statement is:

SELECT * FROM pg_stat_activity;

Detailed Connection Information Analysis

To better understand connection states, we can select specific columns for querying:

SELECT 
    pid AS process_id,
    usename AS username, 
    datname AS database_name,
    client_addr AS client_address,
    application_name,
    backend_start,
    state,
    state_change
FROM pg_stat_activity;

Column descriptions:

• process_id: Unique identifier for the backend process
• username: Username used for the connection
• database_name: Name of the connected database
• client_address: IP address of the client
• application_name: Name of the application
• backend_start: Connection establishment time
• state: Connection state (active, idle, idle in transaction, etc.)
• state_change: Last state change time

Connection State Classification and Management

Connection states reflect the current activity of backend processes:

• active: Currently executing a query
• idle: Idle state, waiting for new commands
• idle in transaction: Idle within a transaction
• idle in transaction (aborted): Idle within an aborted transaction

Connection Count Statistics and Analysis

Grouping statistics help understand connection distribution across databases:

SELECT datname, COUNT(datid) 
FROM pg_stat_activity 
GROUP BY datname;

This query helps identify databases with abnormal connection counts, providing basis for capacity planning.

Connection Pool Configuration Optimization

In practical applications, improper connection pool configuration may lead to connection exhaustion. For example, some connection pools default to creating 4 * num_cpus connections. Assuming a server with 24 CPU cores, this would create 96 connections, potentially approaching or exceeding the max_connections limit.

Query maximum connections configuration:

SELECT setting::int AS max_conn 
FROM pg_settings 
WHERE name = $$max_connections$$;

Available Connections Calculation

Calculate remaining available connections through complex queries:

SELECT 
    max_conn,
    used,
    res_for_super,
    max_conn - used - res_for_super AS res_for_normal
FROM
    (SELECT COUNT(*) AS used FROM pg_stat_activity) t1,
    (SELECT setting::int AS res_for_super FROM pg_settings WHERE name = $$superuser_reserved_connections$$) t2,
    (SELECT setting::int AS max_conn FROM pg_settings WHERE name = $$max_connections$$) t3;

Practical Application Scenarios

When unable to drop a database, query active connections for the specific database:

SELECT * 
FROM pg_stat_activity 
WHERE datname = $$your_database_name$$;

By identifying and terminating relevant connections, you can successfully perform the drop operation.

Graphical Management Tools

Besides command-line queries, graphical tools like pgAdmin can be used. Select the database in the Browser pane, then switch to the Dashboard tab, and view all connection sessions in the Server Activity panel.

Best Practice Recommendations

• Regularly monitor connection states to promptly identify anomalies
• Properly configure connection pool sizes to avoid connection exhaustion
• Reserve superuser connections for critical operations
• Establish connection monitoring alert mechanisms

By systematically mastering the usage of the pg_stat_activity view, database administrators can more effectively manage PostgreSQL connection resources, ensuring stable operation of the database system.