Comprehensive Guide to PostgreSQL pg_stat_activity View

Understanding PostgreSQL pg_stat_activity view

The pg_stat_activity view in PostgreSQL provides detailed insights into the current database connections and their activities. It is an essential tool for monitoring and diagnosing database performance and connection issues.

What is pg_stat_activity?

pg_stat_activity is a system view in PostgreSQL that contains one row per server process (connection). It provides information such as the user, query, backend state, and the duration of the current query or transaction.

Syntax:

To query the pg_stat_activity view, use the following syntax:

SELECT * FROM pg_stat_activity;

Example: Monitoring Active Connections

Code:

-- Select specific columns for better readability
SELECT 
    datname AS database_name,    -- Name of the database
    usename AS user_name,        -- Name of the connected user
    client_addr AS client_address, -- IP address of the client
    state,                       -- Current state of the connection
    query                        -- Query being executed
FROM pg_stat_activity;

Explanation:

Practical Scenarios

1. Identifying Long-Running Queries

Code:

-- Find queries running for more than 5 minutes
SELECT 
    pid,                              -- Process ID
    now() - query_start AS duration, -- Query duration
    state, 
    query 
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '5 minutes';

2. Terminating Problematic Queries

If a query is causing issues or consuming excessive resources, you can terminate it:

Code:

-- Terminate a specific query by PID
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = 12345; -- Replace 12345 with the actual process ID

3. Monitoring Idle Connections

Idle connections can consume resources unnecessarily. Use the following query to identify them:

Code:

-- List all idle connections
SELECT 
    pid, 
    usename, 
    client_addr, 
    state, 
    query 
FROM pg_stat_activity
WHERE state = 'idle';

Advanced Filters

a) Filter by Database:

Code:

SELECT * FROM pg_stat_activity WHERE datname = 'your_database_name';

b) Filter by User:

Code:

SELECT * FROM pg_stat_activity WHERE usename = 'your_user_name';

Explanation:

1. Backend States:

The state column provides the status of each connection:

• active: The connection is running a query.
• idle: The connection is waiting for the next query.
• idle in transaction: The connection is in a transaction but waiting for the next query.
• waiting: The connection is waiting for a lock.
• disabled: The connection is disabled.

2. System Use:

pg_stat_activity is read-only and does not impact database performance when queried, making it a safe tool for monitoring.

3. Real-Time Monitoring:

Use tools like psql to run queries in real-time or integrate them into monitoring dashboards (e.g., Grafana).

Best Practices

• Regularly monitor pg_stat_activity to ensure optimal connection usage.
• Set connection timeouts and manage idle connections to avoid resource exhaustion.
• Use logging and automation for long-running query alerts.

All PostgreSQL Questions, Answers, and Code Snippets Collection.