MIME-Version: 1.0
References: <CAKna9VY2v0XsDberzbJXZ4MqEW1RUtD0L_Mis_vrgEQWZgH0gg@mail.gmail.com>
 <4178E73A-24F5-4E3C-92F6-1532D8102C3E@kleczek.org>
In-Reply-To: <4178E73A-24F5-4E3C-92F6-1532D8102C3E@kleczek.org>
From: Lok P <loknath.73@gmail.com>
Date: Sat, 21 Sep 2024 16:44:08 +0530
Message-ID: <CAKna9Vbt1VJu7Oa8FTWasgby+-kJn7omOhbfmWzkdpVwBiqNzQ@mail.gmail.com>
Subject: Re: How batch processing works
To: =?UTF-8?B?TWljaGHFgiBLxYJlY3plaw==?= <michal@kleczek.org>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e10ea606229f41d4"
Archived-At: <https://www.postgresql.org/message-id/CAKna9Vbt1VJu7Oa8FTWasgby%2B-kJn7omOhbfmWzkdpVwBiqNzQ%40mail.gmail.com>
Precedence: bulk

--000000000000e10ea606229f41d4
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sat, Sep 21, 2024 at 9:51=E2=80=AFAM Micha=C5=82 K=C5=82eczek <michal@kl=
eczek.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=E2=80=99ve done some batch processing of JSON messages from Kafka in Ja=
va.
> 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 =E2=80=A6 FROM parsed RETURNING ...
> )
> INSERT INTO child SELECT =E2=80=A6 FROM parsed=E2=80=A6
>
> 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?

--000000000000e10ea606229f41d4
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Sat, Sep 21, 2024 at 9:51=E2=80=AF=
AM Micha=C5=82 K=C5=82eczek &lt;<a href=3D"mailto:michal@kleczek.org">micha=
l@kleczek.org</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddi=
ng-left:1ex">Hi,<br>
<br>
&gt; On 19 Sep 2024, at 07:30, Lok P &lt;<a href=3D"mailto:loknath.73@gmail=
.com" target=3D"_blank">loknath.73@gmail.com</a>&gt; wrote:<br>
&gt; <br>
[snip]<br>
&gt; <br>
&gt; Method-4<br>
&gt; <br>
&gt; INSERT INTO parent_table VALUES=C2=A0 (1, &#39;a&#39;), (2, &#39;a&#39=
;);<br>
&gt; INSERT INTO child_table VALUES=C2=A0 =C2=A0(1,1, &#39;a&#39;), (1,2, &=
#39;a&#39;);<br>
&gt; commit;<br>
<br>
I=E2=80=99ve done some batch processing of JSON messages from Kafka in Java=
.<br>
By far the most performant way was to:<br>
<br>
1. Use prepared statements<br>
2. Parse JSON messages in Postgres<br>
3. Process messages in batches<br>
<br>
All three can be achieved by using arrays to pass batches:<br>
<br>
WITH parsed AS (<br>
=C2=A0 SELECT msg::json FROM unnest(?)<br>
),<br>
parents AS (<br>
=C2=A0 INSERT INTO parent SELECT =E2=80=A6 FROM parsed RETURNING ...<br>
)<br>
INSERT INTO child SELECT =E2=80=A6 FROM parsed=E2=80=A6<br>
<br>
Not the single parameter that you can bind to String[]<br>
<br>
Hope that helps.<br><br></blockquote><div><br></div><div>Got your point.</d=
iv><div>But wondering why we don&#39;t see any difference in performance=C2=
=A0between method-2 and method-3 above. So does it mean that,I am testing t=
his in a wrong way or it&#39;s the expected behaviour and thus there is no =
meaning in converting the row by row inserts into a bulk insert, but just c=
hanging the commit frequency will do the same job in a row by row insert ap=
proach?</div></div></div>

--000000000000e10ea606229f41d4--