Kill Idle PostgreSQL Sessions with SQL

Kill Idle PostgreSQL Sessions with SQL

These PostgreSQL scripts terminate idle sessions that have been inactive for more than 15 minutes. Two variants are provided: one that targets idle sessions across all databases on the server, and one scoped to only the currently connected database.

Purpose and Overview

Idle and stale connections hold resources — memory, file descriptors, and connection slots — even when no work is being done. In environments where connection limits are tight, or where application connection poolers leak sessions, regularly cleaning up long-idle connections keeps the database healthy and connection counts under control.

These scripts use pg_terminate_backend() to actively kill matching sessions, rather than just listing them.

SQL Scripts

Kill Idle Sessions — All Databases

 1SELECT
 2    pg_terminate_backend(pid)
 3FROM
 4    pg_stat_activity
 5WHERE
 6    -- don't kill yourself
 7    pid <> pg_backend_pid()
 8    --  AND
 9    -- don't kill your admin tools
10    --application_name !~ '(?:psql)|(?:pgAdmin.+)'
11    --  AND
12    --usename not in ('postgres')
13        AND
14    query in ('')
15        AND
16    state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
17        AND
18    (
19      (current_timestamp - query_start) > interval '15 minutes'
20          OR
21      (query_start IS NULL AND (current_timestamp - backend_start) > interval '15 minutes')
22    )
23;

Kill Idle Sessions — Current Database Only

 1SELECT
 2    pg_terminate_backend(pid)
 3FROM
 4    pg_stat_activity
 5WHERE
 6    -- don't kill yourself
 7    pid <> pg_backend_pid()
 8        AND
 9    datname = current_database()
10        AND
11    query in ('')
12        AND
13    state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
14        AND
15    (
16      (current_timestamp - query_start) > interval '15 minutes'
17          OR
18      (query_start IS NULL AND (current_timestamp - backend_start) > interval '15 minutes')
19    )
20;

The only difference between the two is the datname = current_database() condition in the second script, which restricts termination to the database you are currently connected to.

Code Breakdown

pg_terminate_backend(pid)

pg_terminate_backend() sends a SIGTERM signal to the backend process identified by pid, cleanly terminating the connection. It returns true if the signal was sent successfully. Requires superuser privileges or the pg_signal_backend role (PostgreSQL 14+).

pg_stat_activity

The FROM pg_stat_activity clause queries the system view that exposes one row per server process, including client connections and background workers.

pid <> pg_backend_pid()

Excludes your own session from termination so the script cannot kill itself.

query IN ('')

Filters for connections where the last recorded query is an empty string, a reliable indicator that no active work is in progress.

state IN (...)

The four states targeted are:

idle — the connection is open but not executing any query.

idle in transaction — the connection has an open transaction but is not executing a query. This is particularly important to catch, as an open transaction can hold locks and block other sessions.

idle in transaction (aborted) — the transaction has encountered an error and is waiting to be rolled back. These sessions block cleanup and should be terminated.

disabled — statistics tracking is disabled for this connection; generally safe to include.

Idle Duration Check

1(current_timestamp - query_start) > interval '15 minutes'
2    OR
3(query_start IS NULL AND (current_timestamp - backend_start) > interval '15 minutes')

The dual condition handles two cases: connections that last ran a query more than 15 minutes ago, and connections where query_start is NULL (never ran a query) but have been connected for more than 15 minutes.

datname = current_database()

Present only in the second script. Limits the scope to the database you are connected to, which is useful when you want to clean up one database without affecting sessions on others running on the same server.

Commented-Out Safety Options

The scripts include two commented-out filters that can be enabled for safer operation:

Exclude admin tools:

1--application_name !~ '(?:psql)|(?:pgAdmin.+)'

Uncomment this to preserve connections from psql or pgAdmin, preventing accidental termination of an active admin session.

Exclude specific users:

1--usename not in ('postgres')

Uncomment and extend the list to protect service accounts or superuser sessions from being terminated.

Practical Applications

Automated Session Cleanup

Schedule either script as a cron job or maintenance task to run during off-peak hours, keeping idle connection counts low without manual intervention.

Connection Limit Management

PostgreSQL's max_connections setting caps the total number of allowed connections. When that limit is approached, new connections fail. Running this cleanup script frees slots before the limit is hit.

Clearing Stuck Transactions

The idle in transaction and idle in transaction (aborted) states can hold row locks for extended periods, blocking writes. Terminating these sessions releases locks and unblocks other queries.

Adjusting the Idle Threshold

Change interval '15 minutes' to match your environment. Shorter intervals aggressively reclaim connections; longer intervals give clients more time to recover from network blips:

1-- More aggressive: 5 minutes
2(current_timestamp - query_start) > interval '5 minutes'
3
4-- More lenient: 1 hour
5(current_timestamp - query_start) > interval '1 hour'

Preview Before Terminating

Run the equivalent SELECT first to review which sessions will be affected before executing pg_terminate_backend():

 1SELECT
 2    pid,
 3    datname,
 4    usename,
 5    application_name,
 6    state,
 7    query_start,
 8    backend_start,
 9    current_timestamp - query_start AS idle_duration
10FROM
11    pg_stat_activity
12WHERE
13    pid <> pg_backend_pid()
14    AND query IN ('')
15    AND state IN ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
16    AND (
17        (current_timestamp - query_start) > interval '15 minutes'
18        OR
19        (query_start IS NULL AND (current_timestamp - backend_start) > interval '15 minutes')
20    )
21ORDER BY
22    idle_duration DESC;

Version Compatibility

Requires PostgreSQL 9.2 or later. Tested on PostgreSQL 9.2+, 10.x, 11.x, 12.x, and 13.0.

Best Practices

Always preview first: Run a SELECT version of the query before terminating to confirm which sessions will be affected.

Protect admin sessions: Uncomment the application_name exclusion if you regularly leave psql or pgAdmin connections open.

Use connection pooling: PgBouncer or similar poolers reduce idle connection accumulation at the source, making cleanup scripts a last resort rather than a routine necessity.

Log terminations: Wrap the script in a procedure that logs terminated PIDs and usernames to a table for auditing purposes.

References

• PostgreSQL Documentation - pg_terminate_backend - Reference for server signaling functions including pg_terminate_backend
• PostgreSQL Documentation - pg_stat_activity - Full column reference for the pg_stat_activity view
• Source SQL Script - postgres_idle_sessions_kill.sql - Original script by Hari Sekhon
• Source SQL Script - postgres_idle_sessions_current_db_kill.sql - Current database variant by Hari Sekhon