MIME-Version: 1.0
From: Lok P <loknath.73@gmail.com>
Date: Thu, 19 Sep 2024 11:00:47 +0530
Message-ID: <CAKna9VY2v0XsDberzbJXZ4MqEW1RUtD0L_Mis_vrgEQWZgH0gg@mail.gmail.com>
Subject: How batch processing works
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000049441c0622723a86"
Archived-At: <https://www.postgresql.org/message-id/CAKna9VY2v0XsDberzbJXZ4MqEW1RUtD0L_Mis_vrgEQWZgH0gg%40mail.gmail.com>
Precedence: bulk

--00000000000049441c0622723a86
Content-Type: text/plain; charset="UTF-8"

Hello,
Saw multiple threads around the same , so I want some clarification. As we
know row by row is slow by slow processing , so in heavy write systems(say
the client app is in Java) , people asked to do DMLS in batches rather in a
row by row fashion to minimize the chatting or context switches between
database and client which is resource intensive. What I understand is that
, a true batch processing means the client has to collect all the input
bind values and  prepare the insert statement and submit to the database at
oneshot and then commit.

What it means actually and if we divide the option as below, which method
truly does batch processing or there exists some other method for doing the
batch processing considering postgres as backend database?

I understand, the first method below is truly a row by row processing in
which context switches happen between client and database with each row,
whereas the second method is just batching the commits but not a true batch
processing as it will do the same amount of context switching between the
database and client. But regarding the third and fourth method, will both
execute in a similar fashion in the database with the same number of
context switches? If any other better method exists to do these inserts in
batches? Appreciate your guidance.


CREATE TABLE parent_table (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE child_table (
    id SERIAL PRIMARY KEY,
    parent_id INT REFERENCES parent_table(id),
    value TEXT
);


Method-1

insert into parent_table values(1,'a');
commit;
insert into parent_table values(2,'a');
commit;
insert into child_table values(1,1,'a');
Commit;
insert into child_table values(1,2,'a');
commit;

VS

Method-2

insert into parent_table values(1,'a');
insert into parent_table values(2,'a');
insert into child_table values(1,1,'a');
insert into child_table values(1,2,'a');
Commit;

VS

Method-3

with
 a as ( insert into parent_table values(1,'a')  )
, a1 as (insert into parent_table values(2,'a') )
, b as (insert into child_table values(1,1,'a')  )
 , b1 as  (insert into child_table values(1,2,'a')  )
select;
commit;

Method-4

INSERT INTO parent_table VALUES  (1, 'a'), (2, 'a');
INSERT INTO child_table VALUES   (1,1, 'a'), (1,2, 'a');
commit;

Regards
Lok

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

<div dir=3D"ltr">Hello,<div>Saw multiple threads around the same , so I wan=
t some clarification. As we know row by row is slow by slow processing , so=
 in heavy write systems(say the client app is in Java) , people asked to do=
 DMLS in batches rather in a row by row fashion to minimize the chatting or=
 context switches between database and client which is resource intensive. =
What I understand is that , a true batch processing means the client has to=
 collect all the input bind values and =C2=A0prepare the insert statement a=
nd submit to the database at oneshot and then commit. <br><br>What it means=
 actually and if we divide the option as below, which method truly does bat=
ch processing or there exists some other method for doing the batch process=
ing considering postgres as backend database?<br><br>I understand, the firs=
t method below is truly a row by row processing in which context switches h=
appen between client and database with each row, whereas the second method =
is just batching the commits but not a true batch processing as it will do =
the same amount of context switching between the database and client. But r=
egarding the third and fourth method, will both execute in a similar fashio=
n in the database with the same number of context switches? If any other be=
tter method exists to do these inserts in batches? Appreciate your guidance=
.<br><br><br>CREATE TABLE parent_table (<br>=C2=A0 =C2=A0 id SERIAL PRIMARY=
 KEY,<br>=C2=A0 =C2=A0 name TEXT<br>);<br><br>CREATE TABLE child_table (<br=
>=C2=A0 =C2=A0 id SERIAL PRIMARY KEY,<br>=C2=A0 =C2=A0 parent_id INT REFERE=
NCES parent_table(id),<br>=C2=A0 =C2=A0 value TEXT<br>);<br><br><br>Method-=
1<br><br>insert into parent_table values(1,&#39;a&#39;); <br>commit;<br>ins=
ert into parent_table values(2,&#39;a&#39;); <br>commit;<br>insert into chi=
ld_table values(1,1,&#39;a&#39;); <br>Commit;<br>insert into child_table va=
lues(1,2,&#39;a&#39;); <br>commit;<br><br>VS <br><br>Method-2<br><br>insert=
 into parent_table values(1,&#39;a&#39;); <br>insert into parent_table valu=
es(2,&#39;a&#39;); <br>insert into child_table values(1,1,&#39;a&#39;);<br>=
insert into child_table values(1,2,&#39;a&#39;); =C2=A0<br>Commit;<br><br>V=
S<br><br>Method-3<br><br>with<br>=C2=A0a as ( insert into parent_table valu=
es(1,&#39;a&#39;) =C2=A0)<br>, a1 as (insert into parent_table values(2,=
9;a&#39;) )<br>, b as (insert into child_table values(1,1,&#39;a&#39;) =C2=
=A0)<br>=C2=A0, b1 as =C2=A0(insert into child_table values(1,2,&#39;a&#39;=
) =C2=A0)<br>select;<br>commit;<br><br>Method-4<br><br>INSERT INTO parent_t=
able VALUES =C2=A0(1, &#39;a&#39;), (2, &#39;a&#39;);<br>INSERT INTO child_=
table VALUES =C2=A0 (1,1, &#39;a&#39;), (1,2, &#39;a&#39;);<br>commit;<br><=
/div><div><br></div><div>Regards</div><div>Lok</div></div>

--00000000000049441c0622723a86--