Re: Possibilities for optimizing inserts across oracle_fdw foreign data wrapper

On Mon, Sep 20, 2021 at 3:18 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
>
> On Sun, 2021-09-19 at 10:28 +0000, Niels Jespersen wrote:
> > We are often using the oracle_fdw to transfer data between Postgres (version 11+) and Oracle (version 18+). It works great.
> >
> > However I have a task at hand that requires inserting a few billion rows in an Oracle table from a Postgres query.
> >
> > insert into t_ora (a,b,c)
> > select a,b,c from t_pg;
> >
> > This is driven from a plpgsql stored procedure, if that matters.
> >
> > I want to optimize the running time of this. But I am unsure of which, if any, possibilities there actually is.
> >
> > Reducing the number of network roundtrips is usually a good way to increase throughput. But, how do I do that?
> >
> > If I could make the Oracle insert direct load, that would usually also increase throughput.
> > But, is that possible here. There are no constraints defined on the destinaton tables.
>
> The cause of the bad performance for bulk data modifications is that the FDW API is built
> that way: each row INSERTed means a round trip between PostgreSQL and Oracle.
>

Just as a note, with PostgreSQL 14 the FDW APIs allow batch insert. It
should be possible to update oracle_fdw to take advantage of that as
well, right?

--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/