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: Connect to PostgreSQL Detect queries older than 60 seconds 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: ...