MIME-Version: 1.0
From: Carl L <cllewellyno@gmail.com>
Date: Thu, 4 Apr 2024 11:03:56 -0700
Message-ID: <CAPtGvF9i5XunrgFUWYrCLnmnD0akdLKBQLdO1qsz9C5nz0m3ZQ@mail.gmail.com>
Subject: Multiple COPY statements for one table vs one for ~half a billion records
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000561d810615492a91"
Archived-At: <https://www.postgresql.org/message-id/CAPtGvF9i5XunrgFUWYrCLnmnD0akdLKBQLdO1qsz9C5nz0m3ZQ%40mail.gmail.com>
Precedence: bulk

--000000000000561d810615492a91
Content-Type: text/plain; charset="UTF-8"

Hi there,

I have around half a billion records that are being generated from a back
end that are split into 80 threads (one per core) and I'm performing a copy
from memory ( from stdin binary) into Postgres from each of these threads -
i.e. there are 80 COPY statements being generated for one table that are
running concurrently. I can see each of the Postgres processes sitting at
around 15% CPU usage.

These are all also in the same transaction - I am the only one connected,
so it's not an issue to hold a big transaction.

I can see that many of the Postgres threads have a wait event "LWLock:
BufferContent", which I assume means that they are waiting for each other
before they can write to the table. Therefore, would it be more efficient
to combine all of these and put them into one COPY statement?

Thanks!

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

<div dir=3D"ltr">Hi there,<br><br>I have around half a billion records that=
 are being generated from a back end that are split into 80 threads (one pe=
r core) and I&#39;m performing a copy from memory ( from stdin binary) into=
 Postgres from each of these threads - i.e. there are 80 COPY statements be=
ing generated for one table that are running concurrently. I can see each o=
f the Postgres processes sitting at around 15% CPU usage.<br><br>These are =
all also in the same transaction - I am the only one connected, so it&#39;s=
 not an issue to hold a big transaction.<br><br>I can see that many of the =
Postgres threads have a wait event &quot;LWLock: BufferContent&quot;, which=
 I assume means that they are waiting for each other before they can write =
to the table. Therefore, would it be more efficient to combine all of these=
 and put them into one COPY statement? <br><br>Thanks!</div>

--000000000000561d810615492a91--