Re: PARALLEL CTAS

I'm not at all familiar with Oracle / 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 into a Ruby language gist demonstrating the core concept recently.

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