DatabaseOperations.bulk_batch_size() is overly protective on SQLite in most cases

[Sarah Boyce]

DatabaseOperations.bulk_batch_size() for SQLite is dependent on DatabaseFeatures.max_query_params which is currently set as 999.

However, for SQLite versions >= 3.32, this limit is increased to 32,766. See: ​https://www.sqlite.org/limits.html#max_variable_number

SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999 for SQLite versions prior to 3.32.0 (2020-05-22) or 32766 for SQLite versions after 3.32.0.

This means that is some cases (assuming there isn't another database limit being hit), we can increase the maximum batch size for SQLite.

For illustration purposes, on SQLite >= 3.32, if the value of DatabaseFeatures.max_query_params is increased, the following test would pass:

tests/bulk_create/tests.py

@@ -286 +286 @@
         objs = [Country(name=f"Country {i}") for i in range(1000)]
         fields = ["name", "iso_two_letter", "description"]
         max_batch_size = max(connection.ops.bulk_batch_size(fields, objs), 1)
-        with self.assertNumQueries(ceil(len(objs) / max_batch_size)):
+        with self.assertNumQueries(1):
             Country.objects.bulk_create(objs, batch_size=max_batch_size + 1)
 
     @skipUnlessDBFeature("has_bulk_insert")

PR discussion references:

• ​https://github.com/django/django/pull/19088#discussion_r1925652660
• ​https://github.com/django/django/pull/19088#discussion_r1929940327

Ticket which sparked the discussion/discovery: #36118