Implement parameter limit for Postgres backend with server-side binding enabled

[Craig Weber]

[NOTE:] Much of the context of the following report, as well as "Part 3" of the mitigation is a duplicate of #36248, but this ticket was accepted for "Part 1" and "Part 2" below, see triage decision.

Original report follows:

---

When deleting a model instance that has more than 65535 related objects via CASCADE, Django exceeds PostgreSQL's query parameter limit. This causes an OperationalError during the collection phase when Django checks for further related objects.

The issue occurs because:

1. PostgreSQL backend doesn't define max_query_params, so bulk_batch_size() returns unbounded batch sizes
2. When collecting related objects for deletion, Collector.get_del_batches() returns all objects in a single batch
3. The subsequent related_objects() query generates an IN clause with all PKs, exceeding the 65535 parameter limit

Django Version

Tested on Django 5.2 with psycopg 3.x

Database

PostgreSQL with psycopg3, which has a hard limit of 65535 query parameters when using server-side parameter binding.

Note: Django's default with psycopg3 is client-side binding (which avoids this limit by interpolating parameters into the SQL string). This bug manifests when server_side_binding=True is configured in database OPTIONS. Server-side binding is recommended for performance and is required for some features.

Reference: ​https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#server-side-binding

Steps to Reproduce

Minimal Example Models

# models.py
from django.db import models

class StockRecord(models.Model):
    sku = models.CharField(max_length=100)

class Line(models.Model):
    stockrecord = models.ForeignKey(
        StockRecord,
        on_delete=models.CASCADE,
        related_name='lines'
    )

class LineAttribute(models.Model):
    """
    Any model with a non-DO_NOTHING FK to Line prevents fast-delete.
    This forces Django to check for related LineAttributes when deleting Lines,
    which generates the query that exceeds the parameter limit.
    """
    line = models.ForeignKey(
        Line,
        on_delete=models.CASCADE,
        related_name='attributes'
    )
    name = models.CharField(max_length=100)
    value = models.CharField(max_length=100)

Database Configuration

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'OPTIONS': {
            'server_side_binding': True,  # Required to trigger this bug
        },
        # ... other settings
    }
}

Test Case

from django.test import TestCase
from .models import StockRecord, Line

class CascadeDeleteParameterLimitTest(TestCase):
    @classmethod
    def setUpTestData(cls):
        """Create one StockRecord with 66000 related Lines."""
        cls.stockrecord = StockRecord.objects.create(sku="TEST-SKU")

        # Create 66000 lines to exceed the 65535 parameter limit
        num_lines = 66000
        batch_size = 5000

        for batch_start in range(0, num_lines, batch_size):
            batch_end = min(batch_start + batch_size, num_lines)
            Line.objects.bulk_create([
                Line(stockrecord=cls.stockrecord)
                for _ in range(batch_end - batch_start)
            ])

    def test_cascade_delete_with_many_related_objects(self):
        """
        Deleting a StockRecord with 66000+ Lines fails with OperationalError.

        The CASCADE delete collects all 66000 Lines. Since Line has a reverse
        FK from LineAttribute (with CASCADE), Django cannot fast-delete and
        must check for related LineAttributes. This generates:

        SELECT * FROM lineattribute WHERE line_id IN ($1, $2, ..., $66000)

        This exceeds PostgreSQL's 65535 parameter limit.
        """
        self.assertEqual(Line.objects.count(), 66000)

        # This raises OperationalError: number of parameters must be between 0 and 65535
        self.stockrecord.delete()

        # If successful, all lines should be deleted via CASCADE
        self.assertEqual(Line.objects.count(), 0)

Exception Traceback

psycopg.OperationalError: sending query and params failed: number of parameters must be between 0 and 65535

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "test_models.py", line 28, in test_cascade_delete_with_many_related_objects
    self.stockrecord.delete()
  File "django/db/models/base.py", line 1280, in delete
    collector.collect([self], keep_parents=keep_parents)
  File "django/db/models/deletion.py", line 345, in collect
    on_delete(self, field, sub_objs, self.using)
  File "django/db/models/deletion.py", line 23, in CASCADE
    collector.collect(sub_objs, ...)
  File "django/db/models/deletion.py", line 343, in collect
    if getattr(on_delete, "lazy_sub_objs", False) or sub_objs:
  File "django/db/models/query.py", line 400, in __bool__
    self._fetch_all()
  ...
django.db.utils.OperationalError: sending query and params failed: number of parameters must be between 0 and 65535

Root Cause Analysis

Issue 1: PostgreSQL doesn't define max_query_params

In django/db/backends/base/features.py:

max_query_params = None  # No limit by default

Oracle and SQLite override this:

• Oracle: max_query_params = 2**16 - 1 (65535)
• SQLite: max_query_params = 999

PostgreSQL doesn't override it, so it remains None.

Issue 2: bulk_batch_size() returns unbounded size for PostgreSQL

In django/db/backends/base/operations.py:

