skip to content

Why can Django's QuerySet.iterator() fail behind a PostgreSQL pooler in transaction pooling mode, and what are the fixes Django documents?

level: seniorimportance: nice to knowfreq 22%

answer

  1. named cursor lives on one connection
  2. autocommit ends the transaction
  3. DISABLE_SERVER_SIDE_CURSORS
  4. a separate alias or atomic()

basics

~20 s

iterator() opens a server-side cursor that exists on one connection and outlives its autocommit transaction; a transaction pooler may send the next fetch elsewhere. Fix: disable server-side cursors, use a direct alias, or wrap in atomic().

solid answer

~50 s

On PostgreSQL, `iterator()` declares a **named server-side cursor**. Such a cursor exists only on the connection that created it, and because Django runs in autocommit, it is created to survive past the end of its transaction. A pooler in **transaction pooling** mode is free to hand each new transaction a different server connection, so a later fetch can land where the cursor is unknown and fail. Django's documented fixes: set `"DISABLE_SERVER_SIDE_CURSORS": True` in that entry of `DATABASES` (then `iterator()` behaves like a backend without streaming — the whole result arrives at once, though the result cache is still skipped); add a **second database alias** that connects directly or through session pooling and route big iterations there with `.using("direct")`; or wrap the loop in `transaction.atomic()`, which turns autocommit off so the cursor lives inside one transaction on one connection.

code

python · 18 lines
python
# settings.py
DATABASES = {
    "default": {  # through the transaction-pooling pooler
        "ENGINE": "django.db.backends.postgresql",
        "HOST": "pooler.internal",
        "NAME": "telemetry",
        "DISABLE_SERVER_SIDE_CURSORS": True,
    },
    "direct": {  # straight to PostgreSQL, for streaming jobs
        "ENGINE": "django.db.backends.postgresql",
        "HOST": "db.internal",
        "NAME": "telemetry",
    },
}

# in a batch job
# for row in Reading.objects.using("direct").values_list("pk", "value").iterator(chunk_size=5000):
#     ...

go deeper

for a junior

Recall that iterator() on PostgreSQL uses a server-side cursor that belongs to one database connection.

for a middle

Explain why autocommit plus transaction pooling breaks that cursor, and name the DISABLE_SERVER_SIDE_CURSORS option.

for a senior

Pick between disabling cursors, a direct alias and atomic() for each workload, and explain the long-transaction cost of the last.

for a principal

Design the connection topology so request traffic and batch jobs use different paths, each with the cursor behaviour it needs.

## Where the cursor lives When a Django QuerySet is consumed with `iterator()` on PostgreSQL, and the connection's `DISABLE_SERVER_SIDE_CURSORS` option is `False` (the default), Django opens a **named server-side cursor**. Rows stay on the database server and are pulled `chunk_size` at a time. Two facts combine into the failure: - **A server-side cursor belongs to one database connection.** Another connection cannot see it. - **Django runs in autocommit.** Outside an `atomic()` block, each statement commits immediately. Django creates the cursor so that it survives the end of the transaction that declared it, precisely so that later fetches in autocommit still work. ## What a transaction pooler changes A pooler in **transaction pooling** mode assigns a real server connection to a client **only for the duration of a transaction**. When the transaction ends, that connection may go to another client, and the next transaction from the same Django process may get a different one. So: 1. Django declares the cursor; the pooler runs it on server connection A; autocommit ends the transaction. 2. Django asks for the next chunk; the pooler routes it to server connection B. 3. Connection B has no such cursor, and the fetch fails with an error saying the cursor does not exist. It often works in development (no pooler, or a single connection) and fails only under production load, which is why it shows up as an interview scenario. ## The documented fixes | Fix | How | Trade-off | |---|---|---| | Disable server-side cursors | `"DISABLE_SERVER_SIDE_CURSORS": True` in that `DATABASES` entry | `iterator()` no longer streams from the server; the driver receives the whole result, though the QuerySet cache is still skipped | | Separate alias | a second `DATABASES` entry that connects directly or through a **session**-pooling pooler; `.using("direct").iterator()` | extra configuration and connections, but full streaming for big jobs | | Wrap in a transaction | `with transaction.atomic(): for r in qs.iterator(): ...` | the cursor lives inside one transaction, so the pooler keeps one connection; the transaction stays open for the whole loop | ## Choosing among them - **Web requests behind the pooler** rarely need streaming; disabling server-side cursors on the pooled alias is the simplest safe default. - **Batch jobs and exports** benefit from a dedicated alias with a direct connection, so they stream without affecting the pooled traffic. - **`atomic()`** is the quick fix for a single job, but a long-running transaction holds a snapshot and a server connection for its full length; keep the loop body fast, and avoid writes that would be rolled back together if the loop fails near the end. When none suits — for example a very long export — batching by primary key (`filter(pk__gt=last_pk).order_by("pk")[:n]`) avoids server-side cursors altogether and works through any pooler. ## Related PostgreSQL settings - Since Django 5.1 the PostgreSQL backend can use **psycopg connection pools** configured under `OPTIONS["pool"]`; that is an in-process pool, not the external transaction pooler described here. - PostgreSQL plans cursor queries expecting only part of the result to be read; a full scan through a cursor may be planned less efficiently than the same query run directly. ## Diagnosing it step by step 1. Reproduce with the same connection path as production: through the pooler, under concurrent load. 2. Check the `DATABASES` entry the code uses: is `DISABLE_SERVER_SIDE_CURSORS` set, and does `HOST` point at the pooler or the database? 3. Confirm the pooler's mode; session pooling keeps a client on one server connection and does not trigger the problem. 4. Look for `iterator()` calls, including ones hidden in helpers, exports and admin actions. 5. Apply one of the three documented fixes per call site, and add a test or smoke check that runs an `iterator()` loop through the pooled alias. ## How to recognise it - The missing-cursor errors appear only in `iterator()` loops, not in ordinary queries. - They correlate with load, because under light load the pooler often hands back the same connection. - The same code works with a direct database connection.

  • Does setting DISABLE_SERVER_SIDE_CURSORS to True make iterator() pointless?
    Not entirely. The database driver then receives the whole result, so server-side streaming is gone, but `iterator()` still skips the QuerySet result cache and converts rows chunk by chunk, so Django does not hold every model instance at once.
  • Why does wrapping the loop in transaction.atomic() fix the pooler problem?
    Inside `atomic()` autocommit is off, so the cursor's whole life happens within one transaction. A transaction-pooling pooler keeps a client on the same server connection for the length of a transaction, so every fetch reaches the connection that owns the cursor.

saying these in an interview costs you the question

  • Blames the pooler's pool size rather than cursor-to-connection affinity
  • Believes DISABLE_SERVER_SIDE_CURSORS is a global setting outside DATABASES
  • Thinks Django 5.1's psycopg pool is a transaction-pooling proxy
  • Assumes atomic() has no cost for a long export loop
  • Expects the error to appear on ordinary queries as well