In this lesson, you will learn how to proactively manage connection life-cycles to prevent "zombie" or runaway sessions from starving your database of critical resources.
The Problem: The "Idle" Drain
Even in a well-tuned system, applications occasionally crash or experience network partitions, leaving "ghost" connections active on the server. Because PostgreSQL creates a dedicated process for each connection, these idle sessions consume memory and process slots. Furthermore, a runaway query—perhaps one missing a proper index—can hog CPU and lock rows indefinitely, causing backups to stall and performance to degrade system-wide.
Identifying the Culprits
Before we terminate anything, we must inspect the pg_stat_activity view. This system view acts as the dashboard for all current connection states.
SELECT pid, usename, state, query_start, query
FROM pg_stat_activity
WHERE state != 'idle'
AND now() - query_start > interval '5 minutes';
This query highlights sessions that have been actively executing a task for longer than five minutes.
Terminating Sessions
Once you identify a session that is threatening system stability, you have two primary tools:
pg_terminate_backend(pid): This is the "hard kill." It forces the connection to close immediately and rolls back any uncommitted transactions. Use this for truly runaway processes that are impacting production performance.pg_cancel_backend(pid): This is the "gentle nudge." It sends a SIGINT to the process, which instructs the backend to stop the current query while keeping the connection alive. It is preferred for long-running reporting queries that need to be aborted without dropping the user’s session.
Automating Stability
In production, relying on manual intervention is risky. It is a best practice to configure statement_timeout at the session or user level to kill queries automatically if they exceed a set duration. Additionally, keep an eye on idle_in_transaction_session_timeout, which prevents connections that have started a transaction but stopped sending commands from holding locks and preventing VACUUM operations from cleaning up dead tuples (bloat).
Key Takeaway: Proactive monitoring of pg_stat_activity combined with strategic use of pg_terminate_backend ensures that runaway queries and abandoned sessions do not cripple your database's resource availability.