Keep Your PostgreSQL Database Clean, Identify Idle Connections

calendar Apr 7, 2024 / · 8 min read · pg_stat_activity postgresql database performance  ·
Share on: linkedin copy

Identify Idle PostgreSQL Connections: Keep Your Database Clean

A session in state idle since 09:14 is a client that connected, finished its work and never hung up. pg_stat_activity has one row per server process, and its state and state_change columns list every session like it. A short query against that view lists them with the time each has been idle.

Purpose and Overview

Every client connection is a server process, and every process counts against max_connections, which is typically 100 by default. Application servers that open a connection per worker, scripts that crash without closing, and desktop tools left open over a weekend all leave sessions sitting in the connection table. Each idle session occupies one of those slots, so a pile of silent connections shrinks what is left for clients that need to work.

Not all idle sessions are the same problem. A plain idle session is waiting for the next command from its client and holds nothing important. An idle in transaction session has opened a transaction and then stopped talking, so it keeps its locks and holds back vacuum. The first kind is a capacity concern; the second is a correctness and bloat concern. The queries below separate the two.

The output of each query includes the process ID, the role, the database, the client address, the application name, and how long the session has been idle. The process ID is what pg_terminate_backend() needs when a session has to go; the full procedure for that is in Kill Idle PostgreSQL Sessions with SQL.

Sample Code

 1SELECT
 2  pid,
 3  usename,
 4  datname,
 5  application_name,
 6  client_addr,
 7  backend_start,
 8  state_change,
 9  now() - state_change AS idle_for
10FROM pg_stat_activity
11WHERE state = 'idle'
12  AND backend_type = 'client backend'
13  AND pid <> pg_backend_pid()
14  AND now() - state_change > interval '10 minutes'
15ORDER BY state_change;

Sessions that opened a transaction and went quiet are the more expensive kind. This variant lists them with the age of the transaction:

 1SELECT
 2  pid,
 3  usename,
 4  datname,
 5  application_name,
 6  client_addr,
 7  state,
 8  xact_start,
 9  now() - state_change AS idle_for,
10  left(query, 80)      AS last_query
11FROM pg_stat_activity
12WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
13  AND pid <> pg_backend_pid()
14  AND now() - state_change > interval '5 minutes'
15ORDER BY xact_start;

To see which client machines are responsible, rank the idle sessions per client address, oldest connection first:

 1SELECT
 2  rank() OVER (PARTITION BY client_addr ORDER BY backend_start) AS rank_in_client,
 3  pid,
 4  usename,
 5  client_addr,
 6  backend_start,
 7  state_change
 8FROM pg_stat_activity
 9WHERE state = 'idle'
10  AND backend_type = 'client backend'
11  AND pid <> pg_backend_pid()
12  AND now() - state_change > interval '10 minutes'
13ORDER BY client_addr, rank_in_client;

Notes: The first and third queries need PostgreSQL 10 or later because of the backend_type column; on PostgreSQL 9.x remove that line, since background processes are not listed in the view before version 10. Change the 10 minutes threshold to suit the workload. A role without the right privileges sees only its own sessions in full; run the queries as a superuser or a member of pg_read_all_stats for the complete list.

Code Breakdown

Each query has the same skeleton: pick the sessions in a given state, drop the current session, and keep only those older than a cutoff.

The state filter

state is the column that decides what "idle" means. The possible values are starting, active, idle, idle in transaction, idle in transaction (aborted), fastpath function call, and disabled. A session in idle is waiting for a new client command. idle in transaction means the backend is inside a transaction but not executing a query. idle in transaction (aborted) is the same except that one of the statements in the transaction caused an error. disabled appears when track_activities is off for the backend, in which case none of this monitoring works.

Why state_change measures idle time

state_change records when the state last changed. For a session in idle, that is the moment the last statement finished, so now() - state_change is exactly how long the client has been silent. query_start is a weaker choice: for a session that is not active it holds the start time of the last query, which can be much earlier than the end of that query.

Excluding your own session and background processes

pid <> pg_backend_pid() removes the session running the query. backend_type = 'client backend' removes autovacuum workers, the checkpointer, walsenders and other server processes that appear in the same view since PostgreSQL 10. Without it those rows add noise, and the client address is null for them.

