pg_stat_activity is basically a real-time window that helps you see what's happening in your database right now. In the previous lecture, we covered the basics, now let's dive deeper into working with this powerful tool.
Example of a basic query to pg_stat_activity:
SELECT *
FROM pg_stat_activity;
This query will show all active connections and current queries. Awesome! But that's a ton of data, and you could spend forever scrolling through it. So it's super helpful to filter out just the most important info.
Main Fields in pg_stat_activity
Let's check out the key fields you'll need in addition to the ones you already know. query_start shows when the query started running, which is crucial for spotting long-running operations. pid holds the connection process ID — you need this to manage (like kill) a connection. state_change shows when the current connection state was set, which is especially useful for analyzing long-lived problematic states.
Example of selecting active processes:
SELECT pid, usename, state, query, query_start
FROM pg_stat_activity
WHERE state = 'active';
How to Track Long-Running Queries?
Imagine you're the database admin and suddenly the server load goes through the roof. What do you do? First, you gotta figure out which query is hogging all the resources. Let's use pg_stat_activity to find those "greedy" queries.
SELECT pid, usename, query, state, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
AND (now() - query_start) > interval '10 seconds';
This query will show all queries running for more than 10 seconds. Adjust the interval value to fit your needs.
Killing Problematic Queries
Let's break down how to get rid of queries that have been running way too long and are messing with your database. Use the pg_terminate_backend() function to forcefully kill a process.
Example of killing a process with a specific PID:
SELECT pg_terminate_backend(12345);
Where 12345 is the process ID (pid field) from pg_stat_activity.
Important: Killing a process can cause a rollback for an improperly finished transaction, so be careful.
Now, if you want to automatically kill all "stuck" processes, like idle transactions, you can run the following PL/pgSQL block. Since you've already learned programming, the concept of a loop is familiar to you — it's a construct that repeats certain instructions as long as a condition is met or until a data set is processed:
DO $$
DECLARE
r RECORD;
BEGIN
FOR r IN
SELECT pid
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND (now() - state_change) > interval '5 minutes'
LOOP
PERFORM pg_terminate_backend(r.pid);
END LOOP;
END $$;
This dynamic solution lets you clean up problematic transactions from the system. The FOR loop goes through each record from the query result and kills the process for each found PID.
We'll get to PL/pgSQL soon, hang tight, we're almost there :P
Filtering by Transaction State
Sometimes you don't just want to find an active query, but also see which connections are in a special state, like idle or idle in transaction. This can help you spot potential issues before they get critical.
Example query to find transactions in idle in transaction state:
SELECT pid, usename, query, state, state_change
FROM pg_stat_activity
WHERE state = 'idle in transaction';
The state_change field shows when this state was set. So you can find long-lived transactions that aren't doing anything useful but might be blocking database resources.
Practical Use Cases
Monitoring Long Queries in Production: you can set up regular monitoring for queries that go over a certain time threshold and send alerts via Slack, Telegram, or any other notification tool. This lets you react quickly to performance issues.
Query Analysis During Incidents: if your server starts lagging, the first thing you should check is pg_stat_activity to find the cause. This should be your go-to protocol for handling performance problems.
Database Maintenance: regularly analyzing pg_stat_activity helps you track inefficient queries and optimize them (like adding indexes or rewriting queries).
When it comes to monitoring, mistakes can happen because of bad filtering or misinterpreting the data. For example, if you filter by active state, you might miss queries that are in idle in transaction state, and those can also block resources. Another mistake is killing processes too aggressively, which can cause unwanted transaction rollbacks and data loss. Always analyze the context before taking drastic action.
Extra Monitoring Techniques
For more advanced monitoring, you can create complex queries that show stats by user, database, or query type. For example, you can track how much time each user spends running queries on average, or find databases with the most active connections.
It's also useful to set up automatic logging of long queries to PostgreSQL log files using the log_min_duration_statement and log_statement config parameters. This helps you analyze performance issues after the fact and spot patterns in app behavior.
GO TO FULL VERSION