Re: Two proposed modifications to the PostgreSQL FDW

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Chris Travers <chris(dot)travers(at)adjust(dot)com>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Two proposed modifications to the PostgreSQL FDW
Date: 2018-08-21 07:03:31
Message-ID: 4a471794-edb4-821a-e22f-fa7dc8e8b44f@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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.
>
> It seems unlikely to me that an FDW option would be at all convenient
> for this. What about selecting it dynamically based on the planner's
> estimate of the number of rows to be inserted?
>
> 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.

Thanks,
Amit

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Khandekar 2018-08-21 07:18:57 Re: Slotification of partition tuple conversion
Previous Message Haribabu Kommi 2018-08-21 06:55:47 Re: Pluggable Storage - Andres's take