From: Shane Borden <sborden76@gmail.com>
Message-Id: <C1789BA9-1AAE-4BD7-A9FF-E0FB8AF5B54F@gmail.com>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_4C2D72E4-551B-4112-A8C5-C8560A83357B"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.200.110.1.12\))
Subject: Re: PARALLEL CTAS
Date: Mon, 12 Dec 2022 12:13:36 -0500
In-Reply-To: 
 <CAFcck8HhsTh70ZjQEuvm2kfx0LULJAZR3NZNHvSEX1Y5BaxAMg@mail.gmail.com>
Cc: pgsql-sql@lists.postgresql.org
To: Erik Brandsberg <erik@heimdalldata.com>
References: <C1FD3065-84ED-47BA-BDA1-16B6A7E05EE7@gmail.com>
 <CAFcck8HhsTh70ZjQEuvm2kfx0LULJAZR3NZNHvSEX1Y5BaxAMg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/C1789BA9-1AAE-4BD7-A9FF-E0FB8AF5B54F%40gmail.com>
Precedence: bulk


--Apple-Mail=_4C2D72E4-551B-4112-A8C5-C8560A83357B
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

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,


Shane Borden
sborden76@gmail.com

> On Dec 12, 2022, at 11:55 AM, Erik Brandsberg <erik@heimdalldata.com> =
wrote:
>=20
> Copy operations are by far the most efficient way to do bulk loads.  =
The fdw extension isn't very efficient based on prior experience.
>=20
> On Mon, Dec 12, 2022 at 11:17 AM Shane Borden <sborden76@gmail.com =
<mailto:sborden76@gmail.com>> wrote:
>> Based on testing thus far, it appears that the only way to get a bulk =
load operation (similar to oracle) would be to use the COPY command or =
to use multiple connections and break up the inserts across connections. =
 I did see a possibility of using the postgres_fdw extension and use the =
batch functionality, but I haven=E2=80=99t tested that yet.
>>=20
>> Does anyone know the status of this feature / patch that was =
discussed in this link?
>>=20
>> =
https://www.postgresql.org/message-id/CAJcOf-cXnB5cnMKqWEp2E2z7Mvcd04iLVmV=
%3DqpFJrR3AcrTS3g%40mail.gmail.com
>>=20
>> Is there a way in PostgreSQL to parallelize a CTAS statement similar =
to how you can do it in Oracle?
>>=20
>> ---
>>=20
>> Thanks,
>>=20
>>=20
>> Shane Borden
>> sborden76@gmail.com <mailto:sborden76@gmail.com>


--Apple-Mail=_4C2D72E4-551B-4112-A8C5-C8560A83357B
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; =
charset=3Dutf-8"></head><body style=3D"overflow-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;">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.<div><div>
<div>---<br><br>Thanks,<br><br><br>Shane =
Borden<br>sborden76@gmail.com</div>

</div>
<div><br><blockquote type=3D"cite"><div>On Dec 12, 2022, at 11:55 AM, =
Erik Brandsberg &lt;erik@heimdalldata.com&gt; wrote:</div><br =
class=3D"Apple-interchange-newline"><div><div dir=3D"ltr">Copy =
operations are by far the most efficient&nbsp;way to do bulk =
loads.&nbsp; The fdw extension isn't very efficient&nbsp;based on prior =
experience.</div><br><div class=3D"gmail_quote"><div dir=3D"ltr" =
class=3D"gmail_attr">On Mon, Dec 12, 2022 at 11:17 AM Shane Borden =
&lt;<a href=3D"mailto:sborden76@gmail.com">sborden76@gmail.com</a>&gt; =
wrote:<br></div><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><div>Based on testing thus far, =
it appears that the only way to get a bulk load operation (similar to =
oracle) would be to use the COPY command or to use multiple connections =
and break up the inserts across connections.&nbsp; I did see a =
possibility of using the postgres_fdw extension and use the batch =
functionality, but I haven=E2=80=99t tested that =
yet.</div><div><br></div><div>Does anyone know the status of this =
feature / patch that was discussed in this =
link?</div><div><br></div><div><a =
href=3D"https://www.postgresql.org/message-id/CAJcOf-cXnB5cnMKqWEp2E2z7Mvc=
d04iLVmV%3DqpFJrR3AcrTS3g%40mail.gmail.com" =
target=3D"_blank">https://www.postgresql.org/message-id/CAJcOf-cXnB5cnMKqW=
Ep2E2z7Mvcd04iLVmV%3DqpFJrR3AcrTS3g%40mail.gmail.com</a></div><div><br></d=
iv><div>Is there a way in PostgreSQL to parallelize a CTAS statement =
similar to how you can do it in Oracle?</div><br><div>
<div>---<br><br>Thanks,<br><br><br>Shane Borden<br><a =
href=3D"mailto:sborden76@gmail.com" =
target=3D"_blank">sborden76@gmail.com</a></div>

</div>
<br></div></blockquote></div>
</div></blockquote></div><br></div></body></html>=

--Apple-Mail=_4C2D72E4-551B-4112-A8C5-C8560A83357B--