Reading the query column

query shows the text of the current statement for an active session and the last statement that was executed in every other state. For an idle session it therefore tells you what the client did last, not what it is doing. An older approach of filtering on an empty query string misses every session that has run at least one statement, which is nearly all of them; filter on state instead. The text is truncated at 1024 bytes by default, a limit set by track_activity_query_size, so left(query, 80) keeps the output readable.

The rank() window

rank() OVER (PARTITION BY client_addr ORDER BY backend_start) numbers the sessions inside each client address, starting with the oldest connection. A client address with ranks running into the dozens points to a connection leak in whatever runs on that host. client_addr is null for Unix-socket connections, so local sessions collapse into one group.

Key Idle Session Settings

Finding idle sessions is the manual half of the job. PostgreSQL can also close them on its own.

idle_in_transaction_session_timeout

This setting terminates any session that has been idle inside an open transaction for longer than the configured time. The default of zero disables it. It was added in PostgreSQL 9.6. Locks held by a forgotten transaction block other sessions, and an open transaction prevents vacuum from removing recently dead rows that the transaction could still see, which contributes to table bloat.

idle_session_timeout

This setting terminates a session that has been idle outside a transaction for longer than the configured time, and it was added in PostgreSQL 14. The default is zero, which disables it. A plain idle session imposes no large cost on the server, so there is less need for this timeout than for the transaction one. Connection-pooling software may not react well to unexpected connection closure, so enable it for interactive roles rather than for every role:

1ALTER ROLE report_user SET idle_session_timeout = '30min';
2ALTER ROLE app_user SET idle_in_transaction_session_timeout = '5min';

Without units the value is read as milliseconds.

pg_terminate_backend

When a session has to be removed now, pg_terminate_backend(pid) sends the termination signal. A role can terminate sessions of roles it is a member of, or sessions of any role if it holds the privileges of pg_signal_backend; only superusers can terminate superuser backends. Without a timeout argument the function returns true as soon as the signal is sent, whether or not the process has exited yet.

Practical Applications

The queries fit a few recurring situations.

Connection limit alarms

When monitoring reports that connections are near max_connections, run the third query first. If one client address accounts for most idle sessions, the fix belongs in that application's pool settings, not in the database. Terminating sessions only buys time.

Finding the source of table bloat

A table that keeps growing despite regular vacuum often has an old transaction holding back cleanup. The second query, ordered by xact_start, puts the oldest open transaction at the top. If it belongs to a session that has been idle for hours, that is the cause.

Pre-maintenance cleanup

Before a schema change that needs an exclusive lock, an idle-in-transaction session holding a lock on the table makes the change wait indefinitely. List those sessions, contact their owners or terminate them, then run the change. Terminating a backend ends its session, so confirm nothing important is uncommitted first.

Tuning timeouts from real data

Run the queries at a regular interval for a week and record the longest idle times per role. The numbers show where a timeout of five or thirty minutes would be safe and which roles need an exception.

Version Compatibility

All three queries rely on the state and state_change columns. backend_type was added in PostgreSQL 10, together with the change that shows auxiliary processes, background workers and walsender processes in the view. idle_in_transaction_session_timeout arrived in 9.6 and idle_session_timeout in 14, so on older servers the manual query is the only tool available. On any version, sessions belonging to other users appear with many null columns unless the caller is a superuser or holds pg_read_all_stats; the existence of the session, its user and its database stay visible to everyone.

Best Practices

  • Separate idle from idle in transaction — treat the two states with different urgency; the transaction case blocks locks and vacuum.
  • Set timeouts per role — apply idle_in_transaction_session_timeout broadly, and idle_session_timeout only where clients can reconnect cleanly.
  • Exclude admin tools when terminating — add a filter on application_name or usename before feeding the pids to pg_terminate_backend.
  • Fix the client, not just the session — a leak that returns every day needs a pool or application fix.
  • Grant monitoring access narrowly — pg_read_all_stats gives a monitoring role full visibility without superuser rights.
  • Alert on idle-in-transaction age — a transaction open for more than a few minutes is worth a notification.

References

Posts in this series