On Sat, Sep 21, 2024 at 9:51 AM Michał Kłeczek <michal@kleczek.org> wrote:
Hi,
> On 19 Sep 2024, at 07:30, Lok P <loknath.73@gmail.com> wrote: > [snip] > > Method-4 > > INSERT INTO parent_table VALUES (1, 'a'), (2, 'a'); > INSERT INTO child_table VALUES (1,1, 'a'), (1,2, 'a'); > commit;
I’ve done some batch processing of JSON messages from Kafka in Java. By far the most performant way was to:
1. Use prepared statements 2. Parse JSON messages in Postgres 3. Process messages in batches
All three can be achieved by using arrays to pass batches:
WITH parsed AS ( SELECT msg::json FROM unnest(?) ), parents AS ( INSERT INTO parent SELECT … FROM parsed RETURNING ... ) INSERT INTO child SELECT … FROM parsed…
Not the single parameter that you can bind to String[]
Hope that helps.
Got your point.
But wondering why we don't see any difference in performance between method-2 and method-3 above. So does it mean that,I am testing this in a wrong way or it's the expected behaviour and thus there is no meaning in converting the row by row inserts into a bulk insert, but just changing the commit frequency will do the same job in a row by row insert approach?
