# Speeding up Postgres bulk\_create by using unnest

**URL:** <https://forum.djangoproject.com/t/speeding-up-postgres-bulk-create-by-using-unnest/36508>\
**Category:** ORM\
**Created:** [November 17, 2024, 9:40pm UTC](https://forum.djangoproject.com/t/speeding-up-postgres-bulk-create-by-using-unnest/36508 "2024-11-17T21:40:31Z")\
**Posts on this page:** 1\
**Showing post:** 1

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [November 17, 2024, 9:40pm UTC](https://forum.djangoproject.com/t/speeding-up-postgres-bulk-create-by-using-unnest/36508/1 "2024-11-17T21:40:32Z")

</div>

Through some reading last evening I stumbled upon an article that [described a technique that allegedly sped up large INSERT performance by 50% on Postgres](https://www.timescale.com/blog/boosting-postgres-insert-performance/).

The technique revolves around changing the `INSERT` statement from

```sql
INSERT INTO table (f0, ..., fn)
VALUES (f0_v0, ..., fn_v0), ..., (f0_vm, ..., fn_vm)

```

to

```auto
INSERT INTO table (fa, ..., fn)
SELECT * FROM unnest(f0v[], ..., fnv[])

```

For those unfamiliar with `unnest` when used in the context of a table expression it can be seen as an analogous to the `zip` function in Python.

Apparently the speed benefits come from a reduction in time spent planning the query as the `unnest` technique avoids having to reason about `n * m` binding parameters (one for each value of each row) as only `n` homogeneous arrays are passed over for insertion. In other words, the amount of time spent _planing_ the insert is not a function of the number of items anymore which is a desirable property for large inserts.

One point that caught my attention was the author’s statement that

> several object-relational mappers use it under the hood

while I knew for sure that Django’s ORM didn’t so I gave it a try to see how invasive such a change would be.

Well I’m happy to report that through the exploration work I identified [a potential performance improvement to SQL generating logic](https://github.com/charettes/django/commit/a04a043f8ca084a1f1c4aae18a10c6bf16b80813) which yield a 5% improvement on the [query\_values\_10000.benchmark.QueryValues10000.time\_query\_values\_10000 ASV benchmark](https://django.github.io/django-asv/#query_benchmarks.query_values_10000.benchmark.QueryValues10000.time_query_values_10000) and would likely get better if the benchmark was inserting more than one field at a time.

I also managed to [get `unnest` insert working in a non-invasive manner under the following conditions](https://github.com/charettes/django/commit/4ee4afb0c692c5dacd81122fd82dd59bbc83eb23)

1. It can only be used if all values are literals as an homogeneous array of values must be bound to `unnest` parameters. From my experience this is the case most of the time as it’s rare that database expressions are provided for insertion purposes.
2. It cannot be used when inserting `ArrayField` because `unnest` doesn’t allow to discriminate on the depth of the unnesting (it’s more of a _flatten_ function) so there’s no way to provide a bound parameter that is an array of arrays without creating a custom function.

When the proper conditions are met `unnest` can be used and from my local testing trying to reproduce the article’s author result I can reliably reproduce a 20-25% speed up when using `bulk_create` to insert 500 rows at a time and no slow down up when using the technique for 2-499 rows.

The great thing about this approach is that it is compatible with all the other options `bulk_create` support such as `ignore_conflicts` or `update_fields` and friends. It’s really just a replacement for `VALUES` and one binding parameter per field X row and also avoids generating a large string to send over the wire. It also happens to pass the full test suite 🙂

I’m happy to create an optimization ticket out of [this branch](https://github.com/django/django/compare/main...charettes:django:insert-optimizations) but I wanted to get some community validation on the merit of the approach and the reproducibility of the results first.

Cheers!

---

_[View the full topic](https://forum.djangoproject.com/t/speeding-up-postgres-bulk-create-by-using-unnest/36508)._