How to Automatically Kill Long-Running Queries in PostgreSQL

Long-running queries can cause serious performance issues in PostgreSQL. Here’s how to automate their termination to keep your DB healthy.

Why Kill Long-Running Queries?

In real-world scenarios, long-running queries often:

• Lock tables or rows
• Exhaust memory or CPU
• Cause deadlocks
• Block migrations or monitoring tools

Typical causes:

• Forgotten WHERE clauses
• Heavy reporting queries
• Hung jobs or broken clients

How It Works

We’ll use a Bash script to:

1. Connect to PostgreSQL
2. Detect queries older than 60 seconds
3. Automatically terminate them

Bash Script to Kill Long Queries

1. Define connection and timeout

#!/bin/bash

PG_CONN="postgres://user:pass@postgres-host/postgres-db"
QUERY_TIMEOUT="60"
PSQL_RUN="psql $PG_CONN -Atc"

2. Build the query

QUERY="SELECT pid FROM pg_stat_activity WHERE now() - query_start > '${QUERY_TIMEOUT} seconds'::interval"

3. Run and kill

echo "Checking for queries longer than $QUERY_TIMEOUT seconds..."
pids=$(${PSQL_RUN} "$QUERY")

for pid in $pids; do
  echo "Terminating PID $pid..."
  ${PSQL_RUN} "SELECT pg_terminate_backend($pid)"
done

Automate with Cron

To run every 5 minutes:

*/5 * * * * /path/to/kill-long-queries.sh >> /var/log/pg_kill.log 2>&1

Best Practices

• Exclude critical apps: AND application_name NOT LIKE 'pg_monitor%'
• Use pg_cancel_backend(pid) for softer termination
• Ensure permissions to terminate queries

Conclusion

Automating the cleanup of stale queries can save you hours of debugging and prevent service outages.