Postgres idle in transaction: find the owner and fix it

A Postgres session that is idle in transaction still has an open transaction, even though it is not executing a query. Find its application owner, check whether it blocks another session, and close the transaction through the application’s intended commit or rollback path. Increasing the connection pool does not repair the transaction lifetime ^[1][4][5].

Which session owns the work that stopped?#

Start from the session holding the transaction, not just the request waiting behind it. A slow update may be waiting for another writer rather than consuming database CPU. PostgreSQL reports activity state and wait events separately. An active backend can therefore be waiting, while an idle backend can still be inside an unfinished transaction ^[1].

Find the request, worker or interactive session that acquired the connection. Ask what it did before waiting and which code path must end its transaction. Keep the database process ID alongside the application instance and trace identifier. An application name alone may identify a service but still leave many concurrent owners indistinguishable.

Use the following observation query from a separate administrative connection. It combines transaction age, time in the current state and lock blockers. It is a diagnostic sample for PostgreSQL 18, not a continuously polling monitoring agent ^[1][2]. Run it in autocommit mode so repeated observations do not remain in the same statistics snapshot.

SELECT pid, application_name, usename,
       state, wait_event_type, wait_event,
       clock_timestamp() - xact_start AS transaction_age,
       clock_timestamp() - state_change AS state_age,
       pg_blocking_pids(pid) AS blocking_pids,
       left(query, 240) AS last_or_current_query
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid()
  AND (state LIKE 'idle in transaction%'
       OR wait_event_type = 'Lock')
ORDER BY xact_start NULLS LAST;

Read a nonempty blocking-pid array as a lead to inspect those sessions. It can contain duplicate process IDs, and a prepared transaction can be represented by a zero rather than an ordinary backend PID ^[2]. Do not feed the array into a termination script. An incident observation should narrow ownership, not turn partial evidence into a bulk action.

Visibility also matters. An ordinary database role may not see another role’s full query text; PostgreSQL documents the additional visibility available through its statistics-reading role ^[1]. Null fields do not establish that no statement ran. Use your approved operational access rather than granting the application broad monitoring privileges to make this query easier.

How can a finished update block the next writer?#

Scenario: an illustrative reservation service decrements stock, then waits for a remote quote before committing. Another request updates the same stock row. The first SQL statement has finished, but its transaction has not. The second writer waits for the transaction boundary. AWS documents this distinction for row-lock contention in RDS for PostgreSQL ^[7]. The example below uses a disposable table, not a production stock record.

Open separate SQL sessions to the same throwaway database. Run the setup with autocommit enabled. In the first session, begin a transaction and update the sample row, leaving that transaction open. Then attempt the second update from the other session. These are proposed laboratory steps; no database experiment was executed for this article.

-- Disposable database only. Run once with autocommit enabled.
CREATE TABLE editorial_idle_lab (
  sku text PRIMARY KEY,
  available integer NOT NULL
);
INSERT INTO editorial_idle_lab VALUES ('demo-item', 5);

-- Session A: leave this transaction open temporarily.
BEGIN;
UPDATE editorial_idle_lab
SET available = available - 1
WHERE sku = 'demo-item';

-- Session B, connected to the same disposable database.
BEGIN;
SET LOCAL lock_timeout = '5s';
UPDATE editorial_idle_lab
SET available = available - 1
WHERE sku = 'demo-item';
-- If the lock timeout fires, this transaction needs rollback.
ROLLBACK;

Scenario: while the second statement waits, use a third connection for the observation query. Expect the first session to be idle in transaction and the waiting writer to identify a lock blocker. The illustrative five-second lock timeout bounds the experiment; it does not choose a production policy. After the waiting statement errors, explicitly roll its transaction back before reusing that connection.

The last displayed query in the idle session is a trap. It names work that already ran, not necessarily work currently running ^[1]. Searching for a slow execution plan for that update can miss the application call that came afterward. Correlate the request trace with the open-transaction interval. In this scenario the remote quote wait belongs outside the database transaction unless the business design explicitly requires that lock.

Finish the laboratory by verifying that no test transaction remains open, then drop the disposable table. Do not interpret one successful second update as proof that your real application handles cancellations or exceptions. It establishes the lock mechanism in the lab; the application lifetime still needs a separate test.

-- Session A: end its open transaction after observing Session B.
ROLLBACK;
-- Only after Session B also rolled back, in the disposable database:
DROP TABLE editorial_idle_lab;

Where should the application close its transaction?#

