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.
-
VACUUMmay 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.
Top comments (0)