If you run PostgreSQL in production for a long time, you will probably see this problem at some point: Queries start waiting. Connections increase. The database becomes slower. Connection usage gets close to max_connections . One common cause is a session that is blocking other sessions. A common example is a connection in the idle in transaction state. The application started a transaction but did not COMMIT or ROLLBACK it. In this article, we will use pg_stat_activity and several PostgreSQL functions to investigate these problems. We will also look at when to use: pg_cancel_backend() pg_terminate_backend() pg_blocking_pids() The goal is not only to kill a bad session. The goal is to understand why it happened and prevent it from happening again. Why Can PostgreSQL Sessions Block Each Other? PostgreSQL uses MVCC (Multi-Version Concurrency Control) and locking to support many users at the same time. For example: BEGIN ; UPDATE orders SET status = 'PROCESSING' WHERE id = 100 ; The transaction may hold locks until it finishes with: COMMIT ; or: ROLLBACK ; The problem happens when the application starts a transaction but never finishes it. The connection may then appear as: idle in transaction The session is not running a query anymore, but the transaction is still open. This can cause several problems: Locks may still be held. Other queries may have to wait. Old transaction snapshots may stay open. VACUUM may not be able to clean some dead rows. Tables may become larger over time. This is why it is important to understand the difference between: active and: idle in transaction An active session is currently running a query. An idle in transaction session is waiting for the client, but its transaction is still open. Step 1: Inspect All Non-Idle Sessions I normally start an investigation with this query: SELECT pid , usename , application_name , state , query_start , now () - query_start AS duration , query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY query_start ; This removes normal idle connections from the result. You can now see sessions that are: running queries, waiting, or idle inside a transaction. The application_name field is very useful when you have many services. For example, your PostgreSQL connection may include: application_name=order-service Then you may see something like: pid application_name state 12345 order-service active 12346 payment-service idle in transaction This is much easier than trying to guess which application owns a PID. For Go, Node.js, or other microservice systems, I strongly recommend setting application_name for every service. Step 2: Use a Quick Triage Query During an incident, sometimes you do not need every column. You just want to quickly see what is happening. Use: SELECT pid , age ( clock_timestamp (), query_start ), state , query FROM pg_stat_activity WHERE state <> 'idle' ; This shows: PID query age session state query text I use this kind of query as a quick first check. For example: PID 1596183 State: active Duration: 00:08:32 Query: SELECT ... Now you know that one query has been running for more than eight minutes. The next question is: Is this query really slow, or is it waiting for another session? That is an important difference. Step 3: Find Idle-in-Transaction Sessions To check only sessions that are idle inside a transaction: SELECT pid , usename , application_name , state , xact_start , state_change , now () - xact_start AS transaction_age , query FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY xact_start ; Pay special attention to: xact_start This tells you when the transaction started. A transaction that has been open for a few seconds may be normal. A transaction that has been open for 30 minutes or several hours is much more suspicious. Do not immediately kill every idle in transaction connection. First check: Which application created it? How long has the transaction been open? Is this normal for the application? Is it blocking another session? Step 4: Terminate an Idle Transaction If you confirm that a session is stuck and should not still be open, you can terminate it. For one PID: SELECT pg_terminate_backend ( 1596183 ); This closes the PostgreSQL session. PostgreSQL will roll back its open transaction and release its locks. For old idle transactions, you can also use a controlled query like: SELECT pg_terminate_backend ( pid ) FROM pg_stat_activity WHERE state = 'idle in transaction' AND state_change < now () - interval '5 minutes' AND pid <> pg_backend_pid (); This example only terminates sessions that have been idle inside a transaction for more than five minutes. The condition: pid <> pg_backend_pid () prevents the query from terminating your own PostgreSQL session. Be careful with this command in production. It is usually better to filter by things such as: datname usename or: application_name For example: SELECT pg_terminate_backend ( pid ) FROM pg_stat_activity WHERE state = 'idle in transaction' AND application_name = 'order-service' AND state_change < now () - interval '5 minutes' AND pid <> pg_backend_pid (); This is much safer than terminating every idle transaction in the cluster. Step 5: Cancel a Running Query Sometimes the connection itself is fine, but one query is taking too long. In that case, try: SELECT pg_cancel_backend ( 1596183 ); pg_cancel_backend() stops the current query but keeps the database connection alive. This makes it a good first choice for things like: a very expensive SELECT , a report query, an accidental full-table operation, or a query that a user wants to cancel. Think about it like this: pg_cancel_backend() | v Stop the current query | v Keep the connection pg_cancel_backend() vs pg_terminate_backend() These two functions are similar, but they solve different problems. Function What happens Good for pg_cancel_backend(pid) Stops the current query but keeps the connection Long-running or unwanted active queries pg_terminate_backend(pid) Closes the whole database session Stuck sessions, idle transactions, serious blocking problems My normal approach is: Active bad query | v pg_cancel_backend() | | still causing problems v pg_terminate_backend() For an idle in transaction session, pg_cancel_backend() normally does not solve the problem because there is no active query to cancel. In that situation, you may need: pg_terminate_backend () Step 6: Find Who Is Blocking a Query A long-running query is not always slow. Sometimes it is simply waiting for another transaction. PostgreSQL provides a very useful function: pg_blocking_pids () For example: SELECT pid , usename , application_name , state , pg_blocking_pids ( pid ) AS blocking_pids , query FROM pg_stat_activity WHERE cardinality ( pg_blocking_pids ( pid )) > 0 ; You may get something like: pid blocking_pids
20002 {20001} This means: PID 20001 | | blocks v PID 20002 Do not immediately kill PID 20002 . It is the blocked session , not necessarily the real problem. You should investigate PID 20001 . Finding the Root Blocker A production problem can be more complicated: Session A | | blocks v Session B | | blocks v Session C If you kill Session B, Session C may improve, but you have not fixed the real source of the problem. The real blocker is: Session A That is why pg_blocking_pids() is useful. You want to follow the blocking chain until you find the session that is not waiting for another backend. That session is usually the root blocker . Investigate that session first. Step 7: Check Which Database Uses the Most Connections If your PostgreSQL server contains several databases, check how connections are distributed: SELECT datname , count ( * ) FROM pg_stat_activity GROUP BY datname ORDER BY count ( * ) DESC ; Example: datname count
order_prod 82 notification 15 postgres 3 Now you know where most connections are being used. If one database has a very high number, investigate: application connection pool size, PgBouncer settings, connection leaks, long-running queries, idle transactions. A high connection count does not always mean PostgreSQL itself is the problem. The application may simply be opening too many connections. Step 8: Check Connection Headroom You should also check how close PostgreSQL is to its configured connection limit. SELECT ( SELECT count ( * ) FROM pg_stat_activity ) AS used_connections , ( SELECT setting :: int FROM pg_settings WHERE name = 'max_connections' ) AS max_connections ; For example: used_connections | max_connections -----------------+---------------- 87 | 100 You are now using: 87 / 100 connections. That means there is very little room left. This becomes dangerous because when an incident happens, you may not even be able to open another connection to investigate the problem. For production systems, I normally monitor connection usage before it gets close to the maximum. Preventing Idle Transactions Manually killing sessions is useful during an incident. But it is not the real fix. PostgreSQL can automatically terminate connections that stay idle inside a transaction for too long. For example: ALTER DATABASE mydb SET idle_in_transaction_session_timeout = '2min' ; Now PostgreSQL will close sessions that remain idle inside a transaction longer than the configured timeout. You should choose the timeout based on your workload. For example: 30 seconds may work for a fast OLTP API. While: 5 minutes may be safer for applications with longer business operations. Do not copy a timeout value without understanding your system first. Protect Against Long-Running Queries You can also use: statement_timeout For example: ALTER DATABASE mydb SET statement_timeout = '30s' ; This prevents individual SQL statements from running forever. However, be careful. A timeout that is good for API requests may be too short for: reports, migrations, ETL jobs, batch processing. Different applications may need different timeout values. Log Lock Problems Another useful PostgreSQL setting is: log_lock_waits You can enable it with: ALTER SYSTEM SET log_lock_waits = on ; PostgreSQL can then log queries that wait too long for a lock. This works together with: deadlock_timeout For example: ALTER SYSTEM SET deadlock_timeout = '1s' ; This makes lock problems much easier to investigate because PostgreSQL logs useful information when sessions wait for locks. Remember to reload the configuration when required. Fix the Problem in the Application Database commands help during an incident. But if idle in transaction keeps happening, the real bug is usually inside the application. For example, in Go: tx , err := db . Begin ( ctx ) if err != nil { return err } defer tx . Rollback ( ctx ) Then later: if err := tx . Commit ( ctx ); err != nil { return err } The important idea is simple: BEGIN | +---- success ----> COMMIT | +---- error ------> ROLLBACK Every transaction must have a clear ending. This is especially important when using libraries such as: pgx GORM database/sql Node.js pg Every error path should either commit or roll back the transaction correctly. Connection Pools Matter Too Also check your connection pool configuration. For example, imagine you have: 10 services and every service allows: 20 connections Your applications may try to create: 10 × 20 = 200 connections But PostgreSQL may only allow: max_connections = 100 That architecture will eventually create problems. Tools such as PgBouncer can help reduce the number of PostgreSQL backend connections, but they do not replace correct transaction handling. You still need to: commit transactions, roll back transactions, set reasonable pool limits, monitor connection usage. My Production Troubleshooting Flow When PostgreSQL starts becoming slow, I normally think about the problem in this order: PostgreSQL is slow | v Check pg_stat_activity | v Are queries active or idle in transaction? | +------ idle in transaction | | | v | Check transaction age | | | v | Check if it is blocking | | | v | pg_terminate_backend() | +------ active | v Check blocking_pids | +-------+-------+ | | blocked not blocked | | v v Find blocker Investigate query | v pg_cancel_backend() The important point is: Do not kill a PostgreSQL PID only because a query has been running for a long time. First understand what that PID is doing. Ask: Is it running?
Is it waiting?
Is it blocking?
Is it being blocked?
Is it idle inside a transaction? Then choose the correct action. Useful Commands Summary Check non-idle sessions: SELECT pid , usename , application_name , state , query_start , now () - query_start AS duration , query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY query_start ; Find idle transactions: SELECT pid , application_name , xact_start , now () - xact_start AS transaction_age , query FROM pg_stat_activity WHERE state = 'idle in transaction' ; Find blocked sessions: SELECT pid , pg_blocking_pids ( pid ) AS blocking_pids , query FROM pg_stat_activity WHERE cardinality ( pg_blocking_pids ( pid )) > 0 ; Cancel a query: SELECT pg_cancel_backend ( 1596183 ); Terminate a connection: SELECT pg_terminate_backend ( 1596183 ); Check connections per database: SELECT datname , count ( * ) FROM pg_stat_activity GROUP BY datname ORDER BY count ( * ) DESC ; Check connection limit: SELECT ( SELECT count ( * ) FROM pg_stat_activity ) AS used_connections , ( SELECT setting :: int FROM pg_settings WHERE name = 'max_connections' ) AS max_connections ; Final Thoughts When PostgreSQL has a connection or blocking problem, pg_stat_activity is one of the first places I check. But the most important skill is not knowing how to kill a PID. It is knowing which PID should be killed and why . A blocked query may only be a victim. An idle in transaction session may be the real blocker. And a high connection count may be caused by an application pool instead of PostgreSQL itself. A good production investigation should therefore follow this process: Observe ↓ Find the blocker ↓ Understand the transaction ↓ Mitigate the incident ↓ Fix the application ↓ Add protection and monitoring Commands like pg_cancel_backend() and pg_terminate_backend() are useful emergency tools. But the long-term solution is better transaction handling, good connection pool limits, proper timeouts, and enough monitoring to find the problem before production users notice it.