Fix Idle in Transaction Connections from Django

Problem statement

pg_stat_activity shows connections in the state idle in transaction, some for minutes or hours. Around the same time, migrations hang waiting for locks, VACUUM cannot clean dead rows so tables bloat, updates to certain rows block, or the database runs out of connections.

A session is idle in transaction when it has started a transaction (BEGIN), run some statements, and is now waiting for the client to send the next command or COMMIT. While it waits, it holds its locks and pins an old snapshot that prevents vacuum from removing dead rows. In Django projects, the client doing the waiting is usually your own code: a transaction wrapped around something slow, a Celery task, a script, or an interactive shell. This guide finds the source and removes it.

1. List the sessions: SELECT pid, now() - xact_start, application_name, client_addr, query FROM pg_stat_activity WHERE state LIKE 'idle in transaction%';.
2. Give each process type its own application_name in DATABASES["default"]["OPTIONS"] so you can tell web, Celery, and scripts apart.
3. Look at the query column: it is the last statement run, which points at the code path.
4. Fix the code: keep transaction.atomic() blocks short and never wrap network calls, file processing, or sleeps in them; avoid ATOMIC_REQUESTS for slow views.
5. Set idle_in_transaction_session_timeout (for example 60 seconds) as a safety net.
6. Terminate offending sessions only after identifying them: SELECT pg_terminate_backend(pid);.

Step-by-step solution

1. Find the sessions

SELECT pid,
       now() - xact_start  AS xact_age,
       now() - state_change AS idle_for,
       usename, application_name, client_addr,
       left(query, 100) AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY xact_start;

• xact_age is how long the transaction has existed; idle_for how long since its last statement.
• idle in transaction (aborted) means a statement failed and the client has not rolled back yet.
• last_query is the most recent statement, not what the session is doing now (it is doing nothing).

2. Label your connections

Without labels, every Django process shows up as the same user from the same host. Set application_name per process type:

# settings.py
import os
import sys

APP_ROLE = os.environ.get("APP_ROLE", "web")

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        # ...
        "OPTIONS": {
            "application_name": f"myapp-{APP_ROLE}",
        },
    }
}

Set APP_ROLE=celery, APP_ROLE=beat, or APP_ROLE=cron in each service's environment. application_name is a standard libpq connection parameter, so it appears in pg_stat_activity and in PostgreSQL logs.

3. Recognise common Django causes

A transaction around slow work:

# Bad: the transaction stays open during an HTTP call
with transaction.atomic():
    order = Order.objects.select_for_update().get(pk=order_id)
    response = payment_gateway.capture(order.payment_id)   # 5 seconds, or 60 on timeout
    order.status = "paid"
    order.save()

# Better: lock, update state, commit; do the slow call outside
with transaction.atomic():
    order = Order.objects.select_for_update().get(pk=order_id)
    order.status = "capturing"
    order.save(update_fields=["status"])

response = payment_gateway.capture(order.payment_id)

with transaction.atomic():
    order = Order.objects.select_for_update().get(pk=order_id)
    order.status = "paid" if response.ok else "capture_failed"
    order.save(update_fields=["status"])

The same applies to sending email, calling other internal services, uploading to S3, or generating a PDF inside atomic().

ATOMIC_REQUESTS = True wraps every view in a transaction, including the time spent calling APIs and rendering templates. For slow views, that is exactly the pattern above. Disable it globally and use atomic() where needed, or mark specific views with @transaction.non_atomic_requests.

Celery tasks that open atomic() and then loop over many items with network calls inside. Commit in batches, or restructure so each item is its own short transaction.

Streaming responses that iterate a queryset inside an open transaction while the client downloads slowly. Fetch data before streaming, or stream outside atomic().

Interactive sessions: a psql session or python manage.py shell where someone ran BEGIN or entered an atomic() block and went to lunch. application_name shows psql or your shell's label.

Raw psycopg code in scripts without autocommit: psycopg opens a transaction implicitly on the first statement when autocommit is off, and it stays open until commit() or rollback(). Django's own connections use autocommit outside atomic(), but code that creates its own psycopg.connect() does not by default.

4. Terminate what you have identified

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '10 minutes'
  AND application_name = 'myapp-cron';

The transaction rolls back, releasing its locks. Make sure the work in it can be lost or will be retried.

5. Add a server-side safety net

ALTER DATABASE myapp SET idle_in_transaction_session_timeout = '60s';

New sessions then have their transaction and connection terminated if they sit idle inside a transaction for more than 60 seconds. Django sees a closed connection on its next query and raises an error, which surfaces the bug instead of silently holding locks. Choose a value longer than any legitimate pause inside a transaction.

PostgreSQL 17 adds transaction_timeout, which limits total transaction duration regardless of activity.

On managed services, set these through parameter groups or database flags.

Verification check:

SELECT application_name, count(*), max(now() - xact_start) AS oldest
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
GROUP BY application_name;
SHOW idle_in_transaction_session_timeout;

Sessions that appear should be rare and short-lived, and the timeout should show your configured value (from a new connection).

Explanation

PostgreSQL cannot know that a client is merely slow rather than finished; an open transaction keeps its locks and its snapshot until the client commits or rolls back. Snapshots matter beyond locking: rows deleted or updated after the snapshot was taken cannot be vacuumed while it exists, so a single forgotten transaction can make busy tables grow for hours. In Django, the fix is almost always structural: keep database transactions around database work only, and let slow operations happen between short transactions.

Edge cases / notes

Connection poolers

With PgBouncer in transaction mode, an idle-in-transaction client also holds a server connection, which reduces the pool for everyone. PgBouncer's idle_transaction_timeout provides the same safety net at the pooler.

Persistent connections

CONN_MAX_AGE keeps connections open between requests, but they sit in state idle, not idle in transaction. Persistent connections alone do not cause this problem.

When to automate this

Alert when any session has been idle in transaction for more than a minute, including its application_name in the alert. Add a code review rule (or a lint check) against network calls inside transaction.atomic() blocks in services known to be sensitive.

Next steps

• See how idle transactions block schema changes in Fix Django Migrations Hanging on a Database Lock.
• If connections run out as a result, see Fix PostgreSQL "Too Many Connections" in Django.
• Understand the vacuum impact with Tune PostgreSQL Autovacuum for Django.
• Limit active queries too with Set PostgreSQL Statement Timeouts for Django.

FAQ

What does "idle in transaction" mean in PostgreSQL?

A session started a transaction and is waiting for the client's next command. It keeps its locks and snapshot until it commits or rolls back.

Why does Django leave connections idle in transaction?

Usually because code does slow work (network calls, file processing) inside transaction.atomic(), or ATOMIC_REQUESTS wraps slow views. Scripts using raw psycopg without autocommit also cause it.

Is it safe to set idle_in_transaction_session_timeout?

Yes, with a value longer than any legitimate pause in a transaction. Sessions that exceed it are terminated and their transactions rolled back.

How do I tell which Django process owns a stuck transaction?

Set application_name per process type in DATABASES["default"]["OPTIONS"] and read it in pg_stat_activity.