title: What disadvantages of using jsonb_array_elements for batching?
body: <p>I did some experiments with the performance of my <code>RabbitMQ listener</code> which has to insert a batch to <code>PostgreSQL</code>.</p>
  <p>I got a significant improvement in the performance when used <code>jsonb_array_elements</code>.</p>
  <p>For example:</p>
  <p><strong>Table students</strong></p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE students
  (
      id            BIGSERIAL PRIMARY KEY,
      name          VARCHAR(64)  NOT NULL,
      age           INT          NOT NULL,
      average_score NUMERIC(2)   NOT NULL
  )
  </code></pre>
  <p><strong>Insert SQL</strong></p>
  <pre class="lang-sql prettyprint-override"><code>INSERT INTO students (name, age, average_score)
  SELECT arg -&gt;&gt; 0, (arg -&gt; 1)::INT, (arg -&gt; 2)::INT
  FROM jsonb_array_elements(?) AS arg
  </code></pre>
  <p>And then execute the query by <code>JdbcTemplate</code>:</p>
  <pre class="lang-java prettyprint-override"><code>var sql = &quot;&lt;SQL&gt;&quot;;
  var args = generate(10000); // List&lt;Object[]&gt;
  var pGobject = new PGobject();
  pGobject.setType(&quot;jsonb&quot;);
  pGobject.setValue(objectMapper.writeValueAsString(args));
  jdbcTemplate.update(sql, pGobject);
  </code></pre>
  <p>Time difference between this approach and the traditional batch is <strong>50%</strong> (10000 rows locally) like this:</p>
  <pre class="lang-java prettyprint-override"><code>var sql = &quot;INSERT INTO students (id, name, age, average_score) VALUES (DEFAULT, ?, ?, ?)&quot;;
  var args = generate(COUNT);  // List&lt;Object[]&gt;
  jdbcTemplate.batchUpdate(sql, args, new int[]{Types.VARCHAR, Types.INTEGER, Types.NUMERIC});
  </code></pre>
  <p>I see only pluses to use <code>jsonb_array_elements</code> (of course <code>SQL</code> can be a little bit strange), but in common this way attracts me, I even can use <code>INSERT...RETURNING</code> to return generated <code>ids</code>.</p>
  <p>Could somebody show me the disadvantages of this approach until I used it in my projects widely? Maybe there are some possible pitfalls?</p>