MIME-Version: 1.0
References: <C1FD3065-84ED-47BA-BDA1-16B6A7E05EE7@gmail.com>
 <CAFcck8HhsTh70ZjQEuvm2kfx0LULJAZR3NZNHvSEX1Y5BaxAMg@mail.gmail.com>
 <C1789BA9-1AAE-4BD7-A9FF-E0FB8AF5B54F@gmail.com>
 <1e0484b6-40a6-15a9-9890-0991c8b8c1da@gmail.com>
 <7BD04D8E-9307-4107-B4D0-4C281390F996@gmail.com>
In-Reply-To: <7BD04D8E-9307-4107-B4D0-4C281390F996@gmail.com>
From: Steve Midgley <science@misuse.org>
Date: Mon, 12 Dec 2022 10:19:00 -0800
Message-ID: 
 <CAJexoSLz2dvbVeJHbFnYNTCPbA5qLNrs4W0nPQL1dsPV2nG1xg@mail.gmail.com>
Subject: Re: PARALLEL CTAS
To: Shane Borden <sborden76@gmail.com>
Cc: Rob Sargent <robjsargent@gmail.com>, pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000046230a05efa58afe"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJexoSLz2dvbVeJHbFnYNTCPbA5qLNrs4W0nPQL1dsPV2nG1xg%40mail.gmail.com>
Precedence: bulk

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

On Mon, Dec 12, 2022 at 9:36 AM Shane Borden <sborden76@gmail.com> wrote:

> I saw this as an option.  Let=E2=80=99s say you have a million row table.=
  If you
> copy from STDIN, do you have to spool the source table to a local file
> first?  Looking to replace a CTAS thats doing this all within SQL
> statements now.
> ---
>
> Thanks,
>
>
> Shane Borden
> sborden76@gmail.com
>
> On Dec 12, 2022, at 12:25 PM, Rob Sargent <robjsargent@gmail.com> wrote:
>
> On 12/12/22 10:13, Shane Borden wrote:
>
> The issue is there are certain performance benefits to be had by doing
> parallel CTAS operations and when converting from Oracle to PostgreSQL
> switching to a =E2=80=9CCOPY=E2=80=9D operation isn=E2=80=99t feasible.
> ---
>
> Thanks,
>
> Today I suspect you're left with something like the following:
> - CTAS from source where 1=3D2 (i.e. table definition via select semantic=
s)
> - copy from stdin (filled with intended CTAS select)
>
> I'm not at all familiar with Oracle / CTAS (fair warning). But I did spen=
d
some time, years ago, building a fast import using COPY/STDIN in Postgres
awhile back. I copied that code into a Ruby language gist
<https://gist.github.com/science/393907d4123c87ed767bc81e9dd5a7da>
demonstrating the core concept recently.

The key idea, IMO, is to use an intermediate language like Ruby to manage
the "spooling" from the source file to STDIN. That system could presumably
fork processes to have more than one COPY from the file going at a time
(and coordinate each thread to access different rows of the source file).

However, my experience was that if you want the absolutely fastest import,
you should copy the source file to a fast disk/array on the Postgres server
itself, and then run the COPY command pointing to that file locally, not
piping from STDIN over a network connection. Postgres, at least in my
testing, maxes I/O on a single COPY command pretty heartily - so much so
that you might find performance otherwise becomes a problem, esp if your
DBs are on that disk/array. IIRC, I was seeing millions of rows loaded per
second on a high performing Postgres server, using this method. Also, IIRC,
I removed the indexes from the table, again to max throughput, and then
added them back after the COPY completed (not sure if modern Postgres COPY
needs that - this was years ago).

I think total time to completion was also faster, as the index rebuilds
were faster after the fact, than done during the loading (again I'm foggy
so maybe inaccurate - this was years ago), but I was loading into a blank
table -- rebuilding already constructed indexes might have an overwhelming
cost.

If you have to do a network based COPY from STDIN, then my gist above will
hopefully show the way - and I wouldn't be surprised if running multiple of
these simultaneously gets better performance than a single COPY. At that
point, you'll just be fine tuning to max saturation on the network
bandwidth - not the max capability of the postgres server, which the local
disk COPY does.

