PostgreSQL Connection States: Understanding pg_stat_activity for Production Diagnosis

Your application starts throwing timeout errors at 2 AM. You connect to PostgreSQL and run SELECT count(*) FROM pg_stat_activity — 287 connections, nearly at your max_connections limit. You drill in and find 200 of them sitting in idle in transaction state, some for over 40 minutes. The application is not stuck — it has already moved on, but it forgot to close its transactions, and now those abandoned sessions are silently strangling your database. Understanding PostgreSQL connection states is not optional for production systems; it is the difference between diagnosing an incident in two minutes and guessing for two hours.

PostgreSQL's pg_stat_activity view gives you a real-time window into every backend process — what it is doing, how long it has been doing it, and whether it is blocking something else. But the states themselves — idle, active, idle in transaction, idle in transaction (aborted), fastpath function call, disabled — have precise meanings that affect how you interpret what you see and what action you take.

This guide covers every PostgreSQL connection state, the queries you need to diagnose problems in production, the dangers of idle in transaction accumulation, and the timeout and termination tools available to you.

PostgreSQL Connection States Explained

Every row in pg_stat_activity has a state column that reflects the current lifecycle phase of that backend process. There are six possible values, and each one tells a different story.

active

The backend is currently executing a query. This is the healthy, expected state — a connection in active state is doing real work. However, an active connection that has been running for minutes or hours is a candidate for investigation. Runaway queries, full table scans on growing tables, or queries blocked waiting for a lock will all appear as active. Always check wait_event_type alongside the state: an active connection with wait_event_type = 'Lock' is not executing — it is blocked.

idle

The backend has completed its last command and is waiting for a new query from the client. In a connection pool, idle connections represent pooled connections ready to be reused. A large number of idle connections is generally fine — they consume memory (roughly 5–10 MB each depending on work_mem settings) but hold no locks and do not block anything. If you see hundreds of idle connections without a connection pooler, you likely need PgBouncer.

idle in transaction

The backend has an open transaction (a BEGIN was issued) but is not currently executing a query. This is the most dangerous state. The transaction remains open, which means any row-level locks acquired during the transaction are still held, the transaction's snapshot is still pinned (blocking autovacuum from reclaiming dead tuples), and the transaction ID is still active (contributing to transaction ID wraparound pressure). An application that opens a transaction, runs a query, then gets distracted — waiting on an API call, sleeping between steps, or crashing without cleanup — will leave connections in this state.

idle in transaction (aborted)

Like idle in transaction, but the transaction has encountered an error and PostgreSQL is waiting for the client to issue ROLLBACK. Any subsequent commands in this session will return an error until the transaction is explicitly rolled back. This state often indicates application code that does not properly handle database errors — it catches the exception at the application layer but never cleans up the PostgreSQL transaction.

fastpath function call

The backend is executing a fast-path function — a low-overhead RPC mechanism used internally by the PostgreSQL client protocol for certain operations. You will rarely see this in production monitoring, but it is worth knowing it exists so it does not confuse your dashboards.

disabled

The track_activities parameter is disabled for this connection. You will see the state as disabled and most columns will be null. This is uncommon in practice — it is usually set globally in postgresql.conf rather than per session.

Querying pg_stat_activity Effectively

Raw SELECT * FROM pg_stat_activity returns too much noise. These are the queries that matter in production.

Connection state summary

Start every investigation with a state breakdown to understand what you are dealing with.

SELECT
  state,
  count(*) AS connection_count,
  max(now() - state_change) AS longest_in_state
FROM pg_stat_activity
WHERE pid != pg_backend_pid()
GROUP BY state
ORDER BY connection_count DESC;

All non-idle connections with query text

This query shows everything that is not simply waiting for work — the connections that are actively doing something or stuck in a problematic state.

SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  wait_event_type,
  wait_event,
  now() - state_change AS time_in_state,
  now() - query_start AS query_duration,
  left(query, 120) AS query_snippet
FROM pg_stat_activity
WHERE state != 'idle'
  AND pid != pg_backend_pid()
ORDER BY time_in_state DESC NULLS LAST;

Connections grouped by application and state

This is useful for identifying which application is responsible for a connection accumulation problem.

SELECT
  application_name,
  state,
  count(*) AS connections,
  max(now() - state_change) AS oldest_in_state
FROM pg_stat_activity
WHERE pid != pg_backend_pid()
GROUP BY application_name, state
ORDER BY application_name, connections DESC;

The "idle in transaction" Problem — Why It's Dangerous

The most common causes of idle in transaction accumulation:

• Application code that begins a transaction, performs an operation, then calls an external API or service before committing — the external call hangs or times out
• ORMs that wrap every operation in a transaction by default, combined with connection poolers in session mode that keep the underlying connection open
• Long-running batch jobs that open a transaction, process records in a loop, and hold the transaction open across the entire batch
• Application crashes or network interruptions that leave transactions open on the PostgreSQL side while the application has already moved on

Identifying Long-Running Transactions

Any connection that has been in idle in transaction for more than a few minutes deserves investigation. This query surfaces them along with how long the transaction has been open, not just how long the current state has persisted.

SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  now() - xact_start AS transaction_duration,
  now() - state_change AS time_in_current_state,
  left(query, 200) AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND xact_start IS NOT NULL
ORDER BY transaction_duration DESC;

The key distinction here is between xact_start and state_change. A connection might have changed to idle in transaction state one second ago (its last query just finished), but the transaction itself may have been open for 45 minutes. Always sort by xact_start when hunting for long-running transactions.

