Beginner LevelPostgreSQL
How do you monitor running queries using the pg_stat_activity view?
❓ Interview Prompt: Explain `pg_stat_activity` system view, monitoring client connections, query state (`active`, `idle`), duration, wait events, and killing queries (`pg_terminate_backend`).
The 'pg_stat_activity' view displays real-time information about active backend server processes and running queries:
Key Diagnostics:
•
'state': 'active', 'idle', 'idle in transaction'.
•
'wait_event_type': Displays if a query is waiting on locks or disk I/O.
•
Cancel Query: 'SELECT pg_cancel_backend(pid);' (cancels query).
•
Terminate Connection: 'SELECT pg_terminate_backend(pid);' (kills connection).
Code Implementation Example
sql
-- Find queries running longer than 5 seconds
SELECT pid, now() - query_start AS duration, query, state, wait_event
FROM pg_stat_activity
WHERE state != 'idle' AND (now() - query_start) > interval '5 seconds';
-- Terminate a stuck backend process by PID
SELECT pg_terminate_backend(12345);💡Monitoring long-running queries and terminating backend PIDs via pg_stat_activity.
🎯 Interview Key Takeaway
Monitor pg_stat_activity to detect long-running active queries and lock wait states.
#pg_stat_activity#Monitoring#pg_terminate_backend#Beginner
Back to all PostgreSQL Questions→