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?