Re: postgres_fdw insert extremely slow

On Thu, Nov 26, 2020 at 8:25 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Thu, 2020-11-26 at 09:07 -0800, Adrian Klaver wrote:
> So even if Mats where to break this query:
>
> INSERT INTO foreign.labels (address, labels)
> SELECT address_id, ARRAY_AGG(name) AS labels
> FROM labels
> GROUP BY 1
> LIMIT 100;
>
> down into something like this:
>
> INSERT INTO foreign.labels (address, labels)
> VALUES (), (), (), ();
>
> postgres_fdw would send it as individual INSERTs?

Yes, that's the way the FDW API works.

I can confirm that it is NOT like FDW API work. I have the same problem and in the fdw database it receives:

INSERT INTO foreign.labels (address, labels) VALUES ();

One value at a time.

Same scenario, same performance. No network related. It is just a batch insert is split into 1 insert by row.

Pablo

Thank you all for your replies.

We've decided not to use postgres_fdw for the time being because of this behavior. Hope to revisit in the future.

Best,

-- 
Mats
CTO @ Dune Analytics
We're hiring: https://careers.duneanalytics.com