SQLInsertCompiler inherits from generic compiler, losing PostgreSQL UNNEST bulk insert optimization

[jp-hautin]

Summary

psqlextra.compiler.SQLInsertCompiler inherits from django.db.models.sql.compiler.SQLInsertCompiler (the generic base compiler) instead of django.db.backends.postgresql.compiler.SQLInsertCompiler (the PostgreSQL-specific one).

This causes all bulk inserts to fall back to standard INSERT ... VALUES syntax instead of using Django's optimized INSERT ... SELECT * FROM UNNEST(...) strategy, which was introduced to reduce query planning time for large bulk inserts.

Details

In psqlextra/compiler.py:

from django.db.models.sql import compiler as django_compiler

class SQLInsertCompiler(django_compiler.SQLInsertCompiler):
    ...

Django's PostgreSQL backend overrides assemble_as_sql() in django.db.backends.postgresql.compiler.SQLInsertCompiler to use UNNEST arrays instead of individual value placeholders. This optimization significantly reduces query planning time when inserting many rows, as PostgreSQL only sees one placeholder per column instead of N * columns.

Since psqlextra's SQLInsertCompiler inherits from the generic compiler, it never gets this override, and all bulk inserts generate:

INSERT INTO t (a, b) VALUES (1, 'x'), (2, 'y'), (3, 'z')

instead of:

INSERT INTO t (a, b) SELECT * FROM UNNEST(ARRAY[1,2,3]::int[], ARRAY['x','y','z']::varchar[])

The same issue applies to PostgresInsertOnConflictCompiler (line 163).

Suggested fix

Have SQLInsertCompiler inherit from the PostgreSQL-specific compiler:

from django.db.backends.postgresql.compiler import SQLInsertCompiler as BaseSQLInsertCompiler

class SQLInsertCompiler(BaseSQLInsertCompiler):
    ...

Note: simply copying the method reference doesn't work because assemble_as_sql uses super() internally, which resolves against the class hierarchy. The inheritance chain needs to be fixed.

Workaround

We currently monkeypatch assemble_as_sql onto psqlextra.compiler.SQLInsertCompiler at Django startup, reimplementing the UNNEST logic inline (without super()) to avoid MRO issues.

Environment

• Django 5.2
• django-postgres-extra 2.0.8