Re: postgres_fdw insert extremely slow

On 11/25/20 8:37 AM, Mats Julian Olsen wrote:
> Helle pgsql-general,
>
> We have just set up postgres_fdw between two postgres databases, x and
> y, with the plan to periodically insert data from x into y.
>
> We've successfully set up the connection with a few options:
> `use_remote_estimate 'true'` and `fetch_size '50000'` (the latter we've
> played around with). We've run ANALYZE on the foreign server.
>
> SELECTs against the foreign table returns in milliseconds, however an
> INSERT of 100 rows takes 10 seconds. we have roughly 200 000 rows for
> the initial sync, which translates into ~6 hours.

Postgres version(s)?

Without the actual query and EXPLAIN ANALYZE on it this will be
difficult to answer with any detail.

Also would be nice to have the table schema.

>
> Is this the expected performance of postgre_fdw? Is there anything we've
> overlooked when setting this up? Very curious to hear experiences from
> the community when doing read/write and not just read from foreign sources.
>
> Best regards,
>
> --
> Mats
> CTO @ Dune Analytics
> We're hiring: https://careers.duneanalytics.com
> <https://careers.duneanalytics.com>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com