What disadvantages of using jsonb_array_elements for batching?

Viewed 41

I did some experiments with the performance of my RabbitMQ listener which has to insert a batch to PostgreSQL.

I got a significant improvement in the performance when used jsonb_array_elements.

For example:

Table students

CREATE TABLE students
(
    id            BIGSERIAL PRIMARY KEY,
    name          VARCHAR(64)  NOT NULL,
    age           INT          NOT NULL,
    average_score NUMERIC(2)   NOT NULL
)

Insert SQL

INSERT INTO students (name, age, average_score)
SELECT arg ->> 0, (arg -> 1)::INT, (arg -> 2)::INT
FROM jsonb_array_elements(?) AS arg

And then execute the query by JdbcTemplate:

var sql = "<SQL>";
var args = generate(10000); // List<Object[]>
var pGobject = new PGobject();
pGobject.setType("jsonb");
pGobject.setValue(objectMapper.writeValueAsString(args));
jdbcTemplate.update(sql, pGobject);

Time difference between this approach and the traditional batch is 50% (10000 rows locally) like this:

var sql = "INSERT INTO students (id, name, age, average_score) VALUES (DEFAULT, ?, ?, ?)";
var args = generate(COUNT);  // List<Object[]>
jdbcTemplate.batchUpdate(sql, args, new int[]{Types.VARCHAR, Types.INTEGER, Types.NUMERIC});

I see only pluses to use jsonb_array_elements (of course SQL can be a little bit strange), but in common this way attracts me, I even can use INSERT...RETURNING to return generated ids.

Could somebody show me the disadvantages of this approach until I used it in my projects widely? Maybe there are some possible pitfalls?

0 Answers
Related