pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: tsunakawa.takay@fujitsu.com <tsunakawa.takay@fujitsu.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: POC: postgres_fdw insert batching
Date: Tue, 24 Nov 2020 02:44:45 +0100
Message-ID: <43b5d643-da69-70a5-16d2-d04d0851b723@enterprisedb.com> (raw)
In-Reply-To: <TYAPR01MB2990004041DD64597E52759DFEFC0@TYAPR01MB2990.jpnprd01.prod.outlook.com>
References: <20200628151002.7x5laxwpgvkyiu3q@development>
	<0300d7df-854b-6f72-1e93-dfbf98f3fc8a@postgrespro.ru>
	<20200712001101.x65f3hvsanvamtgj@development>
	<OSBPR01MB2982039EA967F0304CC6A3ECFE0B0@OSBPR01MB2982.jpnprd01.prod.outlook.com>
	<20201008221421.foasnh5of66rupnm@development>
	<TYAPR01MB2990F362E75C19F3904B9706FE080@TYAPR01MB2990.jpnprd01.prod.outlook.com>
	<TYAPR01MB2990ECD1C68EA694DD0667E4FEE90@TYAPR01MB2990.jpnprd01.prod.outlook.com>
	<f0fc3678-699a-2bc5-d238-928c5b2382d2@enterprisedb.com>
	<TYAPR01MB299099C34392F2A9FA0777BDFEE80@TYAPR01MB2990.jpnprd01.prod.outlook.com>
	<OSBPR01MB298268F1A1BACFCE61740CC3FEE20@OSBPR01MB2982.jpnprd01.prod.outlook.com>
	<d9a17f23-81cc-fca1-56e6-602bd2ae06b4@enterprisedb.com>
	<TYAPR01MB2990A726F963C10DF3B98790FEE00@TYAPR01MB2990.jpnprd01.prod.outlook.com>
	<71113ab1-ed31-3dbc-ce07-e081e17473b6@enterprisedb.com>
	<TYAPR01MB2990004041DD64597E52759DFEFC0@TYAPR01MB2990.jpnprd01.prod.outlook.com>


On 11/23/20 3:17 AM, tsunakawa.takay@fujitsu.com wrote:
> From: Tomas Vondra <tomas.vondra@enterprisedb.com>
>> I don't think this is usable in practice, because a single session
>> may be using multiple FDW servers, with different implementations,
>> latency to the data nodes, etc. It's unlikely a single GUC value
>> will be suitable for all of them.
> 
> That makes sense.  The row size varies from table to table, so the
> user may want to tune this option to reduce memory consumption.
> 
> I think the attached patch has reflected all your comments.  I hope
> this will pass..
> 

Thanks - I didn't have time for a thorough review at the moment, so I
only skimmed through the diff and did a couple  very simple tests. And I
think overall it looks quite nice.

A couple minor comments/questions:

1) We're calling it "batch_size" but the API function is named
postgresGetMaxBulkInsertTuples(). Perhaps we should rename the function
to postgresGetModifyBatchSize()? That has the advantage it'd work if we
ever add support for batching to UPDATE/DELETE.

2) Do we have to lookup the batch_size in create_foreign_modify (in
server/table options)? I'd have expected to look it up while planning
the modify and then pass it through the list, just like the other
FdwModifyPrivateIndex stuff. But maybe that's not possible.

3) That reminds me - should we show the batching info on EXPLAIN? That
seems like a fairly interesting thing to show to the user. Perhaps
showing the average batch size would also be useful? Or maybe not, we
create the batches as large as possible, with the last one smaller.

4) It seems that ExecInsert executes GetMaxBulkInsertTuples() over and
over for every tuple. I don't know it that has measurable impact, but it
seems a bit excessive IMO. I don't think we should support the batch
size changing during execution (seems tricky).


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company





view thread (115+ messages)  latest in thread

Message-ID: <43b5d643-da69-70a5-16d2-d04d0851b723@enterprisedb.com>
Permalink:  ../43b5d643-da69-70a5-16d2-d04d0851b723@enterprisedb.com/
Also on:    postgresql.org/message-id/43b5d643-da69-70a5-16d2-d04d0851b723@enterprisedb.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: tomas.vondra@enterprisedb.com, tsunakawa.takay@fujitsu.com, tomas.vondra@2ndquadrant.com, a.lepikhov@postgrespro.ru
  Subject: Re: POC: postgres_fdw insert batching
  In-Reply-To: <43b5d643-da69-70a5-16d2-d04d0851b723@enterprisedb.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox