(2018/08/21 16:03), Amit Langote wrote:> On 2018/08/20 23:43, Tom Lane wrote:>> Chris Travers<chris(dot)travers(at)adjust(dot)com> writes:>>> I am looking at trying to make two modifications to the PostgreSQL FDW and>>> would like feedback on this before I do.>>>>> 1. INSERTMETHOD=[insert|copy] option on foreign table.>>>>> One significant limitation of the PostgreSQL FDW is that it does a prepared>>> statement insert on each row written which imposes a per-row latency. This>>> hits environments where there is significant latency or few latency>>> guarantees particularly hard, for example, writing to a foreign table that>>> might be physically located on another continent. The idea is that>>> INSERTMETHOD would default to insert and therefore have no changes but>>> where needed people could specify COPY which would stream the data out.>>> Updates would still be unaffected.

>> A different thing we could think about is enabling COPY TO/FROM a>> foreign table.>> Fwiw, the following commit did introduce COPY FROM support for foreign> tables, although using a FDW INSERT interface, so not exactly optimized> for bulk-loading yet.>> commit 3d956d9562aa4811b5eaaaf5314d361c61be2ae0> Author: Robert Haas<rhaas(at)postgresql(dot)org>> Date: Fri Apr 6 19:16:11 2018 -0400>> Allow insert and update tuple routing and COPY for foreign tables.

That's right. To improve the efficiency, I plan to work on COPY FROM/TO a foreign table for PG12 (In [1], I proposed new FDW APIs for COPY FROM).