PostgreSQL Cheat Sheet: Investigating and Terminating Database Blocking Sessions

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.

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.