Monitoring Active Queries: Using pg_stat_activity for Performance Tuning

When your database suddenly slows down, you need to know what PostgreSQL is doing right now. pg_stat_activity shows active queries, idle sessions, lock waits, and open transactions so you can separate a slow query from a blocked one.

Use it during incidents, but also keep a few saved queries around for routine checks. The examples below focus on PostgreSQL systems where you have permission to read activity for the sessions you need to inspect.

Understanding the pg_stat_activity View

pg_stat_activity is a dynamic system view with one row for each server process connected to the database cluster. That includes client backends, background workers, and sessions that are idle but still connected.

Monitoring this view allows you to see exactly what the database is doing right now, making it invaluable for debugging sudden performance dips or diagnosing contention issues that are too transient for typical log files to capture effectively.

Key Columns for Performance Analysis

While pg_stat_activity contains dozens of columns, the following are essential when diagnosing performance issues:

Column Name	Description	Relevance for Tuning
pid	Process ID of the backend.	Required for cancelling or terminating sessions.
datname	Name of the database this backend is connected to.	Helps scope monitoring in multi-database environments.
usename	User who initiated the connection.	Identifies specific application or user activity.
application_name	Name of the application connecting (if set by the client).	Excellent for identifying connections from specific microservices.
state	Current activity status (e.g., active, idle, idle in transaction).	Core indicator of what the backend is doing.
query	The current query, or the last query for idle sessions. Visibility can be limited by privileges and settings.	Identifies the SQL statement involved.
query_start	Timestamp when the current query execution began.	Used to calculate query duration.
wait_event_type & wait_event	Details on what the process is waiting for (e.g., lock acquisition, I/O).	Critical for diagnosing contention and blocking.

Practical Monitoring Use Cases

The real power of pg_stat_activity comes from filtering the data to answer specific performance questions.

View All Active Queries

To see only the processes currently running a statement (not idle), filter the view by the state column.

-- View all currently executing queries
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

Identify Long-Running Queries

Identifying queries that have been running longer than expected is often the first step in performance tuning. These queries can consume resources, cause I/O spikes, or hold locks.

To identify queries running for more than a specific threshold (e.g., 5 seconds), use interval subtraction with now() and query_start.

-- Find queries running longer than 5 seconds
SELECT
    pid,
    usename,
    datname,
    state,
    (now() - query_start) AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > interval '5 seconds'
ORDER BY duration DESC;

Use a threshold that fits your workload. A five-second checkout query may be serious in an OLTP app, while a five-minute reporting query may be normal if it runs off-hours.

Diagnose Idle in Transaction Sessions

A connection that is idle in transaction has started a transaction but has not committed or rolled back. It is waiting for the client to send the next command. These sessions can hold locks and keep old row versions visible, which can delay cleanup by autovacuum and contribute to table bloat.

-- Find sessions that are idle but holding an open transaction
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    now() - xact_start AS txn_duration,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY txn_duration DESC;

If you find sessions holding transactions open for minutes or hours, check the application path that opened the transaction. Common causes include missing rollback handling after an exception, a connection returned to a pool before cleanup, or an interactive admin session left open.

Analyze Lock Contention and Blocking

When a query hangs, it is often waiting for a lock held by another process. The pg_stat_activity view, combined with pg_locks, is crucial for diagnosing contention.

To find sessions that are currently waiting for a resource (a lock, I/O, etc.), look at the wait_event column. If a session is blocked, its wait_event_type will often be Lock.

-- Identify processes currently blocked by a lock
SELECT
    a.pid,
    a.usename,
    a.query_start,
    a.query,
    a.wait_event,
    a.wait_event_type
FROM pg_stat_activity a
WHERE a.state = 'active'
  AND a.wait_event IS NOT NULL
ORDER BY a.query_start;

For a quick "who is blocking whom" view, PostgreSQL also provides pg_blocking_pids().

-- Show blocked sessions and the sessions blocking them
SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

If this returns a blocked web request waiting on a long admin update, canceling the admin query may be safer than terminating the application session.

Managing Problematic Sessions

Once a problematic query or session is identified using its Process ID (pid), PostgreSQL provides two functions to manage it:

Cancel a Query with pg_cancel_backend

This function attempts to gracefully stop the execution of a specific query. The session itself remains connected and available for future queries.

-- Example: Cancel the query running on PID 12345
SELECT pg_cancel_backend(12345);

Terminate a Session with pg_terminate_backend

This function forcefully disconnects the backend process from the server. If the session was in the middle of a transaction, PostgreSQL will automatically roll back the transaction.

-- Example: Forcefully terminate the session with PID 54321
SELECT pg_terminate_backend(54321);

Try pg_cancel_backend first when the session is simply running a bad query. Use pg_terminate_backend for sessions that are stuck, abandoned, or holding an open transaction that cannot be cleaned up normally. Rolling back a large transaction can take time and add I/O load, so do it deliberately.

Best Practices for Monitoring

Filter Aggressively

Avoid SELECT * FROM pg_stat_activity as your default production habit. The output is noisy, and the query text may expose sensitive values if your applications send literals instead of bind parameters. Select the columns you need and filter by state, datname, application_name, or duration.

Use Tools for Automated Monitoring

Manual checks are useful during an incident, but trends belong in monitoring. Track active sessions, waiting sessions, long transactions, and idle in transaction counts in your PostgreSQL dashboard.

Configure Statement Logging

Combine real-time monitoring with historical data. Configure parameters like log_min_duration_statement to log queries that exceed a certain threshold, providing data for analysis even after the query finishes executing.

Takeaway

Keep three saved checks: active queries ordered by duration, idle transactions ordered by transaction age, and blocked sessions with their blockers. When PostgreSQL feels slow, those views tell you whether to tune SQL, fix transaction handling, or clear a blocking session.