def bulk_batch_size(self, fields, objs):
    return len(objs)  # Returns ALL objects - no batching

Oracle overrides this to respect max_query_params:

# django/db/backends/oracle/operations.py
def bulk_batch_size(self, fields, objs):
    """Oracle restricts the number of parameters in a query."""
    if fields:
        return self.connection.features.max_query_params // len(fields)
    return len(objs)

PostgreSQL doesn't override it, so Collector.get_del_batches() returns all objects in a single batch.

Issue 3: related_objects() query exceeds parameter limit

When Collector.collect() processes CASCADE-related objects, it must check for further related objects. The flow is:

1. StockRecord.delete() triggers collector.collect([stockrecord])
2. For the Line.stockrecord FK with CASCADE, Django calls CASCADE(collector, field, sub_objs, using)
3. CASCADE calls collector.collect(sub_objs) where sub_objs is the Lines QuerySet
4. Inside this nested collect(), Line cannot be "fast-deleted" because LineAttribute has a CASCADE FK pointing to Line
5. Django fetches all 66000 Lines into memory
6. For each reverse FK on Line, Django calls:

   batches = self.get_del_batches(new_objs, [field])  # Returns [[all 66000 lines]]
   for batch in batches:
       sub_objs = self.related_objects(related_model, [field], batch)
   

7. related_objects() generates a query with all PKs:

   SELECT * FROM lineattribute WHERE line_id IN ($1, $2, ..., $66000)
   

8. This query has 66000 parameters, exceeding the 65535 limit

Issue 4: Collector.delete() combines batched QuerySets with OR

Even after Parts 1+2 fix the SELECT queries during collection, the UPDATE phase for SET_NULL/SET_DEFAULT relations combines all batched QuerySets back together:

# django/db/models/deletion.py, lines 483-485
if updates:
    combined_updates = reduce(or_, updates)  # Combines ALL batches!
    combined_updates.update(**{field.name: value})

When a related model has on_delete=SET_NULL, Django collects the QuerySets in batches but then recombines them with OR before executing .update(). This negates the batching and can still exceed the parameter limit.

For example, if Line has a reverse relation from LogEntry.line with SET_NULL:

1. Deleting StockRecord cascades to 66000 Lines
2. Collection batches Lines properly (Part 1+2 fix)
3. Django needs to SET_NULL on LogEntry records pointing to those Lines
4. reduce(or_, updates) combines all batches into one QuerySet
5. The UPDATE query exceeds 65535 parameters

Suggested Fix

Part 1: Add max_query_params to PostgreSQL features

# django/db/backends/postgresql/features.py
class DatabaseFeatures(BaseDatabaseFeatures):
    max_query_params = 2**16 - 1  # PostgreSQL protocol limit

Part 2: Add bulk_batch_size() to PostgreSQL operations

# django/db/backends/postgresql/operations.py
from itertools import chain
from django.db.models import CompositePrimaryKey

def bulk_batch_size(self, fields, objs):
    """PostgreSQL has a 65535 parameter limit with server-side binding."""
    if self.connection.features.max_query_params is None:
        return len(objs)
    fields = list(
        chain.from_iterable(
            field.fields if isinstance(field, CompositePrimaryKey) else [field]
            for field in fields
        )
    )
    if fields:
        return self.connection.features.max_query_params // len(fields)
    return len(objs)

Part 3: Execute field updates per batch instead of combining with OR

# django/db/models/deletion.py, in Collector.delete()

# Instead of:
if updates:
    combined_updates = reduce(or_, updates)
    combined_updates.update(**{field.name: value})

# Do:
for qs in updates:
    qs.update(**{field.name: value})

This executes multiple UPDATE queries (one per batch) instead of combining them into a single query that exceeds the parameter limit.

Additional Notes

1. This issue manifests with psycopg3's server-side binding (server_side_binding=True). With psycopg2 or client-side binding (Django's default for psycopg3), parameters are interpolated into the SQL string, avoiding the limit.

2. The issue occurs when deleting an object that has many CASCADE-related objects, and those objects have further related objects that Django must check (any reverse FK with on_delete != DO_NOTHING). Common scenarios include:
   • E-commerce: Deleting a product/SKU with 100k+ order line items
   • Audit systems: Deleting entities with extensive audit trail records
   • Any model with self-referential FKs or related models that track metadata

3. Oracle already handles this correctly because it defines both max_query_params and overrides bulk_batch_size(). The same pattern should be applied to PostgreSQL.

4. Parts 1+2 can be implemented in a custom database backend, but Part 3 requires a Django core change to django/db/models/deletion.py. Without Part 3, the fix will still fail when SET_NULL or SET_DEFAULT relations exist on the cascaded objects.

[Jacob Walls]

Thanks for the report. The related object aspect (Part 3) is a duplicate of #36248, but I take your point that max_query_params shouldn't be unbounded on Postgres if you're using server-side cursors.

I'll re-scope your ticket to just Parts 1 & 2 of your suggested fix.