The application should have one clear owner from connection checkout through transaction completion and client release. With node-postgres, all statements in a transaction must run on the same client instance; separate calls through the pool’s convenience query method do not supply that ownership ^[4]. This is a lifecycle rule, not a reason to abandon pooling.

Write the normal path before adding retries: acquire a client, begin, perform the database work, commit, and return the client. Then review every exit from that path. A validation failure, thrown exception or aborted request must still end the transaction and return or discard the client. Returning a connection and finishing its transaction are separate obligations.

1. Move remote work before acquisition where possibleFetch the quote before opening the transaction. If the quote can become stale, carry its version or expiry into the later database check; moving work outside a transaction does not magically preserve its assumptions.
2. Keep one client for the database unitRun BEGIN, the row checks and changes, and COMMIT through the acquired client. Keep the checkout owner visible in application traces ^[4].
3. Make failure cleanup explicitOn failure before a confirmed commit, attempt rollback on that same client. If cleanup cannot establish a usable connection, discard it through the driver’s documented lifecycle rather than returning uncertain state to another request.
4. Release exactly onceReview returns, throws and cancellation paths. A checked-out client must not remain unavailable to the pool after its owner finishes. The pool documentation explains why a missing release can exhaust it ^[5].

This is our review sequence, not a transaction abstraction supplied by a driver. It deliberately leaves the application’s retry and business semantics visible. Our transactional outbox guide addresses a related boundary: committing database state and later delivering an external event. Sending an HTTP request inside an open transaction does not make that request part of the database’s atomic commit.

Treat commit acknowledgement loss separately from a known rollback. If the connection breaks while commit is being acknowledged, a generic catch-and-retry wrapper may not know the business outcome. Use the operation’s stable identifier and reconciliation path to establish what happened. The appropriate recovery depends on your schema and idempotency contract; this article does not supply a universal retry helper.

Should you cancel, terminate or set a timeout?#

Choose the intervention by what it acts on. PostgreSQL’s cancellation function targets a session’s current query; its termination function ends the session ^[6]. A session waiting for another client command may have no running statement to cancel. Use the application owner to finish or roll back work when that path remains available.

Do not copy a timeout value from an unrelated workload. Choose a role or session policy from the longest legitimate pause, and rehearse what the client sees when the server closes its session. The idle-in-transaction timeout is disabled when its value is zero ^[3]. A guardrail can contain a future leak, but an application that keeps leaking transactions still needs repair.

Before a session termination, recheck the process ID, transaction start, application identity and owner. Process identifiers are observations, not durable business identifiers. Record why interruption is acceptable and who will reconcile its work. PostgreSQL restricts signaling functions by role and privilege; a successful signal result also should not replace verifying the resulting state ^[6].

What proves the leak is repaired?#

Rehearse successful work, a statement error, a failure between statements, request cancellation and connection loss. For each path, record whether the business operation committed, whether a transaction stayed open, whether the checkout returned, and whether a new request could proceed. Do not reduce those observations to a single “database healthy” check.

Keep separate evidence for lock waits and checkout waits. The observation query can find a database blocker; it cannot identify a request waiting inside an application pool that has not acquired a connection yet. Correlate pool acquisition timing with database state. If the blocked writer disappears but acquisition still stalls, investigate retained clients and concurrent work before declaring success.

The multi-tenant row-level security guide discusses another reason to make transaction scope explicit: request context must belong to the intended database work. For this incident, the acceptance record should name the responsible code path, its cleanup result and the remaining deliberate long transactions. Restarting the service can clear the symptom. The cleanup results from those failure paths establish whether the connection lifetime was repaired.

Frequently asked questions#

Is an idle connection the same as idle in transaction?

No. PostgreSQL distinguishes waiting for a new command outside a transaction from waiting inside an unfinished transaction. Inspect the state and transaction start together; an ordinary reusable pooled connection can be idle without owning an open transaction ^[1].

Will a larger connection pool remove the blocker?

A larger pool does not close the transaction holding a conflicting lock. It may let additional work enter, but you still need to identify and finish the owning transaction. Measure application checkout waits separately from database lock waits ^[5][7].

Why did cancelling the query leave the problem?

Cancellation targets the current query. An idle transaction may be waiting for the client rather than executing a statement, so the application still needs to end that transaction. Session termination is a different intervention with different effects and privileges ^[6].

Should every transaction use the same idle timeout?

Choose the timeout from legitimate application behavior and rehearse session loss. Interactive work and short API transactions may have different acceptable pauses. A scoped policy is easier to reason about than a value copied across unrelated workloads ^[3].