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.klaver@aklaver.com