Subject: [pgjdbc/pgjdbc] issue #3195: Alternative implementation for reWriteBatchedInserts

From: "vlsi (@vlsi)" <[email-masked]>
Date: Mon, 08 Apr 2024 11:02:00 +0000

**Describe the issue**

Currently we rewrite the select to multi-values insert.
It requires multiple statements, and we use power-of-two packing to alleviate the overhead.

There is an alternative option:

```sql
insert into table(id, name, ...)
 select unnest(?), unnest(?), ...
```

Then we could bind the values as a set of arrays.

It might have the following improvements:
1) We would no longer require "power-of-two" logic
2) The query will be shorter
3) It might be faster
4) There will be no limit of "65535 binds"

TODO:
* Measure how `select unnest(?), unnest(?)` compares with `values(...), (...), (...)`
* Test if it would work with "on conflict", "merge" and so on
* Figure out if we could support `values(DEFAULT, ...)`. It might be `DEFAULT` would require adjusting the column list in the `into` clause
* Figure out if we could support expressions in `values` like `values(?+?, ?-?)`

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 02 May 2024 18:59:28 +0000

This deserves some study for sure

---

From: "lantalex (@lantalex)" <[email-masked]>
Date: Fri, 22 Aug 2025 12:40:24 +0000

Some results from my local machine (with this change: https://github.com/pgjdbc/pgjdbc/pull/3782)

`Benchmark                           (p1nrows)  (p2multi)  Mode  Cnt     Score    Error  Units
InsertBatch.insertBatch                    16          1  avgt   50     1.727 ±  0.023  ms/op
InsertBatch.insertBatch                    16          4  avgt   50     1.658 ±  0.030  ms/op
InsertBatch.insertBatch                    16          8  avgt   50     1.623 ±  0.032  ms/op
InsertBatch.insertBatch                    16         16  avgt   50     1.605 ±  0.029  ms/op
InsertBatch.insertBatch                    16        128  avgt   50     1.616 ±  0.041  ms/op
InsertBatch.insertBatch                   128          1  avgt   50     2.725 ±  0.089  ms/op
InsertBatch.insertBatch                   128          4  avgt   50     2.421 ±  0.032  ms/op
InsertBatch.insertBatch                   128          8  avgt   50     2.277 ±  0.055  ms/op
InsertBatch.insertBatch                   128         16  avgt   50     2.137 ±  0.035  ms/op
InsertBatch.insertBatch                   128        128  avgt   50     2.078 ±  0.023  ms/op
InsertBatch.insertBatch                  1024          1  avgt   50    15.202 ±  0.625  ms/op
InsertBatch.insertBatch                  1024          4  avgt   50     6.122 ±  0.301  ms/op
InsertBatch.insertBatch                  1024          8  avgt   50     3.109 ±  0.181  ms/op
InsertBatch.insertBatch                  1024         16  avgt   50     3.180 ±  0.198  ms/op
InsertBatch.insertBatch                  1024        128  avgt   50     3.172 ±  0.172  ms/op
InsertBatch.insertBatchWithRewrite         16          1  avgt   50     1.597 ±  0.018  ms/op
InsertBatch.insertBatchWithRewrite         16          4  avgt   50     1.600 ±  0.026  ms/op
InsertBatch.insertBatchWithRewrite         16          8  avgt   50     1.593 ±  0.035  ms/op
InsertBatch.insertBatchWithRewrite         16         16  avgt   50     1.608 ±  0.034  ms/op
InsertBatch.insertBatchWithRewrite         16        128  avgt   50     1.606 ±  0.033  ms/op
InsertBatch.insertBatchWithRewrite        128          1  avgt   50     2.100 ±  0.044  ms/op
InsertBatch.insertBatchWithRewrite        128          4  avgt   50     2.129 ±  0.055  ms/op
InsertBatch.insertBatchWithRewrite        128          8  avgt   50     2.099 ±  0.043  ms/op
InsertBatch.insertBatchWithRewrite        128         16  avgt   50     2.102 ±  0.045  ms/op
InsertBatch.insertBatchWithRewrite        128        128  avgt   50     2.056 ±  0.051  ms/op
InsertBatch.insertBatchWithRewrite       1024          1  avgt   50     3.334 ±  0.152  ms/op
InsertBatch.insertBatchWithRewrite       1024          4  avgt   50     3.383 ±  0.233  ms/op
InsertBatch.insertBatchWithRewrite       1024          8  avgt   50     3.286 ±  0.213  ms/op
InsertBatch.insertBatchWithRewrite       1024         16  avgt   50     3.128 ±  0.155  ms/op
InsertBatch.insertBatchWithRewrite       1024        128  avgt   50     3.210 ±  0.222  ms/op
InsertBatch.insertCopy                     16          1  avgt   50    25.855 ±  0.389  ms/op
InsertBatch.insertCopy                     16          4  avgt   50     6.475 ±  0.058  ms/op
InsertBatch.insertCopy                     16          8  avgt   50     3.279 ±  0.057  ms/op
InsertBatch.insertCopy                     16         16  avgt   50     1.649 ±  0.012  ms/op
InsertBatch.insertCopy                     16        128  avgt   50     1.677 ±  0.032  ms/op
InsertBatch.insertCopy                    128          1  avgt   50   208.746 ±  3.734  ms/op
InsertBatch.insertCopy                    128          4  avgt   50    52.187 ±  0.633  ms/op
InsertBatch.insertCopy                    128          8  avgt   50    25.740 ±  0.428  ms/op
InsertBatch.insertCopy                    128         16  avgt   50    13.209 ±  0.190  ms/op
InsertBatch.insertCopy                    128        128  avgt   50     1.896 ±  0.022  ms/op
InsertBatch.insertCopy                   1024          1  avgt   50  1651.287 ± 12.337  ms/op
InsertBatch.insertCopy                   1024          4  avgt   50   414.660 ±  6.796  ms/op
InsertBatch.insertCopy                   1024          8  avgt   50   209.550 ±  3.395  ms/op
InsertBatch.insertCopy                   1024         16  avgt   50   105.950 ±  1.746  ms/op
InsertBatch.insertCopy                   1024        128  avgt   50    15.373 ±  0.278  ms/op
InsertBatch.insertExecute                  16          1  avgt   50    24.457 ±  0.426  ms/op
InsertBatch.insertExecute                 128          1  avgt   50   196.709 ±  3.700  ms/op
InsertBatch.insertExecute                1024          1  avgt   50  1569.965 ± 29.554  ms/op
InsertBatch.insertUnnestArrays             16          1  avgt   50     1.908 ±  0.042  ms/op
InsertBatch.insertUnnestArrays             16          4  avgt   50     1.695 ±  0.026  ms/op
InsertBatch.insertUnnestArrays             16          8  avgt   50     1.650 ±  0.034  ms/op
InsertBatch.insertUnnestArrays             16         16  avgt   50     1.619 ±  0.032  ms/op
InsertBatch.insertUnnestArrays             16        128  avgt   50     1.610 ±  0.029  ms/op
InsertBatch.insertUnnestArrays            128          1  avgt   50     3.203 ±  0.146  ms/op
InsertBatch.insertUnnestArrays            128          4  avgt   50     2.613 ±  0.067  ms/op
InsertBatch.insertUnnestArrays            128          8  avgt   50     2.356 ±  0.053  ms/op
InsertBatch.insertUnnestArrays            128         16  avgt   50     2.161 ±  0.047  ms/op
InsertBatch.insertUnnestArrays            128        128  avgt   50     1.988 ±  0.047  ms/op
InsertBatch.insertUnnestArrays           1024          1  avgt   50    15.987 ±  0.890  ms/op
InsertBatch.insertUnnestArrays           1024          4  avgt   50     6.338 ±  0.322  ms/op
InsertBatch.insertUnnestArrays           1024          8  avgt   50     3.269 ±  0.203  ms/op
InsertBatch.insertUnnestArrays           1024         16  avgt   50     2.979 ±  0.129  ms/op
InsertBatch.insertUnnestArrays           1024        128  avgt   50     3.013 ±  0.217  ms/op
InsertBatch.insertUnnestStruct             16          1  avgt   50     1.819 ±  0.042  ms/op
InsertBatch.insertUnnestStruct             16          4  avgt   50     1.651 ±  0.021  ms/op
InsertBatch.insertUnnestStruct             16          8  avgt   50     1.630 ±  0.029  ms/op
InsertBatch.insertUnnestStruct             16         16  avgt   50     1.604 ±  0.030  ms/op
InsertBatch.insertUnnestStruct             16        128  avgt   50     1.610 ±  0.035  ms/op
InsertBatch.insertUnnestStruct            128          1  avgt   50     2.705 ±  0.066  ms/op
InsertBatch.insertUnnestStruct            128          4  avgt   50     2.442 ±  0.051  ms/op
InsertBatch.insertUnnestStruct            128          8  avgt   50     2.204 ±  0.034  ms/op
InsertBatch.insertUnnestStruct            128         16  avgt   50     2.079 ±  0.048  ms/op
InsertBatch.insertUnnestStruct            128        128  avgt   50     2.018 ±  0.049  ms/op
InsertBatch.insertUnnestStruct           1024          1  avgt   50    15.142 ±  0.766  ms/op
InsertBatch.insertUnnestStruct           1024          4  avgt   50     5.853 ±  0.266  ms/op
InsertBatch.insertUnnestStruct           1024          8  avgt   50     3.095 ±  0.190  ms/op
InsertBatch.insertUnnestStruct           1024         16  avgt   50     3.079 ±  0.193  ms/op
InsertBatch.insertUnnestStruct           1024        128  avgt   50     3.027 ±  0.151  ms/op`

---

From: "lantalex (@lantalex)" <[email-masked]>
Date: Fri, 22 Aug 2025 12:43:26 +0000

Some additional results for batch size = 8192 and total rows = 16384
`Benchmark                           (p1nrows)  (p2multi)  Mode  Cnt      Score     Error  Units
InsertBatch.insertBatch                 16384          1  avgt   10    210.620 ±  15.459  ms/op
InsertBatch.insertBatch                 16384       8192  avgt   10     12.740 ±   0.656  ms/op
InsertBatch.insertBatchWithRewrite      16384          1  avgt   10     13.450 ±   0.949  ms/op
InsertBatch.insertBatchWithRewrite      16384       8192  avgt   10     13.264 ±   0.967  ms/op
InsertBatch.insertCopy                  16384          1  avgt   10  26194.022 ± 277.634  ms/op
InsertBatch.insertCopy                  16384       8192  avgt   10      8.687 ±   1.083  ms/op
InsertBatch.insertExecute               16384          1  avgt   10  24822.447 ± 251.000  ms/op
InsertBatch.insertUnnestArrays          16384          1  avgt   10    194.278 ±  15.689  ms/op
InsertBatch.insertUnnestArrays          16384       8192  avgt   10      9.933 ±   0.173  ms/op
InsertBatch.insertUnnestStruct          16384          1  avgt   10    192.089 ±  27.568  ms/op
InsertBatch.insertUnnestStruct          16384       8192  avgt   10     10.148 ±   0.241  ms/op
`