MIME-Version: 1.0
References: <CAPtGvF9i5XunrgFUWYrCLnmnD0akdLKBQLdO1qsz9C5nz0m3ZQ@mail.gmail.com>
In-Reply-To: <CAPtGvF9i5XunrgFUWYrCLnmnD0akdLKBQLdO1qsz9C5nz0m3ZQ@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 4 Apr 2024 14:15:40 -0400
Message-ID: <CANzqJaCBmGpbqc6RS-BubJpeW3aUNiM3knQL0KWOmCFdoeOQvg@mail.gmail.com>
Subject: Re: Multiple COPY statements for one table vs one for ~half a billion records
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000004f9141061549544f"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaCBmGpbqc6RS-BubJpeW3aUNiM3knQL0KWOmCFdoeOQvg%40mail.gmail.com>
Precedence: bulk

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

On Thu, Apr 4, 2024 at 2:04=E2=80=AFPM Carl L <cllewellyno@gmail.com> wrote=
:

> 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 co=
py
> 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.
>

Is the target table partitioned in the same way that the input data is
split?

That would make things faster...


> 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.
>

Unless it fills up your WAL partition.

>

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Apr 4, 2024 at 2:04=E2=80=AFPM Ca=
rl L &lt;<a href=3D"mailto:cllewellyno@gmail.com">cllewellyno@gmail.com</a>=
&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_=
quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,=
204);padding-left:1ex"><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 per core) and I&#39;m performing a copy from memory ( =
from stdin binary) into Postgres from each of these threads - i.e. there ar=
e 80 COPY statements being generated for one table that are running concurr=
ently. I can see each of the Postgres processes sitting at around 15% CPU u=
sage.<br></div></blockquote><div><br></div><div>Is the target table partiti=
oned in the same way that the input data is split?</div><div><br></div><div=
>That would make things faster...</div><div>=C2=A0</div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"ltr">These are all also in the=
 same transaction - I am the only one connected, so it&#39;s not an issue t=
o hold a big transaction.<br></div></blockquote><div><br></div><div>Unless =
it fills up your WAL partition.</div><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddin=
g-left:1ex">
</blockquote></div></div>

--0000000000004f9141061549544f--