Subject: Re: New Table Access Methods for Multi and Single Inserts
To: Jeff Davis <pgsql@j-davis.com>,
 Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>,
 Justin Pryzby <pryzby@telsasoft.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>,
 Andres Freund <andres@anarazel.de>, Paul Guo <guopa@vmware.com>
References: 
 <CALj2ACVi9eTRYR=gdca5wxtj3Kk_9q9qVccxsS1hngTGOCjPwQ@mail.gmail.com>
 <20201217050522.GU30237@telsasoft.com>
 <CALj2ACVgT1iocd5nQ+rEmqt3xcCONkR037qbc8PiojdR39Ag=w@mail.gmail.com>
 <20201217204442.GX30237@telsasoft.com>
 <CALj2ACW3BC5kgdffZ2LD_CT2wQoXVc29kGB74SVWnGZ=UFqcAQ@mail.gmail.com>
 <20201218175439.GA30237@telsasoft.com> <20201221074725.GF30237@telsasoft.com>
 <CALj2ACWMnZZCu=G0PJkEeYYicKeuJ-X=SU19i6vQ1+=uXz8u0Q@mail.gmail.com>
 <20201225023958.GW30237@telsasoft.com>
 <CALj2ACVDtYYRYD2SC+X2ALOUkhnUcgC7RLxiEYVWW2HxxrfRww@mail.gmail.com>
 <96eaa813-4ad6-e80a-04a4-cc8082d356dd@swarm64.com>
 <f9a3e6d7e60a890e2feaac93055e27f61fb2b351.camel@j-davis.com>
