Kill Idle PostgreSQL Sessions with SQL

calendar Mar 24, 2026 / · 9 min read · postgresql database administration sql queries connection management performance tuning pg_stat_activity postgres dba  ·
Share on: linkedin copy

Kill Idle PostgreSQL Sessions with SQL

The connection count sits at max_connections, new clients are being refused, and pg_stat_activity is full of sessions that have run nothing for hours. Freeing the slots takes one statement, pg_terminate_backend(), but the choices around it decide whether the cleanup is safe: which sessions to select, which signal to send, who is allowed to send it, and what happens to the client on the other end.

Purpose and Overview

Every connected session holds a server process, memory, and a connection slot, whether or not it is doing any work. A session that is idle in the plain sense costs little beyond the slot. A session that is idle in transaction costs more, because its open transaction keeps its locks and prevents vacuum from removing recently dead rows. Cleaning up either kind is a routine DBA task when a connection pooler leaks sessions, an application forgets to close transactions, or an interactive client is left open.

The procedure here is the termination itself. Finding and measuring idle sessions, and setting server-side timeouts that close them automatically, are covered in identifying idle PostgreSQL connections, and the slow-query side is in monitoring slow running queries. For a broader look at everything a server is running before deciding what to stop, see monitoring running queries with pg_stat_activity.

Two functions do the work. pg_cancel_backend(pid) stops the current query and leaves the connection open. pg_terminate_backend(pid) ends the session. An idle session has no current query to cancel, so for idle sessions the terminate function is the one that frees the slot. The selection logic matters more than the call: pick the wrong filter and the statement kills healthy connections.

Sample Code

Preview first. This lists sessions that have been idle, or idle inside a transaction, for more than 15 minutes:

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

Terminate the same set, on every database in the cluster:

 1SELECT
 2    pid,
 3    usename,
 4    application_name,
 5    pg_terminate_backend(pid) AS signalled
 6FROM pg_stat_activity
 7WHERE backend_type = 'client backend'
 8  AND pid <> pg_backend_pid()
 9  AND state IN ('idle', 'idle in transaction', 'idle in transaction (aborted)')
10  AND state_change < clock_timestamp() - interval '15 minutes';

Terminate in the current database only, protecting admin tools and service accounts:

 1SELECT
 2    pid,
 3    pg_terminate_backend(pid, 5000) AS terminated
 4FROM pg_stat_activity
 5WHERE backend_type = 'client backend'
 6  AND pid <> pg_backend_pid()
 7  AND datname = current_database()
 8  AND usename NOT IN ('postgres')
 9  AND application_name !~ '^(psql|pgAdmin)'
10  AND state IN ('idle', 'idle in transaction', 'idle in transaction (aborted)')
11  AND state_change < clock_timestamp() - interval '15 minutes';

For a query that is running rather than idle, cancel it first and leave the connection alone:

1SELECT pid, pg_cancel_backend(pid) AS cancelled
2FROM pg_stat_activity
3WHERE state = 'active'
4  AND pid <> pg_backend_pid()
5  AND query_start < clock_timestamp() - interval '30 minutes';

Notes: The idle-time test uses state_change, which needs PostgreSQL 9.2 or later. The backend_type filter needs PostgreSQL 10; on earlier releases remove it. The two-argument form pg_terminate_backend(pid, 5000) needs PostgreSQL 14; on earlier releases use the one-argument form. Replace 'postgres' and the application-name pattern with the accounts and tools to protect.

Code Breakdown

The selection is built from the same four conditions in every variant.

state_change, not query_start

query_start is the time the currently active query started, or, when the state is not active, the time the last query started. For an idle session that is the start of the last query, not the moment the session went idle. A session that ran a 40-minute report and finished one minute ago has an old query_start and a one-minute-old state_change, so a filter on query_start would terminate a session that has barely been idle. state_change is the time the state last changed, which for an idle session is when it finished its last command. The idle-time test belongs on state_change. query_start is the right column for the cancel variant above, because there the state is active and query_start is when the running query began.

State list

idle is a connection waiting for the next command. idle in transaction has an open transaction and is running nothing. idle in transaction (aborted) is the same except a statement in the transaction raised an error, and the session is waiting for a ROLLBACK. All three are in the list. A fourth value, disabled, means track_activities is off for that backend, so its other columns cannot be trusted and it is left out here.

