Context

Production databases occasionally slow down because one user or application is running expensive queries, opening too many sessions, or holding locks. Interviewers want to know whether you can diagnose the issue quickly and respond safely.

Core question

Explain how you would identify a single user generating heavy load on a PostgreSQL database and how you would stop that activity. Your answer should cover:

1. Which PostgreSQL system views or tools you would check first
2. How you would determine whether the load is CPU, long-running queries, blocking locks, or too many connections
3. Which commands you would use to stop the user safely
4. The difference between cancelling a query and terminating a backend session
5. What follow-up steps you would take after mitigation

Scope guidance

Focus on practical PostgreSQL operations rather than generic database theory. A strong answer should mention specific views such as pg_stat_activity, lock inspection, and the operational trade-offs of pg_cancel_backend() versus pg_terminate_backend().

pg_stat_activity

pg_stat_activity is the primary PostgreSQL system view for inspecting active sessions. It shows the connected user, current query, session state, wait events, start times, and application name, which makes it the first place to identify a noisy user.

SELECT pid, usename, application_name, state, query_start, now() - query_start AS runtime, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY runtime DESC;

Blocking and Lock Inspection

Heavy load is not always caused by CPU-intensive queries; it may come from blocking locks that stall many sessions. Inspecting locks helps distinguish a single expensive query from one session that is preventing others from progressing.

SELECT a.pid, a.usename, a.state, a.query, pg_blocking_pids(a.pid) AS blocking_pids
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0;

Cancel vs Terminate

pg_cancel_backend(pid) sends a cancel signal to the currently running query but keeps the session connected. pg_terminate_backend(pid) forcibly ends the entire backend session, which is stronger and should be used only when cancellation is ineffective or the session is clearly abusive.

SELECT pg_cancel_backend(12345);
SELECT pg_terminate_backend(12345);

Connection-Level Diagnosis

A user can overload the database by opening many sessions even if each query is small. Grouping pg_stat_activity by user and state helps identify connection storms, idle-in-transaction sessions, or application pools misbehaving.

SELECT usename, state, COUNT(*) AS session_count
FROM pg_stat_activity
GROUP BY usename, state
ORDER BY session_count DESC;

Safe Operational Response

Stopping load should be done in escalating steps: identify impact, confirm the offending session, cancel first, terminate if needed, and then prevent recurrence. Good operational practice also includes checking logs, query plans, and application behavior after the incident.