From: Luc Vlaming <luc@swarm64.com>
Message-ID: <543ac8bf-5914-aa90-2c2e-e4f8345bfe61@swarm64.com>
Date: Wed, 6 Jan 2021 08:00:55 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.5.0
In-Reply-To: <f9a3e6d7e60a890e2feaac93055e27f61fb2b351.camel@j-davis.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
MIME-Version: 1.0
X-MS-Exchange-MessageSentRepresentingType: 1
Received: from [10.1.0.5] (213.61.129.43) by
 FRXPR01CA0059.DEUPRD01.PROD.OUTLOOK.DE (2a01:4180:c010:15::27) with Microsoft
 SMTP Server (version=TLS1_2,
 cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.3742.4 via Frontend
 Transport; Wed, 6 Jan 2021 07:00:54 +0000
X-MS-PublicTrafficType: Email
X-MS-Office365-Filtering-Correlation-Id: 58f71f1a-3649-49d0-f8d0-08d8b210d038
X-MS-TrafficTypeDiagnostic: LEXPR01MB1181:
X-Microsoft-Antispam-PRVS: 
	<LEXPR01MB1181FF9C889AF91E31BB663AB7D00@LEXPR01MB1181.DEUPRD01.PROD.OUTLOOK.DE>
X-MS-Oob-TLC-OOBClassifiers: OLM:8882;
X-MS-Exchange-SenderADCheck: 1
X-Microsoft-Antispam: BCL:0;
X-Microsoft-Antispam-Message-Info: 
	NpZHcHWxEpe3MW8zeKWTJYXtY3HssJp/9z33+NZkrxS+gyI6GEkW/hfgr2SzOIJoYNRtSD3mi7vKm8U5yjjvPmzRewnTNtoQzFATUqu37SDmAlWHw+DGVp9JX7t5mPsDjAdG5hjoPvwkgXN1IeR0ZwvviQO5y1GM8kWpqiMtYPOJYdiJe8sdeMDnKxy5Z1W2/kGzvKHe5NgugdvIBQ81QKiYbFfQuvpOb3uUZRtOJaSw0fi3ossPK7sEzQNJz9jxH7hL84kzz4awDpm/Vj/Yc9gO1LkeGLTjVJ7VIXaiwBXRA0HZEqxSsMHHcu/+9zyvzT3D9Avea41xCgz1q0NnZq3ivfT3V75mV5m4Oya/XyfC23AKfOcQH6180wi98MXq8Z8sISt3YKADDi+yZgQjS+ssshuwkN8zVZX5KYSP8RFb/9hvfeldkdkifo3sX5jI
X-Forefront-Antispam-Report: 
	CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:LEXPR01MB0144.DEUPRD01.PROD.OUTLOOK.DE;PTR:;CAT:NONE;SFS:(39830400003)(366004)(396003)(136003)(376002)(346002)(66556008)(66946007)(36756003)(55236004)(2906002)(186003)(66476007)(2616005)(956004)(8676002)(478600001)(5660300002)(26005)(86362001)(16576012)(316002)(31686004)(16526019)(8936002)(31696002)(110136005)(4326008)(54906003)(53546011)(52116002)(43740500002);DIR:OUT;SFP:1101;
X-MS-Exchange-AntiSpam-MessageData: 
	=?utf-8?B?VTB4MVlDZzRiRVRtam1XQmJyZG9VcGZIM0dZWXVrcjB5cFhpRytzajhFbjg5?=
 =?utf-8?B?UW91UEF2SkVTeXBxamdGalZkQkZGdDNkK3diOGVRdTdzOXRrMzArWVRVQjFl?=
 =?utf-8?B?L1NRbDY4dGI0QUx6dmMwTEE4QU9VL20ydVluMUFCNlQ5cCtVUWFTN1Q2VXpJ?=
 =?utf-8?B?SEIveUdQc3R5dzlUa3pwSWlkZzZhbEUxOEFpZXJnL21abXgrY1Z6MjhzUmpC?=
 =?utf-8?B?ekFjSkdrYlNYUVJxNFp1REJXbmpVYWpiN0Q1MmpzaERURi8xa1pFNlcxYXha?=
 =?utf-8?B?N0Uzc0p2SmZMZDFtSU0vYjV5WDYrNXhEUU5qaU04WnhJYlRnUEsrMWRzQ2Yx?=
 =?utf-8?B?dzgzaFBvQzR1c0I5WkNtRDRLY3V3OGpObXowRCtuaUc4M2MwblBSU2xmdGRx?=
 =?utf-8?B?a1FrU3AzN09oSzVmSFk1a0xqeUF2aEo0NVZNbjFmSkFleUNmcHJOdldvYWpI?=
 =?utf-8?B?QkRRcXB4b3VZQWZQaUFkaFBNcFRSUWpOdGtWbS83T1NodGQvcHgwejRVYVpi?=
 =?utf-8?B?UzBlVU5Gc29SWlpKMEVSMFVSdllZRlgzMTd2SitwM1BYSndDY3B0bVp1aElo?=
 =?utf-8?B?L1lnQVhRODBTS1dLbm5vQ2loc0ZXUGJjZUZtRHJlanQwc081UWZpNGtRYkgx?=
 =?utf-8?B?MXN6OWY5UkxtbVJ0MmtwNGlValBpODZoT3dpMGR2YTVmN0hub0ZZS1NGWU56?=
 =?utf-8?B?cDlEbUhOdVRUSlNIZ1NnNnNhcUJaclNmWGtFWUdycTdwdi95ZXo5OTNkMVJy?=
 =?utf-8?B?dmM5MEE0c0k2SWZOMTAvTTNxODJsdEFYNzF3TVY1KzJkQkhTME5hRkJJdEN6?=
 =?utf-8?B?dXI3T0JqL3lNUTdHZ0duN2lzaitnbndwOTY2bVh3N0pyMExXQm5EY1Z1WUE4?=
 =?utf-8?B?cWRBYnZZaUpNbGI5Q3JFRm1KSFhZWFFmVHFKTFFqRExWM1E1YkwzMEJlN0JF?=
 =?utf-8?B?TGdsR3Rjd2h2ZGJFTC9WenFod21UaVozYmhmSklKczNRNHhhdEg2QUdxU3V5?=
 =?utf-8?B?bFlvZEhQVlFGN0VDK0VQWUc0MHB0VytKVWFkVFg1cjllTmxaVmlnVFRnTWUv?=
 =?utf-8?B?SS9mTkVaN1NlUlBVL0NGZzJSbk80U2NVTEw5dGVOYjA2bDdwYWFyVUc3QzY3?=
 =?utf-8?B?U3FaQkk1Tm1vSW10cjBiYXRwZ0loKytKOEVLVEI1V2RTY3M5RFErdngvalJV?=
 =?utf-8?B?cHUrZGpURHRYNWNHS3o4WGhMVHgwR2NnR2dtdTc0QXNIbjlZNGNIR0huNlZ1?=
 =?utf-8?B?UlNES0hHSmJuSUtaREs4cVFaZVdWWW9WWis2UmRBSEhHN2E4R21oS09NODN4?=
 =?utf-8?Q?wfgxxoI2hgof9pWuCk+eIn2R0AOJBEtPPj?=
X-OriginatorOrg: swarm64.com
X-MS-Exchange-CrossTenant-AuthSource: LEXPR01MB0144.DEUPRD01.PROD.OUTLOOK.DE
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 06 Jan 2021 07:00:55.4382
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 6a3a733d-0e05-4774-8f9e-4fc6a12face6
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 58f71f1a-3649-49d0-f8d0-08d8b210d038
X-MS-Exchange-CrossTenant-MailboxType: HOSTED
X-MS-Exchange-CrossTenant-UserPrincipalName: 
 NMN/8/zzM4hSnmi0tXTfNFyLaZU9l0yqxbDfPN1K2DjpEghPIcpBDZ9+cmc0+5Et
X-MS-Exchange-Transport-CrossTenantHeadersStamped: LEXPR01MB1181
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Precedence: bulk

On 05-01-2021 22:28, Jeff Davis wrote:
> On Mon, 2021-01-04 at 08:59 +0100, Luc Vlaming wrote:
>> Reason I'm asking is that I quite liked the heap_insert_begin
>> parameter
>> is_multi, which could even be turned into a "expected_rowcount" of
>> the
>> amount of rows expected to be commited in the transaction (e.g.
>> single,
>> several, thousands/stream).
> 
> Do you mean "written by the statement" instead of "committed in the
> transaction"? It doesn't look like the TableInsertState state will
> survive across statement boundaries.
> 
> Though that is an important question to consider. If the premise is
> that a given custom AM may be much more efficient at bulk inserts than
> retail inserts (which is reasonable), then it makes sense to handle the
> case of a transaction with many single-tuple inserts. But keeping
> insert state across statement boundaries also raises a few potential
> problems.
> 
> Regards,
> 	Jeff Davis
> 
> 

I did actually mean until the end of the transaction. I know this is 
currently not possible with the current design but I think it would be 
cool to start going that way (even if slightly). Creating some more 
freedom on how a tableam optimizes inserts, when one syncs to disk, etc 
would be good imo. It would allow one to create e.g. a tableam that 
would not have as a high overhead when doing single statement inserts.

Kind regards,
Luc