To find transactions older than a threshold — useful for alerting:

SELECT count(*) AS long_running_transactions
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND xact_start < now() - interval '5 minutes';

Detecting Blocked Queries and Lock Chains

Lock contention is often the direct consequence of idle in transaction accumulation. When one session holds a lock and others are waiting, you will see the waiting sessions in active state with wait_event_type = 'Lock'. Identifying the blocking chain requires joining pg_stat_activity with pg_locks.

Find blocked queries and their blockers

SELECT
  blocked.pid AS blocked_pid,
  blocked.usename AS blocked_user,
  blocked.application_name AS blocked_app,
  blocked.state AS blocked_state,
  now() - blocked.query_start AS blocked_duration,
  left(blocked.query, 100) AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.usename AS blocking_user,
  blocking.state AS blocking_state,
  now() - blocking.state_change AS blocking_state_duration,
  left(blocking.query, 100) AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_locks AS blocked_locks
  ON blocked.pid = blocked_locks.pid
JOIN pg_locks AS blocking_locks
  ON blocking_locks.granted = true
  AND blocked_locks.relation = blocking_locks.relation
  AND blocked_locks.locktype = blocking_locks.locktype
  AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity AS blocking
  ON blocking.pid = blocking_locks.pid
WHERE blocked_locks.granted = false
ORDER BY blocked_duration DESC;

Count waiting connections by lock type

SELECT
  wait_event,
  count(*) AS waiting_connections,
  max(now() - query_start) AS longest_wait
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
  AND state = 'active'
GROUP BY wait_event
ORDER BY waiting_connections DESC;

Setting Timeouts to Prevent Accumulation

The most reliable way to prevent idle in transaction accumulation from becoming an incident is to configure PostgreSQL to terminate these sessions automatically.

idle_in_transaction_session_timeout

This parameter, introduced in PostgreSQL 9.6, terminates any session that has been in idle in transaction state for longer than the specified interval. It is the direct fix for the accumulation problem.

-- Set at the database level (recommended)
ALTER DATABASE myapp SET idle_in_transaction_session_timeout = '5min';

-- Set globally in postgresql.conf
-- idle_in_transaction_session_timeout = '300000'  -- milliseconds

-- Verify current setting
SHOW idle_in_transaction_session_timeout;

-- Set for current session only (useful for testing)
SET idle_in_transaction_session_timeout = '30s';

A value between 30 seconds and 5 minutes is appropriate for most production web applications. Batch processing jobs that legitimately hold long transactions should use SET idle_in_transaction_session_timeout = 0 within their sessions to disable the timeout for their duration.

statement_timeout

Limits the maximum execution time of any single statement. This prevents runaway queries from holding locks and consuming resources indefinitely.

-- Set at database level
ALTER DATABASE myapp SET statement_timeout = '30s';

-- Override for specific roles that run long queries
ALTER ROLE reporting_user SET statement_timeout = '10min';

-- Override for current session
SET statement_timeout = '60s';

-- Check current setting
SHOW statement_timeout;

lock_timeout

Causes a statement to abort if it waits longer than the specified duration for a lock. This prevents lock queue pileups where one blocked query causes dozens of downstream queries to stack up waiting.

-- Abort if we wait more than 5 seconds for any lock
SET lock_timeout = '5s';

-- Useful pattern: short lock_timeout with retry logic at the application layer
ALTER DATABASE myapp SET lock_timeout = '10s';

Terminating Problem Connections Safely

When timeouts have not been configured or an incident is already in progress, you need to terminate problem connections manually. PostgreSQL provides two functions with different levels of force.

pg_cancel_backend() — interrupt the current query

Sends a cancellation signal to the specified backend. The backend will abort its current query and return to an idle state — the connection remains open and the client can continue. This is the gentler option and should be tried first for active connections running runaway queries.

-- Cancel a specific query
SELECT pg_cancel_backend(12345);

-- Cancel all queries running longer than 10 minutes
SELECT
  pid,
  pg_cancel_backend(pid),
  left(query, 80) AS query
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - interval '10 minutes'
  AND pid != pg_backend_pid();

pg_terminate_backend() — close the connection

Sends a termination signal. The connection is forcibly closed, the transaction is rolled back, and the client will receive a connection error. Use this for idle in transaction connections where there is no active query to cancel.

-- Terminate a specific connection
SELECT pg_terminate_backend(12345);

-- Terminate all idle in transaction connections older than 10 minutes
SELECT
  pid,
  usename,
  application_name,
  now() - xact_start AS transaction_age,
  pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND xact_start < now() - interval '10 minutes'
  AND pid != pg_backend_pid();

-- Terminate all connections from a specific application (use carefully)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE application_name = 'problematic_worker'
  AND pid != pg_backend_pid();

Both functions require superuser privileges or membership in the pg_signal_backend role (available since PostgreSQL 14). They return true if the signal was sent successfully and false if the backend was not found (already gone) — they do not return false if the termination failed.

Working with JusDB on PostgreSQL Connection Management

JusDB monitors and optimizes PostgreSQL connection health for engineering teams — detecting idle in transaction accumulation, tuning timeouts, configuring PgBouncer transaction mode, and alerting on connection state anomalies before they cause incidents.

Explore JusDB PostgreSQL Management →  |  Talk to a DBA

Related reading:

• PostgreSQL Connection Pooling with PgBouncer in Production
• Database Connection Pooling Guide
• PostgreSQL Wait Events: Understanding What Slows Your Database