MIME-Version: 1.0
References: <c3bbe654-939b-467a-9626-8c0938b7347d@gmail.com> <CAKAnmmLdeAMr6BSXfqSt-psk_bCsGh=ksyUHRtM9A49DOqsifg@mail.gmail.com>
In-Reply-To: <CAKAnmmLdeAMr6BSXfqSt-psk_bCsGh=ksyUHRtM9A49DOqsifg@mail.gmail.com>
From: =?UTF-8?Q?Roland_M=C3=BCller?= <rolmur@gmail.com>
Date: Fri, 8 May 2026 06:31:14 +0300
Message-ID: <CA+8p0G2TjxcxuJeTy=TLc5vaPWPbAPQwunp3f+r18Q73mt002A@mail.gmail.com>
Subject: Re: Improving insert performance
To: Greg Sabino Mullane <htamfids@gmail.com>, pgsql-performance@postgresql.org, 
	=?UTF-8?B?0KDRg9GB0LjQvdC+0LIg0KHQtdC80LXQvQ==?= <mrpinkolik@gmail.com>
Content-Type: multipart/alternative; boundary="00000000000031e10106514608a1"
Archived-At: <https://www.postgresql.org/message-id/CA%2B8p0G2TjxcxuJeTy%3DTLc5vaPWPbAPQwunp3f%2Br18Q73mt002A%40mail.gmail.com>
Precedence: bulk

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

JDBC is using autocommit by default.

You can switch it off and  start using  explit commits.

connection.setAutoCommit(false);


Greg Sabino Mullane <htamfids@gmail.com> ezt =C3=ADrta (id=C5=91pont: 2026.=
 m=C3=A1j. 5.,
K 6:05):

> You can definitely do better than 35k rps. Setting the table as unlogged
> is a great start. Are you using prepared queries? Have you tried COPY? Wh=
at
> is the size of the typical rows going in? Have you tried version 18?
>
> By using small string values and COPY, I can easily get over 1 million
> rows per second on a single thread, on an underpowered and fairly busy
> laptop, to give you a rough idea of potential. Checking wait_events as
> Laurenz says is a great idea. I'd also do 10 inserts with
> log_statement=3D'all' on so you can see exactly what the driver and
> application are really doing for those inserts. Play around with the thre=
ad
> sizes to find the best combo.
>
> > transactional inserts: yes
>
> This is worrisome - are you committing after every insert? That's gonna
> hurt performance.
>
>
> Cheers,
> Greg
>
>

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

<div dir=3D"auto"><div>JDBC is using autocommit by default.</div><div dir=
=3D"auto"><br></div><div dir=3D"auto">You can switch it off and=C2=A0 start=
 using=C2=A0 explit commits.</div><div dir=3D"auto"><br>connection.setAutoC=
ommit(false);</div><div dir=3D"auto"><br></div><div dir=3D"auto"><br><div c=
lass=3D"gmail_quote" dir=3D"auto"><div dir=3D"ltr" class=3D"gmail_attr">Gre=
g Sabino Mullane &lt;<a href=3D"mailto:htamfids@gmail.com" target=3D"_blank=
" rel=3D"noreferrer">htamfids@gmail.com</a>&gt; ezt =C3=ADrta (id=C5=91pont=
: 2026. m=C3=A1j. 5., K 6:05):<br></div><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex"><div dir=3D"ltr"><div>You can definitely do better than 35k =
rps. Setting the table as unlogged is a great start. Are you using prepared=
 queries? Have you tried COPY? What is the size of the typical rows going i=
n? Have you tried version 18?</div><div><br></div><div>By using small strin=
g values and COPY, I can easily get over 1 million rows per second on a sin=
gle thread, on an underpowered and fairly busy laptop, to give you a rough =
idea of potential. Checking wait_events as Laurenz says is a great idea. I&=
#39;d also do 10 inserts with log_statement=3D&#39;all&#39; on so you can s=
ee exactly what the driver and application are really doing for those inser=
ts. Play around with the thread sizes to find the best combo.</div><div><br=
></div><div>&gt; transactional inserts: yes</div><div><br></div><div>This i=
s worrisome - are you committing after every insert? That&#39;s gonna hurt =
performance.</div><div><br></div><div><br></div><div>Cheers,</div><div>Greg=
</div><div><br></div></div>
</blockquote></div></div></div>

--00000000000031e10106514608a1--