MIME-Version: 1.0
References: <c3bbe654-939b-467a-9626-8c0938b7347d@gmail.com>
In-Reply-To: <c3bbe654-939b-467a-9626-8c0938b7347d@gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Mon, 4 May 2026 23:05:11 -0400
Message-ID: <CAKAnmmLdeAMr6BSXfqSt-psk_bCsGh=ksyUHRtM9A49DOqsifg@mail.gmail.com>
Subject: Re: Improving insert performance
To: =?UTF-8?B?0KDRg9GB0LjQvdC+0LIg0KHQtdC80LXQvQ==?= <mrpinkolik@gmail.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000da08a30651095292"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmLdeAMr6BSXfqSt-psk_bCsGh%3DksyUHRtM9A49DOqsifg%40mail.gmail.com>
Precedence: bulk

--000000000000da08a30651095292
Content-Type: text/plain; charset="UTF-8"

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 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='all' on so you
can see exactly what the driver and application are really doing for those
inserts. Play around with the thread 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

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

<div dir=3D"ltr"><div>You can definitely do better than 35k rps. Setting th=
e 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 in? Have you tri=
ed version 18?</div><div><br></div><div>By using small string values and CO=
PY, 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 potenti=
al. Checking wait_events as Laurenz says is a great idea. I&#39;d also do 1=
0 inserts with log_statement=3D&#39;all&#39; on so you can see exactly what=
 the driver and application are really doing for those inserts. 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 is worrisome - a=
re you committing after every insert? That&#39;s gonna hurt performance.</d=
iv><div><br></div><div><br></div><div>Cheers,</div><div>Greg</div><div><br>=
</div></div>

--000000000000da08a30651095292--