Query completes on Azure PostgreSQL but client keeps running (state = idle in transaction / ClientRead)

Question

Query completes on Azure PostgreSQL but client keeps running (state = idle in transaction / ClientRead)

Anna 0

Environment

Service: Azure Database for PostgreSQL – Flexible Server (General Purpose, D2ds_v5, 2 vCores, 8 GiB RAM, 1024 GiB storage)
Client OS: Windows 11 / WSL 2
PostgreSQL version: 15.12

Problem

I connect from PyCharm and run fairly simple DDL / DML statements against a large table (~28 GB). While the query is active:

Portal metrics show CPU at 70–80 %.

After < 10 minutes the CPU drops to ~2 %, suggesting the server has finished its part.

In pg_stat_activity the backend transitions from active ➝ idle in transaction with wait_event = ClientRead, then either sits there for a long time or disappears altogether.

Meanwhile the Python process never returns (no error, just hangs); I eventually have to stop it manually.
I reproduced the exact same pattern in DataGrip: the query hangs indefinitely, yet once CPU falls to ~2 % the new table already shows up in the schema browser, confirming the server work is done.

What I’ve ruled out

No blocking locks (pg_locks, pg_blocking_pids() are empty).

Nothing new appears in pg_stat_statements or any pg_stat_progress_* view.

The same query completes instantly on a smaller subset of the table (~17 GB).

The table has been vacuumed and analyzed (VACUUM ANALYZE).

Questions

Why does the server report the session as idle / finished while the client still thinks it is fetching data?
Could result-set size, TCP keep-alive settings, or Azure gateway time-outs be responsible?
Which PostgreSQL or Flexible Server parameters might explain this behaviour?

Any insights or similar experiences would be greatly appreciated—especially ways to detect sooner that the backend has already sent all rows.

Mahesh Kurva 4,705 Reputation points Microsoft External Staff Moderator

2025-06-11T10:08:20.6266667+00:00

Hi Anna

Greetings!!

Could you please share the details requested in the private message?
Narendra Pakkirigari 475 Reputation points Microsoft External Staff Moderator

2025-06-16T08:36:47.5766667+00:00

Hi Anna,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Anna 0 Reputation points

2025-06-17T11:10:21.4366667+00:00
Hi @Narendra Pakkirigari

Thank you for the detailed explanation and suggestions. We were able to solve the issue.

To follow up:

As you suggested, we tested the same query using Azure Cloud Shell to rule out local environment factors (e.g., WSL2). The behavior was similar:

The query executed and the table appeared on the server.

CPU usage dropped (from ~80% to ~30% in this case).

The session transitioned to idle.

Eventually, Azure Cloud Shell disconnected, and the query never returned a result or error.

We experimented with the idle_in_transaction_session_timeout, tcp_keepalives_idle, tcp_keepalive_count parameters:

When set to a shorter duration, the session is terminated correctly (tcp_keepalives_idle: 120, tcp_keepalives_count: 9).

When increased to higher values, the original issue persists—the server finishes processing, but the Python process never receives a response and hangs indefinitely (tcp_keepalives_idle: 300, tcp_keepalives_count: 10).

We are continuing to explore tuning options to better understand this behavior. In particular, we would appreciate any insights into why increasing the keep-alive timeouts appears to cause the query to hang indefinitely, rather than simply extending the allowable duration.

Accepted answer

0 additional answers

Your answer

Mahesh Kurva 4,705 Reputation points Microsoft External Staff Moderator

2025-06-11T10:08:20.6266667+00:00

Hi Anna

Greetings!!

Could you please share the details requested in the private message?
Narendra Pakkirigari 475 Reputation points Microsoft External Staff Moderator

2025-06-16T08:36:47.5766667+00:00

Hi Anna,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Anna 0 Reputation points

2025-06-17T11:10:21.4366667+00:00

Hi @Narendra Pakkirigari

Thank you for the detailed explanation and suggestions. We were able to solve the issue.

To follow up:

As you suggested, we tested the same query using Azure Cloud Shell to rule out local environment factors (e.g., WSL2). The behavior was similar:

The query executed and the table appeared on the server.

CPU usage dropped (from ~80% to ~30% in this case).

The session transitioned to idle.

Eventually, Azure Cloud Shell disconnected, and the query never returned a result or error.

We experimented with the idle_in_transaction_session_timeout, tcp_keepalives_idle, tcp_keepalive_count parameters:

When set to a shorter duration, the session is terminated correctly (tcp_keepalives_idle: 120, tcp_keepalives_count: 9).

When increased to higher values, the original issue persists—the server finishes processing, but the Python process never receives a response and hangs indefinitely (tcp_keepalives_idle: 300, tcp_keepalives_count: 10).

We are continuing to explore tuning options to better understand this behavior. In particular, we would appreciate any insights into why increasing the keep-alive timeouts appears to cause the query to hang indefinitely, rather than simply extending the allowable duration.

Answer 1

Hi Anna,

The issue is because the server finishing its job, but the client like Python in PyCharm or DataGrip hanging while it waits to receive the results. On the server side, PostgreSQL switches to an “idle in transaction” state with a wait_event = ClientRead. It means that PostgreSQL is done processing the query and is now waiting for the client to read the data or send the next command.

The problem only shows up when working with a large table (about 28 GB), and not with smaller subsets (like 17 GB). That points to the issue related to how large result sets are handled especially when sent over the network. If the client can’t keep up or doesn’t start reading the data quickly enough, the connection appears to be idle as the server simply waits.

You've already checked for common issues like locking or slow queries using tools like pg_locks, pg_blocking_pids(), and pg_stat_statements, which is good. This confirms that the problem isn't with how the query runs on the server, but instead with how the client is handling the data after the query finishes.

Try with the PostgreSQL parameters like idle_in_transaction_session_timeout to automatically close sessions that sit idle for too long in a transaction. It also helps to adjust TCP keep-alive settings (tcp_keepalives_idle, tcp_keepalives_interval, tcp_keepalives_count) so that connections don’t silently hang when there’s no activity.

On the client side, it’s best to avoid queries like INSERT ... RETURNING * on huge tables, reduce the fetch size (especially in tools like DataGrip), and always make sure you explicitly commit or close your transactions. If possible, try running the same query outside of WSL2 on native Windows or using Azure Cloud Shell—to see if the issue is tied to the WSL2 environment.

Share via

Query completes on Azure PostgreSQL but client keeps running (state = idle in transaction / ClientRead)

0 additional answers

Your answer