pg_stat_activity

pg_stat_activity is one of PostgreSQL's most important system views (in the pg_catalog schema). It provides a real-time snapshot of all server processes (backends) and their current activity on the database instance.

Each row represents one backend process (typically one client connection/session, plus some internal processes).

What It Gives You (Key Columns)

Here are the most useful columns (varies slightly by PostgreSQL version; check your version with \d+ pg_stat_activity):

Common Use Cases

• Identify blocking/locked sessions (combine with pg_locks): Look for sessions with non-null wait_event related to locks.

Filter out your own session or background processes:SQL

WHERE pid <> pg_backend_pid() 
  AND backend_type = 'client backend';

Kill problematic sessions:SQL

SELECT pg_terminate_backend(pid);

Find long-running or idle-in-transaction queries (common cause of bloat/locking):SQL

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

See what’s running right now:SQL

SELECT pid, usename, datname, state, query, query_start 
FROM pg_stat_activity 
ORDER BY query_start;

Important Notes

• Requires appropriate privileges (usually superuser or members of pg_read_all_settings).
• It’s dynamic — reflects the exact moment you query it.
• Does not show historical data or query performance stats (use pg_stat_statements for that).
• In high-connection environments, pair it with a connection pooler like PgBouncer.

State in more detail

The state column in pg_stat_activity tells you the current overall state of each backend process (connection/session).

It is one of the most useful fields for quick diagnostics.

Possible Values for state

Why It Matters

• High number of "idle in transaction" → Look for connection leaks, unclosed transactions in application code, or missing COMMIT/ROLLBACK.
• Many long "active" → Slow queries, resource contention, or missing indexes.
• Lots of "idle" → Normal, but if connection count is very high, consider a connection pooler (PgBouncer).

Useful Queries

SQL

-- Group by state to see the big picture
SELECT state, COUNT(*) 
FROM pg_stat_activity 
GROUP BY state;

-- Long idle in transaction
SELECT pid, usename, datname, query_start, age(now(), query_start) AS idle_time
FROM pg_stat_activity 
WHERE state = 'idle in transaction'
ORDER BY idle_time DESC;

-- Currently running queries
SELECT pid, usename, query, age(now(), query_start) 
FROM pg_stat_activity 
WHERE state = 'active';

Idle in transaction (aborted)

When you run BEGIN (or start a transaction), and then an error occurs inside that transaction, PostgreSQL marks the transaction as aborted.

At this point:

• The transaction can no longer execute normal statements (most will fail with "current transaction is aborted").
• The only allowed commands are ROLLBACK (or COMMIT, but it will act as rollback).
• The session stays in idle in transaction (aborted) state until the client/application issues a ROLLBACK.

How It Gets into This State

Typical flow:

1. Client runs: BEGIN;
2. Client runs some statements.
3. An error happens (e.g. unique violation, foreign key violation, division by zero, syntax error, constraint violation, etc.).
4. PostgreSQL aborts the transaction.
5. If your application code does not catch the error and call ROLLBACK, the connection stays stuck in the aborted state.

Why It Stays Aborted

PostgreSQL is designed this way for safety:

• Once a transaction fails, it refuses to let you continue with more changes (to prevent partial/inconsistent data).
• It forces you to clean up with ROLLBACK before starting new work on that connection.

This is different from a normal idle in transaction, which occurs when you're inside a clean BEGIN ... with no errors yet.

Common Causes

• Application doesn't handle database errors properly (no try/catch or equivalent around transactions).
• Using connection pools without proper error handling or transaction reset.
• ORM/framework that doesn't automatically rollback on exceptions.
• Manual testing where you run a failing query and forget to rollback.

How to Fix It

From inside the same session:

SQL

ROLLBACK;

From another session (as admin), you can kill the stuck backend:

SQL

SELECT pg_terminate_backend(pid);

(Find the pid from pg_stat_activity).

Best Practices to Avoid It

• Always handle errors and explicitly ROLLBACK on failure.
• Use SAVEPOINTs for partial rollback if needed.
• In application code, use proper transaction blocks with try/finally.
• With connection pools (PgBouncer, etc.), enable transaction mode or ensure the pool resets sessions after errors.

Monitor with this query:SQL

SELECT pid, usename, datname, query, age(clock_timestamp(), state_change) AS time_in_state
FROM pg_stat_activity 
WHERE state = 'idle in transaction (aborted)';

If any one of your connections enters the idle in transaction (aborted) state, that specific connection becomes mostly useless until it is cleaned up.

What Happens to That Connection

• It can no longer execute normal SELECT, INSERT, UPDATE, etc. statements.
• The only thing it can do is ROLLBACK (or in some cases COMMIT, which acts like rollback).
• It continues to consume a connection slot from your backend / connection pool.

Almost every command will fail with an error like:

current transaction is aborted, commands ignored until end of transaction block

The other 9 connections continue working normally.

Real-World Impact (with 10 connections)

• Mild case: One bad connection → you lose ~10% of your DB capacity.
• If several go aborted: You can quickly run out of usable connections → application slowdown, timeouts, or errors for users.
• In the worst case (especially with a connection pool of limited size), aborted connections can slowly “leak” and exhaust the entire pool.

This is a very common cause of mysterious performance degradation or “database is slow but CPU is fine” issues.

How to Handle It

1. Auto-cleanup (recommended):
   • Fix your application to always ROLLBACK on error.
   • Use a connection pool (PgBouncer or built-in pool in your framework) with proper session/transaction reset on error.
   • You can even set up a background job or use extensions to kill long-aborted sessions automatically.

Immediate fix when you see them:SQL

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE state = 'idle in transaction (aborted)';

Detect them quickly:SQL

SELECT 
    pid,
    usename,
    datname,
    application_name,
    age(clock_timestamp(), state_change) AS time_aborted,
    query
FROM pg_stat_activity 
WHERE state = 'idle in transaction (aborted)';