Re: postgres_fdw: Use COPY to speed up batch inserts

2025-11-18 Thread Matheus Alcantara
On Mon Nov 17, 2025 at 11:03 PM -03, Masahiko Sawada wrote: > IIUC the performance regression occurs when users insert many rows > into a foreign table with batch_size = 1 and use_copy_for_insert = > true (tps: 133.451518 vs. 132.644801 vs. 88.998804). Since batch_size > defaults to 1, users might

Re: postgres_fdw: Use COPY to speed up batch inserts

2025-11-17 Thread Masahiko Sawada
On Thu, Nov 6, 2025 at 3:49 PM Matheus Alcantara wrote: > > On Fri Oct 31, 2025 at 4:02 PM -03, I wrote: > > It's showing a bit complicated to decide at runtime if we should use the > > COPY or INSERT for batch insert into a foreign table. Perhaps we could > > add a new option on CREATE FOREIGN TA

Re: postgres_fdw: Use COPY to speed up batch inserts

2025-11-06 Thread Matheus Alcantara
On Fri Oct 31, 2025 at 4:02 PM -03, I wrote: > It's showing a bit complicated to decide at runtime if we should use the > COPY or INSERT for batch insert into a foreign table. Perhaps we could > add a new option on CREATE FOREIGN TABLE to enable this usage or not? We > could document the performanc

Re: postgres_fdw: Use COPY to speed up batch inserts

2025-10-31 Thread Matheus Alcantara
On Thu Oct 30, 2025 at 1:32 PM -03, Andrew Dunstan wrote: >> It seems to me that we need to disable the COPY usage when the foreign >> table has triggers enabled. >> > > I think it's probably worth finding out why COPY is so much worse in the > presence of triggers. Is there something we can do to

Re: postgres_fdw: Use COPY to speed up batch inserts

2025-10-30 Thread Andrew Dunstan
On 2025-10-29 We 8:28 PM, Matheus Alcantara wrote: On Wed Oct 29, 2025 at 12:10 AM -03, jian he wrote: On Sat, Oct 25, 2025 at 2:27 AM Matheus Alcantara wrote: On this new version I also added some regress tests on postgres_fdw.sql In the CopyFrom function, we have the CopyInsertMethod, CI

Re: postgres_fdw: Use COPY to speed up batch inserts

2025-10-29 Thread Matheus Alcantara
On Wed Oct 29, 2025 at 12:10 AM -03, jian he wrote: > On Sat, Oct 25, 2025 at 2:27 AM Matheus Alcantara > wrote: >> >> On this new version I also added some regress tests on postgres_fdw.sql >> > > In the CopyFrom function, we have the CopyInsertMethod, CIM_SINGLE is slower > than CIM_MULTI, I thi

Re: postgres_fdw: Use COPY to speed up batch inserts

2025-10-28 Thread jian he
On Sat, Oct 25, 2025 at 2:27 AM Matheus Alcantara wrote: > > On this new version I also added some regress tests on postgres_fdw.sql > In the CopyFrom function, we have the CopyInsertMethod, CIM_SINGLE is slower than CIM_MULTI, I think. We should do performance tests for the case where the COPY s

Re: postgres_fdw: Use COPY to speed up batch inserts

2025-10-24 Thread Matheus Alcantara
Hi, thanks for testing this patch! On Thu Oct 23, 2025 at 6:49 AM -03, jian he wrote: > On Thu, Oct 23, 2025 at 8:01 AM Matheus Alcantara > wrote: >> >> Please see the attached v3 version that implements this idea. >> > hi. > > I am not famailith with this module. > some of the foreach can be rep

Re: postgres_fdw: Use COPY to speed up batch inserts

2025-10-23 Thread jian he
On Thu, Oct 23, 2025 at 8:01 AM Matheus Alcantara wrote: > > Please see the attached v3 version that implements this idea. > hi. I am not famailith with this module. some of the foreach can be replaced with foreach_int. I suspect that somewhere Form_pg_attribute.attisdropped is not handled prope

Re: postgres_fdw: Use COPY to speed up batch inserts

2025-10-22 Thread Matheus Alcantara
On Tue Oct 21, 2025 at 11:25 AM -03, Matheus Alcantara wrote: >>> Lastly, I don't know if we should change the EXPLAIN(ANALYZE, VERBOSE) >>> output for batch inserts that use the COPY to mention that we are >>> sending the COPY command to the remote server. I guess so? >>> >> >> Good point. We defi

Re: postgres_fdw: Use COPY to speed up batch inserts

2025-10-21 Thread Matheus Alcantara
Thanks for testing and for the comments! On Fri Oct 17, 2025 at 6:28 AM -03, Jakub Wartak wrote: > On Thu, Oct 16, 2025 at 10:42 PM Tomas Vondra wrote: >> Thanks for the patch. Please add it to the next committfest (PG19-3) at > > Hi Matheus! same here - thanks for the patch! > >> > The attached

Re: postgres_fdw: Use COPY to speed up batch inserts

2025-10-21 Thread Matheus Alcantara
Thanks for the review! On Thu Oct 16, 2025 at 5:39 PM -03, Tomas Vondra wrote: > Thanks for the patch. Please add it to the next committfest (PG19-3) at > > https://commitfest.postgresql.org/ > > so that we don't lose track of the patch. > Here it is: https://commitfest.postgresql.org/patch/6137

Re: postgres_fdw: Use COPY to speed up batch inserts

2025-10-17 Thread Jakub Wartak
On Thu, Oct 16, 2025 at 10:42 PM Tomas Vondra wrote: > Thanks for the patch. Please add it to the next committfest (PG19-3) at Hi Matheus! same here - thanks for the patch! > > The attached patch uses the COPY command whenever we have a *numSlots > > > 1 but the tests show that maybe we should h

Re: postgres_fdw: Use COPY to speed up batch inserts

2025-10-16 Thread Tomas Vondra
Hi Matheus, Thanks for the patch. Please add it to the next committfest (PG19-3) at https://commitfest.postgresql.org/ so that we don't lose track of the patch. On 10/15/25 17:02, Matheus Alcantara wrote: > Hi all, > > Currently on postgres_fdw we use prepared statements to insert batches >