Re: postgres_fdw insert extremely slow

On Fri, Nov 27, 2020 at 2:00 PM pabloa98 <pabloa98(at)gmail(dot)com> wrote:

> I would like to suggest for postgres_fdw: If the foreign database is
> PostgreSQL,
>

Just to be clear, the "postgres" part of the name means the remote database
must be a PostgreSQL database, there is no "if". Likewise, for the
extension mysql_fdw the remote database is "MySQL".

> the link should just pass through all the CRUD SQL commands to the other
> database.
>
> If the other database is of a version so different that cannot make sense
> of the CRUD SQL command, it will generate an error and that's it.
>
> This would be very useful to keep datasets synchronized.
>
>
We already offer a tool for that, dblink.

https://www.postgresql.org/docs/current/dblink.html

But the generalized behavior of FDW doesn't work to just send a raw SQL
command across, even for CRUD.
e.g.,
UPDATE remote_tbl
SET ...
FROM local_tbl
WHERE remote_tbl.col_id=local_tbl.col_id;
or
INSERT INTO remote_tbl SELECT * FROM local_tbl;

Not saying that there isn't room for improvement here but I'm doubting it's
simple.

David J.