Alright, let’s pull back the curtain. You want to know what’s happening right now inside your Postgres database. Not what happened five minutes ago, not some aggregated average—the raw, unvarnished truth of the current moment. For that, you go to pg_stat_activity. Think of it as the database’s live activity monitor, the one place you can see every single connection and what it’s up to, whether it’s heroically saving your data or just sitting there idly, burning a hole in your connection limit.

This view is your first, last, and best resort when things feel slow, when connections are maxed out, or when you just need to know who’s kicking off that massive UPDATE at 3 PM on a Friday. It’s deceptively simple but incredibly powerful if you know how to interrogate it properly.

The Core Columns: Your Essential Toolkit

Don’t just do a SELECT * and get overwhelmed. Focus on the columns that actually tell the story. Here’s the core cast of characters:

SELECT
    pid,                -- The OS process ID. Your handle for everything.
    usename,            -- Who? (The user)
    client_addr,        -- Where from? (Client IP, NULL for internal)
    application_name,   -- What app? (Often set by the client, hilariously misreported)
    state,              -- What's it doing? (active, idle, idle in transaction, etc.)
    backend_type,       -- Is it a client backend, autovacuum, or a background worker?
    query,              -- The actual SQL query. The main event.
    query_start,        -- When did it start?
    xact_start          -- When did its transaction start?
FROM pg_stat_activity;

The state column is your best friend. An active state means the backend is right now executing that query. An idle state means it’s connected but waiting for a new command. The one that’ll bite you is idle in transaction. This is a client that started a transaction (BEGIN), did something, and then just… left it open. It’s holding onto locks and vacuum cleanup is waiting for it. It’s the equivalent of leaving your shopping cart in the middle of the grocery aisle. Nasty.

The Query Column: A Word of Caution

See that query column? It shows the last command sent by this client. There’s a massive, hilarious, and often infuriating caveat: for long-running queries, it shows the entire query text, even if it’s a novel. This can make the view bloated and slow to query itself. To see a preview instead, use substring():

SELECT pid, usename, state, substring(query, 1, 40) AS preview
FROM pg_stat_activity
WHERE state = 'active';

Finding the Troublemakers: Useful Queries

You won’t remember all the columns, so save these. This is how you separate the signal from the noise.

To find long-running active queries (the usual suspects for performance issues):

SELECT pid, usename, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
AND query_start IS NOT NULL
ORDER BY duration DESC;

To find those dreaded idle-in-transaction connections:

SELECT pid, usename, now() - xact_start AS xact_duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_duration DESC;

If you see one of these with a duration measured in hours, you’ve found your problem. Go yell at the application team whose code doesn’t properly close its transactions.

To see who’s connected and from where:

SELECT client_addr, usename, count(*)
FROM pg_stat_activity
GROUP BY client_addr, usename
ORDER BY count DESC;

This is great for spotting a single client machine that’s decided to open 500 connections instead of using a connection pooler. It happens more than you’d think.

Terminating Connections (The Big Red Button)

Seeing a problem is only half the job. Sometimes you need to be the problem for that problem. You need to terminate a query or an entire connection. Use these powers with extreme prejudice.

To cancel a running query (SIGINT):

SELECT pg_cancel_backend(pid);

This is relatively polite. It asks the query to stop. The client might get an error and can choose to retry.

To terminate the entire connection (SIGTERM):

SELECT pg_terminate_backend(pid);

This is the hard stop. It drops the connection immediately, rolling back any open transaction. It’s the equivalent of pulling the plug. Use this when a query is hopelessly stuck or when you find those idle in transaction connections that have been there since last Tuesday.

A crucial best practice: never, ever terminate a backend without checking what it is first. The pg_stat_activity view includes its own PID. If you terminate the connection that’s running the termination query, you’ll end up in a confusing situation. Always filter yourself out:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND pid <> pg_backend_pid(); -- This is the magic line

A Note on Backend Types

Modern Postgres has more than just client backends. The backend_type column is crucial for this. You might see autovacuum worker, logical replication launcher, or background worker. Don’t go around terminating these on a whim. An autovacuum worker might be holding locks, but it’s usually there for a very good reason. If you kill it, it’ll just come back. Identify the type before you decide to escalate.

pg_stat_activity is the foundational view. It tells you the what. For the why, you’ll often need to join it with other views, like pg_locks to see what’s being held. But that’s a story for the next section. For now, just get comfortable here. It’s the control room.