Poorly optimized queries and excessive connections can cause problems in PostgreSQL, here's a quick way to identify and kill them.
First the query, then we'll explain parts of it:
SELECT COALESCE(now() - query_start, '0 seconds'::INTERVAL) AS duration , * FROM pg_stat_activity WHERE state != 'idle' AND pid != pg_backend_pid() ORDER BY 1 DESC, backend_start;
pg_stat_activity view will return one row per server process, and each row will have information about the PID, user, query, and more.
state != 'idle'will filter out open connections that aren't executing anything - you might want to include these if you're debugging connection issues
pid != pg_backend_pid()will filter out this
pg_stat_activityquery from the results
datname = current_database()will filter to only the current database, which may be the only database your active user has permissions to
See "Finding Long-Running Queries in MySQL" for the MySQL version of this query.
Dec 22, 2021 · 4 min read
Poorly optimized queries and excessive connections can cause problems in MySQL, here's a quick way to identify and kill them.
Killing a single connection
pg_terminate_backend() function is used to both terminate a query and kill the connection, given a PID from the above query:
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = :pid AND pid != pg_backend_pid();
Killing every query & connection
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = :database_name AND pid != pg_backend_pid();