Hopefully this input is relevant - please ignore if I'm missing your goal
entirely!

Best,
Steve

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

<div dir=3D"ltr"><div dir=3D"ltr"><div><br></div></div><br><div class=3D"gm=
ail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, Dec 12, 2022 at 9:=
36 AM Shane Borden &lt;<a href=3D"mailto:sborden76@gmail.com">sborden76@gma=
il.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"m=
argin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left=
:1ex"><div>I saw this as an option.=C2=A0 Let=E2=80=99s say you have a mill=
ion row table.=C2=A0 If you copy from STDIN, do you have to spool the sourc=
e table to a local file first?=C2=A0 Looking to replace a CTAS thats doing =
this all within SQL statements now.<br><div>
<div>---<br><br>Thanks,<br><br><br>Shane Borden<br><a href=3D"mailto:sborde=
n76@gmail.com" target=3D"_blank">sborden76@gmail.com</a></div>

</div>
<div><br><blockquote type=3D"cite"><div>On Dec 12, 2022, at 12:25 PM, Rob S=
argent &lt;<a href=3D"mailto:robjsargent@gmail.com" target=3D"_blank">robjs=
argent@gmail.com</a>&gt; wrote:</div><br><div><div>On 12/12/22 10:13, Shane=
 Borden wrote:<br><blockquote type=3D"cite">The issue is there are certain =
performance benefits to be had by doing parallel CTAS operations and when c=
onverting from Oracle to PostgreSQL switching to a =E2=80=9CCOPY=E2=80=9D o=
peration isn=E2=80=99t feasible.<br>---<br><br>Thanks,<br><br></blockquote>=
Today I suspect you&#39;re left with something like the following:<br>- CTA=
S from source where 1=3D2 (i.e. table definition via select semantics)<br>-=
 copy from stdin (filled with intended CTAS select)<br><br></div></div></bl=
ockquote></div></div></blockquote><div>I&#39;m not at all familiar with Ora=
cle / CTAS (fair warning). But I did spend some time, years ago, building a=
 fast import using COPY/STDIN in Postgres awhile back. I copied that code i=
nto a <a href=3D"https://gist.github.com/science/393907d4123c87ed767bc81e9d=
d5a7da">Ruby language gist</a> demonstrating the core concept recently.<div=
><br></div><div>The key idea, IMO, is to use an intermediate language like =
Ruby to manage the &quot;spooling&quot; from the source file to STDIN. That=
 system could presumably fork processes to have more than one COPY from the=
 file going at a time (and coordinate each thread to access different rows =
of the source file).</div></div><div><br></div><div>However, my experience =
was that if you want the absolutely fastest import, you should copy the sou=
rce file to a fast disk/array on the Postgres server itself, and then run t=
he COPY command pointing to that file locally, not piping from STDIN over a=
 network connection. Postgres, at least in my testing, maxes I/O on a singl=
e COPY command pretty heartily - so much so that you might find performance=
 otherwise becomes a problem, esp if your DBs are on that disk/array. IIRC,=
 I was seeing millions of rows loaded per second on a high performing Postg=
res server, using this method. Also, IIRC, I removed the indexes from the t=
able, again to max throughput, and then added them back after the COPY comp=
leted (not sure if modern Postgres COPY needs that - this was years ago).=
=C2=A0</div><div><br></div><div>I think total time to completion was also f=
aster, as the index rebuilds were faster after the fact, than done during t=
he loading (again I&#39;m foggy so maybe inaccurate - this was years ago), =
but I was loading into a blank table -- rebuilding already constructed inde=
xes might have an overwhelming cost.</div><div><br></div><div>If you have t=
o do a network based COPY from STDIN, then my gist above will hopefully sho=
w the way - and I wouldn&#39;t be surprised if running multiple of these si=
multaneously gets better performance than a single COPY. At that point, you=
&#39;ll just be fine tuning to max saturation on the network bandwidth - no=
t the max capability of the postgres server, which the local disk COPY does=
.</div><div><br></div><div>Hopefully this input is relevant - please ignore=
 if I&#39;m missing your goal entirely!</div><div><br></div><div>Best,</div=
><div>Steve</div><div>=C2=A0</div></div></div>

--00000000000046230a05efa58afe--