Playing with pg_stat_activity

The pg_stat_activity view in PostgreSQL provides real-time information about the currently running queries and sessions in the database. It's a useful tool for monitoring and troubleshooting performance issues, as it allows you to see what queries are being executed, who is executing them, and other session-related information.

Here are some ways to "play" with pg_stat_activity by running queries that give you insights into database activity:

1. List All Active Sessions

This query will show all active sessions (including idle sessions) in the PostgreSQL database.

SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    client_hostname,
    client_port,
    backend_start,
    state,
    query,
    query_start
FROM 
    pg_stat_activity
ORDER BY 
    backend_start DESC;

pid: Process ID of the session.
usename: The username of the connected client.
application_name: The name of the application connected (if specified).
client_addr: The IP address of the client.
client_hostname: The hostname of the client (if available).
client_port: The port number of the client.
backend_start: The time when the backend session started.
state: Current state of the session (e.g., active, idle, idle in transaction).
query: The SQL query that is currently being executed.
query_start: The time when the query started.

2. Find Long-Running Queries

To monitor long-running queries that might be affecting performance, you can look for queries that have been running for a long time.

SELECT 
    pid,
    usename,
    application_name,
    state,
    query,
    query_start,
    now() - query_start AS duration
FROM 
    pg_stat_activity
WHERE 
    state = 'active' 
    AND now() - query_start > interval '5 minutes'
ORDER BY 
    duration DESC;

This query will return all queries that have been running for more than 5 minutes.

duration: How long the query has been running.

You can adjust the interval '5 minutes' to any other duration you want to monitor.

3. Check for Idle Connections

You can also check for idle connections that are consuming resources but not actively running queries. This could indicate potential connection leaks or idle transactions.

SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    backend_start,
    xact_start,
    query
FROM 
    pg_stat_activity
WHERE 
    state = 'idle';

xact_start: The start time of the transaction. This helps you identify if there are long-running transactions in an idle state.

4. Find Blocking Sessions

Sometimes, queries are blocked by other queries. You can find blocking sessions with this query.

SELECT 
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocking.query AS blocking_query,
    blocking.state AS blocking_state,
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.query AS blocked_query,
    blocked.state AS blocked_state
FROM 
    pg_stat_activity blocked
JOIN 
    pg_stat_activity blocking
ON 
    blocked.blocking_pid = blocking.pid
WHERE 
    blocked.state = 'active';

blocking_pid: The PID of the session that is blocking other queries.
blocked_pid: The PID of the session being blocked.

5. Show Queries by Specific User

If you want to see the queries being run by a specific user, you can filter the pg_stat_activity by usename:

SELECT 
    pid,
    usename,
    application_name,
    state,
    query,
    query_start
FROM 
    pg_stat_activity
WHERE 
    usename = 'your_username'
ORDER BY 
    query_start DESC;

Replace 'your_username' with the actual username you're interested in. This query will show the active queries for that user.

6. Show Queries by Application

If you want to see queries run by a particular application (e.g., a specific web app), filter by application_name:

SELECT 
    pid,
    usename,
    application_name,
    state,
    query,
    query_start
FROM 
    pg_stat_activity
WHERE 
    application_name = 'your_application_name'
ORDER BY 
    query_start DESC;

Replace 'your_application_name' with the name of your application.

7. List All Active Connections

To get a simple list of all active connections, you can run:

SELECT 
    pid,
    usename,
    client_addr,
    state
FROM 
    pg_stat_activity
WHERE 
    state = 'active';

This query will give you an overview of the active connections to the database.

8. Terminate a Query or Session

If you find a query that is taking too long or causing issues, you can terminate that query or session using the pg_terminate_backend() function. Be cautious with this, as it will forcibly terminate the session.

SELECT pg_terminate_backend(<pid>);

Replace <pid> with the process ID of the session or query you want to terminate. You can find this in the pg_stat_activity table.

9. List Active Queries in a Specific Database

To see which queries are running in a specific database, you can filter by datname:

SELECT 
    pid,
    usename,
    application_name,
    state,
    query,
    query_start
FROM 
    pg_stat_activity
WHERE 
    datname = 'your_database_name'
ORDER BY 
    query_start DESC;

Replace 'your_database_name' with the name of the database you're interested in.

10. Show Waiting Queries

In PostgreSQL, a query can be in a "waiting" state if it is blocked by another query. You can use the following query to identify waiting queries:

SELECT 
    pid,
    usename,
    application_name,
    state,
    query,
    query_start,
    wait_event,
    wait_event_type
FROM 
    pg_stat_activity
WHERE 
    state = 'waiting';

wait_event: The specific event the query is waiting for (e.g., Lock).
wait_event_type: The type of wait event (e.g., Lock, IO, etc.).

Conclusion

By using pg_stat_activity, you can monitor and troubleshoot PostgreSQL activity in real-time. You can identify long-running queries, idle sessions, blocked queries, and much more. These insights can help you diagnose performance issues, optimize query execution, and ensure efficient resource usage in your PostgreSQL database.