The Problem: Why Server-Side Cursors Break Under Connection Pooling
When you call QuerySet.iterator() on a PostgreSQL-backed Django model, Django does not fetch all rows into memory at once. Instead, it opens a server-side (named) cursor with a statement like DECLARE <name> NO SCROLL CURSOR WITHOUT HOLD FOR ..., then retrieves rows from the database in controlled batches as your code consumes them.
This is efficient by design: the result set lives on the PostgreSQL server, and rows are streamed to the client only as needed, keeping application memory usage low even against tables with tens of millions of rows.
The trouble starts when a connection pooler such as PgBouncer sits between Django and PostgreSQL in transaction pooling mode. Server-side cursors are tied to the physical database connection that declared them. In transaction pooling, PgBouncer may hand a different physical connection to the next statement in your "logical" session, so when Django tries to FETCH more rows from a cursor declared on a connection it no longer holds, PostgreSQL raises a cursor does not exist error.
This is not a Django bug -- it is a fundamental consequence of how connection pooling and named cursors interact:
- Named (server-side) cursors are scoped to one physical connection.
- Transaction pooling reassigns physical connections between logical transactions.
- A cursor opened in transaction A may not exist when transaction B tries to fetch from it.
The Standard Fix: DISABLE_SERVER_SIDE_CURSORS
Django ships a dedicated setting for exactly this scenario. Adding it to your DATABASES configuration forces .iterator() to fall back to ordinary client-side fetching instead of issuing DECLARE ... CURSOR, with no changes required to any query or model code.
DATABASES = {
"default": {
"ENGINE": "django.db.backends.postgresql",
"NAME": "mydb",
"USER": "myuser",
"PASSWORD": "mypassword",
"HOST": "pgbouncer-host",
"PORT": "6432",
"DISABLE_SERVER_SIDE_CURSORS": True,
}
}
Making It Environment-Driven
Hardcoding True works, but most production setups want this toggled per environment (e.g., off for a direct DB connection used for batch jobs, on for the pooled web-facing connection):
import os
DATABASES["default"]["DISABLE_SERVER_SIDE_CURSORS"] = (
os.getenv("DISABLE_SERVER_SIDE_CURSORS", "True").lower() == "true"
)
If you use dj-database-url, pass it as a keyword argument to config() rather than embedding it in the URL query string -- it is not a connection OPTIONS value, so it will not work as a URL parameter:
import os
import dj_database_url
DATABASES["default"] = dj_database_url.config(
default=os.getenv("DATABASE_URL"),
conn_max_age=600,
disable_server_side_cursors=os.getenv(
"DISABLE_SERVER_SIDE_CURSORS", "True"
).lower() == "true",
)
The Hidden Cost: Memory Pressure After Disabling Cursors
Disabling server-side cursors solves the pooling error, but it reintroduces the exact problem cursors were meant to prevent: without a cursor to stream from, .iterator() must pull data from the database driver in memory-bound batches rather than true server-side streaming. If application code additionally bypasses .iterator() entirely -- e.g., plain for obj in Model.objects.all(): -- the default QuerySet caches every fetched row internally, causing memory usage to climb steadily as the loop progresses, which is disastrous against multi-million-row tables.
The fixes below are ordered from simplest to most robust.
Best Practices for Memory-Safe Iteration
1. Always Use .iterator() with an Explicit chunk_size
Never iterate a large queryset directly. .iterator() prevents the internal result cache from growing unbounded, and chunk_size controls exactly how many rows are held in memory per database round trip. If omitted, Django defaults to 2000, which may be too large for wide rows.
from myapp.models import Order
for order in Order.objects.filter(status="pending").iterator(chunk_size=1000):
process(order)
2. Prefer .values() / .values_list() When Full Model Instances Are Not Needed
Instantiating full ORM model objects for every row is often the largest memory cost, not the raw row data itself. Skip it when you only need specific fields.
for order_id, total in (
Order.objects.filter(status="pending")
.values_list("id", "total")
.iterator(chunk_size=2000)
):
process(order_id, total)
3. Turn Off DEBUG in Production
With DEBUG=True, Django logs every SQL statement into connection.queries. Over a long-running iteration, that log itself becomes a memory leak, independent of your queryset logic.
4. Avoid OFFSET-Based Pagination at Scale
Slicing a queryset (queryset[start:start+chunk]) forces PostgreSQL to scan and discard all preceding rows before returning the requested page, making each successive page slower as OFFSET grows.
5. Use Primary-Key (Keyset) Chunking Instead
Filtering by pk__gt=last_seen_pk keeps every page's cost constant regardless of table size, and avoids relying on server-side cursors altogether -- making it safe under PgBouncer transaction pooling without any special settings.
def chunked_queryset_iterator(queryset, chunk_size=1000):
"""Iterate a large queryset in fixed-size, memory-safe chunks
using primary-key keyset pagination (no OFFSET, no server-side cursor)."""
pk = 0
base_qs = queryset.order_by("pk")
while True:
chunk = list(base_qs.filter(pk__gt=pk)[:chunk_size])
if not chunk:
break
for obj in chunk:
yield obj
pk = chunk[-1].pk
for order in chunked_queryset_iterator(
Order.objects.filter(status="pending"), chunk_size=1000
):
process(order)
6. Route True Streaming Workloads Through a Direct Connection
If specific batch or reporting jobs genuinely need server-side cursor streaming (minimal memory, true DB-side pointer), define a second database alias that bypasses the pooler -- either a direct connection to PostgreSQL or a pooler in session pooling mode -- and keep server-side cursors enabled only there.
DATABASES = {
"default": {
# Routed through PgBouncer in transaction pooling mode
"ENGINE": "django.db.backends.postgresql",
"HOST": "pgbouncer-host",
"PORT": "6432",
"DISABLE_SERVER_SIDE_CURSORS": True,
},
"direct": {
# Direct to PostgreSQL, or pooler in session pooling mode
"ENGINE": "django.db.backends.postgresql",
"HOST": "postgres-host",
"PORT": "5432",
"DISABLE_SERVER_SIDE_CURSORS": False,
},
}
Order.objects.using("direct").filter(
status="pending"
).iterator(chunk_size=1000)
Alternatively, Django's docs note that wrapping the queryset iteration in an explicit transaction.atomic() block also confines the server-side cursor's lifetime to that transaction, which can be sufficient if your pooler setup allows it.
Choosing the Right Approach
| Technique | Memory profile | Pooler-safe | Best for |
|---|---|---|---|
Plain queryset loop (no .iterator()) |
Worst -- unbounded cache growth | Yes | Never use on large tables |
.iterator(chunk_size=N), cursors disabled |
Moderate | Yes | Default choice behind PgBouncer transaction pooling |
.iterator(chunk_size=N), server-side cursors enabled |
Best (true streaming) | No (needs session pooling or direct connection) | Batch/ETL jobs on a dedicated connection |
| PK-based (keyset) manual chunking | Best, constant per page | Yes | Very large tables, ordered processing, exports |
Django Paginator
|
Good | Yes | Admin UIs, user-facing paginated views |
Summary of Recommended Configuration
import os
import dj_database_url
DATABASES = {
"default": dj_database_url.config(
default=os.getenv("DATABASE_URL"),
conn_max_age=600,
disable_server_side_cursors=os.getenv(
"DISABLE_SERVER_SIDE_CURSORS", "True"
).lower() == "true",
),
}
# Usage in application code
for obj in MyModel.objects.filter(active=True).iterator(chunk_size=1000):
handle(obj)
This combination -- environment-driven cursor disabling for pooled connections, explicit chunk_size on every .iterator() call, and PK-based chunking for the largest workloads -- covers the vast majority of production Django deployments running behind PgBouncer in transaction pooling mode.
Top comments (1)
Nice article!