Re: POC: postgres_fdw insert batching - Mailing list pgsql-hackers

From Etsuro Fujita
Subject Re: POC: postgres_fdw insert batching
Date
Msg-id CAPmGK14ASgw=iTCTKTtifUj2Pu5T56eU6i5dqN6G72GMojfYqw@mail.gmail.com
Whole thread Raw
In response to Re: POC: postgres_fdw insert batching  (Amit Langote <amitlangote09@gmail.com>)
List pgsql-hackers
On Tue, Jun 30, 2020 at 2:54 PM Amit Langote <amitlangote09@gmail.com> wrote:
> On Tue, Jun 30, 2020 at 1:22 PM Ashutosh Bapat
> <ashutosh.bapat@2ndquadrant.com> wrote:
> > On Tue, 30 Jun 2020 at 08:47, Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> >> On Mon, Jun 29, 2020 at 7:52 PM Ashutosh Bapat
> >> <ashutosh.bapat.oss@gmail.com> wrote:
> >> > On Sun, Jun 28, 2020 at 8:40 PM Tomas Vondra
> >> > <tomas.vondra@2ndquadrant.com> wrote:
> >>
> >> > > 3) What about the other DML operations (DELETE/UPDATE)?
> >> > >
> >> > > The other DML operations could probably benefit from the batching too.
> >> > > INSERT was good enough for a PoC, but having batching only for INSERT
> >> > > seems somewhat asmymetric. DELETE/UPDATE seem more complicated because
> >> > > of quals, but likely doable.
> >> >
> >> > Bulk INSERTs are more common in a sharded environment because of data
> >> > load in say OLAP systems. Bulk update/delete are rare, although not
> >> > that rare. So if an approach just supports bulk insert and not bulk
> >> > UPDATE/DELETE that will address a large number of usecases IMO. But if
> >> > we can make everything work together that would be good as well.
> >>
> >> In most cases, I think the entire UPDATE/DELETE operations would be
> >> pushed down to the remote side by DirectModify.  So, I'm not sure we
> >> really need the bulk UPDATE/DELETE.
> > That may not be true for a partitioned table whose partitions are foreign tables. Esp. given the work that Amit
Langoteis doing [1]. It really depends on the ability of postgres_fdw to detect that the DML modifying each of the
partitionscan be pushed down. That may not come easily. 
>
> While it's true that how to accommodate the DirectModify API in the
> new inherited update/delete planning approach is an open question on
> that thread, I would eventually like to find an answer to that.  That
> is, that work shouldn't result in losing the foreign partition's
> ability to use DirectModify API to optimize updates/deletes.

That would be great!

Best regards,
Etsuro Fujita



pgsql-hackers by date:

Previous
From: Fujii Masao
Date:
Subject: Re: min_safe_lsn column in pg_replication_slots view
Next
From: Peter Eisentraut
Date:
Subject: Re: Rotten parts of src/backend/replication/README