Why there is no filter on the query text

The query column shows the currently executing query when the state is active, and the last query that ran in every other state. An idle session therefore almost always has non-empty query text, so a condition such as query = '' matches only sessions that never ran a statement. It does not mean "no work in progress", and it is not used here. The state column is the correct signal.

Self and system exclusions

pid <> pg_backend_pid() keeps the session from terminating itself. backend_type = 'client backend' keeps background processes such as the checkpointer and autovacuum workers out of the result, so the statement only ever targets user connections.

The signal functions

pg_cancel_backend and pg_terminate_backend send SIGINT and SIGTERM respectively to the backend with the given process ID, and both return a boolean. Without a timeout, pg_terminate_backend returns true as soon as the signal was sent, whether or not the process has actually exited. With a timeout in milliseconds greater than zero, it waits until the process has terminated or the time has passed. If the process ended, it returns true. On timeout it emits a warning and returns false. Returning pid next to the function result gives a record of what was signalled.

Key Permission and Safety Rules

Signalling another session is a privileged action, and a few rules decide who can do it.

Who may signal a session

A superuser can signal any session. A non-superuser can signal a session if the calling role is a member of the role that owns the target backend, or if the calling role has privileges of the predefined pg_signal_backend role. Only a superuser can signal a superuser's backend, so even pg_signal_backend does not extend to those sessions. pg_signal_backend was introduced in PostgreSQL 9.6.

Least privilege for a cleanup job

A cleanup job does not need a superuser. Granting pg_signal_backend to a dedicated maintenance role limits what the job can touch to non-superuser sessions, and the usename exclusion in the script protects the sessions that should stay.

What the client sees

Termination closes the connection from the server side. The client gets a connection error on its next use, and any open transaction in that session does not commit. Applications and poolers that do not expect a closed connection can fail or hold a dead connection in their pool, which is why the exclusion filters and the preview matter on managed pools.

Practical Applications

The same filter serves several situations, with a different safety margin each time.

Freeing connection slots under pressure

When new connections are being refused, run the preview, check that application_name and client_addr on the oldest rows belong to a pool that can reconnect, then run the terminate variant.

Clearing an idle-in-transaction blocker

A session in idle in transaction with an old xact_age can hold locks that block other sessions. Find it with the blocking query in the running-queries post, then terminate that one pid. Cancelling is not useful here because the session is not running a query.

Scheduled cleanup of stale pool connections

Wrap the terminate statement in a job that runs every few minutes with a longer idle threshold, and have it insert the returned pid, usename, and application_name into a log table. Repeated hits from the same application show which client is leaking sessions, which is the real fix.

Cancel first on running queries

For a runaway query, pg_cancel_backend ends the statement and keeps the connection, so a pooled application can carry on. Terminate only if the cancel does not take effect.

Version Compatibility

The idle-time filter needs state and state_change in pg_stat_activity, available since PostgreSQL 9.2. The backend_type column arrived in PostgreSQL 10. The predefined role pg_signal_backend arrived in PostgreSQL 9.6. The optional timeout argument to pg_terminate_backend was added in PostgreSQL 14.

PostgreSQL 14 also added the idle_session_timeout parameter, which closes sessions that have been idle outside a transaction for longer than the configured time. idle_in_transaction_session_timeout does the same for sessions idle inside a transaction. Both default to zero, which disables them. Pooling software and other middleware may not react well to an unexpected connection closure, so enable idle_session_timeout for particular users rather than for connections that come through a pooler. A server-side timeout is the durable fix; this script is the manual tool for the moment before one is in place.

Best Practices

  • Preview with the same WHERE clause — run the SELECT version first and read application_name, client_addr, and idle_for before terminating anything.
  • Measure idle time with state_change — query_start reports when the last query began, so it overstates idle time for any session whose last query was long.
  • Treat idle in transaction separately — terminating it discards uncommitted work, so use a longer threshold and log the pid, user, and application.
  • Protect admin and service sessions — exclude usename and application_name values you rely on before running against a shared cluster.
  • Use a narrow role — grant pg_signal_backend to a dedicated cleanup role instead of running the job as a superuser.
  • Prefer server timeouts for steady state — idle_in_transaction_session_timeout and idle_session_timeout close sessions without a scheduled script.

References

Posts in this series