Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vlpn2-004T72-2C for pgsql-hackers@arkaria.postgresql.org; Fri, 30 Jan 2026 14:48:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vlpn0-0056uq-22 for pgsql-hackers@arkaria.postgresql.org; Fri, 30 Jan 2026 14:48:03 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vlpn0-0056ue-0f for pgsql-hackers@lists.postgresql.org; Fri, 30 Jan 2026 14:48:03 +0000 Received: from mail-centralusazolkn190100000.outbound.protection.outlook.com ([2a01:111:f403:d107::] helo=DM1PR04CU001.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1vlpmy-00000000DEJ-262V for pgsql-hackers@postgresql.org; Fri, 30 Jan 2026 14:48:02 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=T1sBuXME12YzyVQakgRZwHjZTJos2D0YShAup8M9ythskmRgFT1cACihH4jXVF+cAbK5EVIvNUj5CeyyGaIWHlc/fDjx5VllrhrAp1I8+iiXsgOx1oyED5ZaSeh5lPbkw3iHGCXbKOhGQqVypw5sqiGePZ05SZR35bv25FbpBrBLgnrKj/tk6lU9BecUKqrcd60UPMciLrmtAI//Sd+OQOmqpsmzgpwk7vBW4yZphoBjA+Qk2SXGcBJVP+Ms7VuNumRtizCD+brhuiuqUfNtl1ZlJGUCtd+/10Q1+ctmqUo+uhAx+ykGyDfXlCO8MaZtB6fdknk32lJT/zwE6cXnBA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector10001; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=d6iGKYZEOgfHPQZTln6ijqLC11MtOsr+LACuag6OUp0=; b=VCl6dOYSWOWA9zfAo+Y8UaEIBqxQSsPxRAfTeLm52pv3gYZ6wjZoDfPUMUvgWELoEPblTPFBPWlp7HLcg0KpQqS/1d57Fm0un9TBUQmPi4NadZ9ygtWjgjIxNF0sIEgt2btrKxEB+6cjrIFF64ywx/ZByi0VrbH08O3BqXQoBQZEcZcL1+NN2gcszlOIFx6+3VjBraht5fbsDJR/mM29+Urz7KoGGgX0QCVjRE78ozCGG9ezhjgArUjFLS3Z9Jk3e/rJls/smiN/46S6DpjuWLr9WFXULuIiB/ceSglQIqufdDzEyt5TnAp0t85EFCQ7u/RMiX6qlG/tMl39AUJdfg== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=outlook.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=d6iGKYZEOgfHPQZTln6ijqLC11MtOsr+LACuag6OUp0=; b=TneEu0UKVqVUKne402MqUYFJRWfKmXGj8Br5IvBOk+lso8bBOfIBph7Sai+eAQRp1+WJflvD0hdxAcmqLX2fTV63q5w+iEjkr1f98/hpuvLD6wXml8YQjwl6JnZ/nYUDl3KzWR6I3YjJt9jINLsJCiKA+zyyx0QpaE/uirrLoERVnSJP2r6l9YfpwC6x7PH8/ueIbHFzRTJhSzR1JFkKkIapvDSOD8F+XnvxWdQW6cdLQe57YG9I0DXIY+wucYvm/GuCPT2w5QrU1do2YkQP/+MNFKbN4iQFpUVG0LAldwW57WGy7bMYlXshnOm2nVvBQhJugeaAX3aPSpcqnQ/fiQ== Received: from PH0PR08MB7020.namprd08.prod.outlook.com (2603:10b6:510:74::15) by SJ0PR08MB6496.namprd08.prod.outlook.com (2603:10b6:a03:2ac::14) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.9564.8; Fri, 30 Jan 2026 14:47:56 +0000 Received: from PH0PR08MB7020.namprd08.prod.outlook.com ([fe80::129a:a42a:445c:3273]) by PH0PR08MB7020.namprd08.prod.outlook.com ([fe80::129a:a42a:445c:3273%4]) with mapi id 15.20.9564.010; Fri, 30 Jan 2026 14:47:54 +0000 From: Boris Mironov To: Madyshev Egor , "pgsql-hackers@postgresql.org" Subject: Re: Idea to enhance pgbench by more modes to generate data (multi-TXNs, UNNEST, COPY BINARY) Thread-Topic: Idea to enhance pgbench by more modes to generate data (multi-TXNs, UNNEST, COPY BINARY) Thread-Index: AQHcUwuRPlzLCtdZw06GAdoL15fmHbTwZ0aAgAGpzjGAACRkgIAAEcrGgAQQLgCAAG6d+YAGXmPMgAFzjL6AAV1tS4BpseD8gAEgG5GAAGN+EIAAFWg0 Date: Fri, 30 Jan 2026 14:47:54 +0000 Message-ID: References: In-Reply-To: Accept-Language: en-CA, en-US Content-Language: en-CA X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: x-ms-exchange-messagesentrepresentingtype: 1 x-ms-publictraffictype: Email x-ms-traffictypediagnostic: PH0PR08MB7020:EE_|SJ0PR08MB6496:EE_ x-ms-office365-filtering-correlation-id: 98c68fed-0d55-4d32-95ff-08de600e8cfc x-ms-exchange-slblob-mailprops: vuaKsetfIZmcv7cN2CQdJd4XVKwegu/40aI/WTVY8XoP0vEd2LtDSBW8lQK73GvJgqhx/zWs3p6xOeZlZ0LUbbLcMA74vfKYv0b5NOLQ4/JkLSdfeMS++p15AODMDTlXwCgDnJdzQLyTv3JzTQgCV36M9N3Ui8IsGnDRLa2NHiei2bbXxZplpHBPdMGTcQH37a9gDrnc2lAURnFKvijqCr3+gViChAoDpmfgFsmDol1v2xuwX20SLAXzZ78LI3HDllV7p2vwlOuM+tTwLWYmlknubaxS9nfOKgDhcZ6GWiUp3QS49wWblqcPrj97BXIaoUXeVHwYghCpBTmtZTo5kS0N9UFqo9LKdjo9Na8ltr6zm/HEAXBjb0lrAVBLmlwFDIx8zogP0MDZ1q3SG9k+ThZb19MKHWx9U7RQrpP9nbzSbFJMoF+1jy46XIxmpTYIqB7NWcSXH6EndAZ56JAQuseCkOTw59aleDAJORy9QYP0YHa4LryvzlGsmce65aAyk38iY/Qu3LChFs2HNIuuy3XvfCSqFzPBhphv3hyPemSOwLVgL/4qF2Ua8zH0c8SEEKISJrnl8JFylsCtzI32pbn8QDxCnQ9xpa3EiNf7Zl3ftKuYBlZW5TC9gUNwL42xrvClnuFYzEIWHEmWlE7B230FuVdDq8b1NuizMpViBPbPjJlgzOrV8aneiL/E0ce0pDpf/TTs+0gKs+cI6q0h3yvAe04Q/cGH1iQW2Fh6kQvO4xysq+xtU2tVA+7rr0vgrO3U1ovmiX+frFiIMyaF8rWKXWoIiBJgZizasfo7vx9rb19DSJSDhEzeMob1bQb+ x-microsoft-antispam: BCL:0;ARA:14566002|15030799006|15080799012|461199028|31061999003|12050799012|8060799015|8062599012|19110799012|12121999013|9400799043|51005399006|440099028|3412199025|10035399007|40105399003|102099032; x-microsoft-antispam-message-info: =?iso-8859-1?Q?yh+N4r4kIeyO3QeDXshPZIJVLwBFwE8ClAFzohUiyXjhS41RQ6U+oA3Imb?= =?iso-8859-1?Q?2aK+NG0dA0L9w22tv/0LFSB3mRVdPflQWjrbEagKbhJpfb0cM4FMU1va2f?= =?iso-8859-1?Q?sgLDtsmYjaQt55U3AfUB3Md1N0Cgy02x3XsRKJLM4VCTZh/Vjt/o1nOrb4?= =?iso-8859-1?Q?xIzzGApZAyyJaBYhx3suWGfWxC0ePkggxdHWSpW2fuDcXLU7KaIKSUZ73e?= =?iso-8859-1?Q?OCg1/HeMok8moGrmZmfWuW7/SoeQCA+iDDlk6HW7PrRgTbBljdrkprv1xo?= =?iso-8859-1?Q?NVwexUZw13JgO8nLffWnz+acH810+8H189y9pFxxgTuniXe7BN4qCuV7Gd?= =?iso-8859-1?Q?Sryot0iLn9Hs7aN+CYJeokekCHKSPOsBWosU6HN/b2oe8c1IOyEdRfhET/?= =?iso-8859-1?Q?iYn2csjzIF2VWujsFli16GaNMyuY9xMgxqOUnt6bDuajc1FrEW8dCKrQeg?= =?iso-8859-1?Q?beEfOI5X0PnNL8fqKvN1VBz2hsHWljFdBSdYJmnQPaJcZMJkgPle1otwLT?= =?iso-8859-1?Q?doxX4e5kVQDMype0rvBsxBfgaCuXDIqS0N8psMFFvcRw0rq1beTrlY2Xr7?= =?iso-8859-1?Q?r+CHS3GK6dFslpAVAPVzuDGE+y2sFyKwCfZu3ZwGT8usSUOoR9iHfaWVlt?= =?iso-8859-1?Q?dflNCxCMp2BvwEKmjNuH3RuCi83ESqsWpJXCZ7XbUqQiO7J37Np5lNgK+a?= =?iso-8859-1?Q?NUZepfYGEmAd9rwYEN3efUEbKnL3BQ/v/6aCqHTzxr4GnWWr6P9dfMqafE?= =?iso-8859-1?Q?vobLawSzgmpAO0UstxRR4MbPLdAsugh+AN9ttmbxVJg9uruvvhy8nRySY4?= =?iso-8859-1?Q?D0AGDshsCqMQ/EjUgnyEueOffdidt4m+zFdZ72w+/g16xYlgqRiFuqmtlI?= =?iso-8859-1?Q?Ub7u9UMvFC4Dfh5LCE3EWs8Y6gGYuXH2Vd5333ZSeNeTJZ9l0ypXxTrCWc?= =?iso-8859-1?Q?540OvbDpioa+xS/h495k+AIJxWJVgy0m49ofqfGZfSP4sn+GBsI8wO++ib?= =?iso-8859-1?Q?ou6mEQXCJ3VryIXYbBG64y6YHFSvBr/lf7yoVq55ZT0JeWTJsDajQ+6Qtb?= =?iso-8859-1?Q?W6qDdWApkyqfQpfJ0CKJnPjkBvUNrrQZrPEmexmOy0tMOrFLx1BPSQRWEv?= =?iso-8859-1?Q?7iPhz6blTEqWVLldI21nIj7XahkZv8lDIqSCMN+FvJpSS13l8DPIas10w1?= =?iso-8859-1?Q?EWE5zEzrd8TFOrPtrai3lxw4cPmwN9HERaFC2O/qXmP9rxNjbEJSPdiQG0?= =?iso-8859-1?Q?AN6uzTHALYgXqvmwY+QDrisoueYTZHUIYoyB+0jnCsk2/lSmhm00tKJ38v?= =?iso-8859-1?Q?dz8FxsWLHZ9nKNkz7tZre96XNN2WugBTbuZnabYETXArZImNtwhMQbKp0f?= =?iso-8859-1?Q?bmNBPo5VkmSLbIONrDmo7ys2nwv+aEIPEIu54aqsRT4JIL2DIVx5NnOGSa?= =?iso-8859-1?Q?UCi/mIk8z6BdL/30?= x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?3SeDNGhyTz1wMvihEKSTH3jP1ImwO2d3qvshAH2+789xN7jw7n/Z0eqJt1?= =?iso-8859-1?Q?D7bmhqo5Jm5mAlrkgQ7u+5SdCHK7gsuhNhFHtqCFyAgteB0MBiC32xpIkU?= =?iso-8859-1?Q?vPvnTAipvGBFgvqlhi+1xapte5iWB7CKzOHqGGr+7tqNDf2PQ5gvFhIhyE?= =?iso-8859-1?Q?MPxgB9SL9K2MqYNwUp8Bedc2BDR2dSsCniC3SNAClmzPa68H5cnbHJP6uy?= =?iso-8859-1?Q?c0EELfjLcj6edrNyV++B7PrWAvPrrkU7LxxPZ4OL9UKtzK+l6Pn40nJTsE?= =?iso-8859-1?Q?I5YzERHi+yjeq6PWlIyt6mCIjYzgsZ6iyjfTdXoNlW7sr1v+IW9T5oAB9g?= =?iso-8859-1?Q?lPe+VYFHiG1wHFQ6cAL3D6ZkxpDp5iuo8u7CBDMhq3AhagAF3bz9P1qNX5?= =?iso-8859-1?Q?vl4ncj1nIn2QPw6aWILT0KQUojw1ENQxzH8rdADiwWIIdpFgE2p6WdCghk?= =?iso-8859-1?Q?mD7zoxxbFs4dCAS9bIwMdJwJG2kbM3+FVvIqpLd1Nc7JstJDXlnr7gTaT3?= =?iso-8859-1?Q?YvTRqdSS38iSD8mC+M+2xQCnHq2j+lkM2gVxs1MOUEzXxhpyQSo4tav/+M?= =?iso-8859-1?Q?EvVT3RiWqt3OUZp65OVsoaw0pBU8x77WGxh6O6U4K6UxSt0Idyy8JLBJBv?= =?iso-8859-1?Q?prbmLoZgGKY40TyDDlI3Omj05QQe+d3HbMFxSq8d2Ew0rCUJJ7tPmsLqKR?= =?iso-8859-1?Q?lgi51qZEfLCyNJNU3Ra9O+ZFOMOoNd7k2ZkCEA/swC6XbE1RLtzVJWJool?= =?iso-8859-1?Q?jfiYu3tk7rVA9Vz69hUOOOEjsJccZUYlyF23i8hIgvf263Kuei1NoQAylq?= =?iso-8859-1?Q?4RAO6p6nRgg3fdtI+MWi+r7+x90eUrke+MOjEl2YjlwyYz9d3+Ub/WxMqS?= =?iso-8859-1?Q?8QpLRx18Cymj7S5l2PlHoTJLFBp+dme6ZEHjvfcVdoUzQ2m4CPisN13gsL?= =?iso-8859-1?Q?2Di8KvekoVkMrLV6/656NAtg1K3b1PfyH8F9ZKdWdI6FBPW5IsGzj9KMpY?= =?iso-8859-1?Q?3y6/C0FA0PUYC33igIDYzSetPXxoTcKDX30zEKJ6q9yvopjlnuYCvkbIkQ?= =?iso-8859-1?Q?hv79hFcyBw7Y2GhFp96yUritAcAi8kpU6ec8gXqUhie7s9KmFOciQ1ruP2?= =?iso-8859-1?Q?P1aWFHJMm0NnlUGPdm6HSxMNWsDDWeX/YvqUpzIyLQ+qa/o3EjF7BKOK+d?= =?iso-8859-1?Q?pIEbOhuKNFKt7itGDC5Kipo9IaiR3T+20gQEUw7qGYxVfKmlIwJBbdUKjL?= =?iso-8859-1?Q?4oXWlad0TYg9XDHZUfxcaLiJKWg7gKYaCIr3ipKf/v/F9UjkbAaLYhtKrc?= =?iso-8859-1?Q?0kap/13scwg+fGNgCcDCjApji6FD9z7VG1jBRvwMOOxe/83kKwIR5Kzx3N?= =?iso-8859-1?Q?YtFV1tQmgSq8NgGgcw6gK7l4B5qeJsLWB1gOH87q2mqsTnpCG1CxHL8FNL?= =?iso-8859-1?Q?8e/8+YQegDExROyDZlURAGEncfh+SeA28gD6Q4HaboXUvWDeszpwhftAm7?= =?iso-8859-1?Q?Q=3D?= Content-Type: multipart/alternative; boundary="_000_PH0PR08MB70206B42F5A79A518B610815889FAPH0PR08MB7020namp_" MIME-Version: 1.0 X-OriginatorOrg: outlook.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: PH0PR08MB7020.namprd08.prod.outlook.com X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-Network-Message-Id: 98c68fed-0d55-4d32-95ff-08de600e8cfc X-MS-Exchange-CrossTenant-originalarrivaltime: 30 Jan 2026 14:47:54.3792 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-Transport-CrossTenantHeadersStamped: SJ0PR08MB6496 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_PH0PR08MB70206B42F5A79A518B610815889FAPH0PR08MB7020namp_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello Egor, Thank you very much for taking this patch under your wing! > I propose, by analogy with the existing 'g'/'G' modes, to use lowercase > letters for client-side data generation and uppercase letters for > server-side generation. Furthermore, I propose considering making the > "one transaction per scale" mode a separate setting. This would result > in the following modes: > 1. g: COPY .. FROM STDIN .. TEXT, single transaction (orig. mode) > 2. c: COPY .. FROM STDIN .. BINARY, single transaction (added mode) > 3. G: INSERT .. SELECT generate_series, single transaction (orig. mode) > 4. I: INSERT .. SELECT unnest, single transaction (added mode) > And: M: multiple transactions. A setting that, when used, makes a mode > run with a transaction for each scale instead of a single transaction. > This would yield 8 possible combinations. Sure thing. I agree with your proposal to add more flexibility to parameters with single exception. For UNNEST test I would suggest to use "U" instead of "I" as it might be confusing later in case of another patch from current CommitFest will make it into the master. I'm referring to: https://commitfest.postgresql.org/patch/6242/ It uses parameter "-i" to start use multiple threads to populate tables. > It would be reasonable to first collect performance measurements for > these modes and then decide whether to keep them, before proceeding with > a full implementation including their selection. Since logic will be slightly different by following your proposal new set of metrics will be required. That's for sure. My main motivation in splitting one huge transaction to fill tables into smaller ones comes from another idea that was put on a backburner - running data population via multiple threads. This idea is implemented in above mentioned patch by Mircea Cadariu. By amount of changes in that patch it is clear that we're quite equal by number of lines. Hence putting the change into my patch would be overwhelming for any reviewer. Another reason for smaller in size transactions ("one per scale") is my experience during generation of test databases that are much bigger than host RAM (e.g., scale=3D5000). Data population phase is not just slow, but more than often has to use multiple checkpoints for such single transaction because even my max_wal_size was smaller than size of such "change". One might argue that my DB is not tuned properly, but it's a topic for another day. As a side effect of decision to use multiple transactions raises another issue - inability to use FREEZE optimisation for COPY commands which leads to Autovacuum storm in turn even during very process of data population. > Thus, I propose reconsidering the approach to data generation modes > and adding a setting to control the number of transactions. > I also suggest conducting new, more accurate performance measurements to > inform the decision on the necessity of the additional generation modes. Agree and Agree. Both make perfect sense. Best regards, Boris --_000_PH0PR08MB70206B42F5A79A518B610815889FAPH0PR08MB7020namp_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello Egor,

Thank you very much for taking this patch under your wing!

> I propose, by analogy with the existing 'g'/'G' modes, to use lowercas= e
> letters for client-side data generation and uppercase letters for
> server-side generation. Furthermore, I propose considering making= the
> "one transaction per scale" mode a separate setting. Th= is would result
> in the following modes:
> 1.  g: COPY .. FROM STDIN .. TEXT, single transaction (orig.= mode)
> 2.  c: COPY .. FROM STDIN .. BINARY, single transaction (add= ed mode)
> 3.  G: INSERT .. SELECT generate_series, single transaction = (orig. mode)
> 4.  I: INSERT .. SELECT unnest, single transaction (added mo= de)
> And: M: multiple transactions. A setting that, when used, makes a= mode
> run with a transaction for each scale instead of a single transac= tion.
> This would yield 8 possible combinations.

Sure thing. I agree with your proposal to add more flexibility to
parameters with single exception. For UNNEST test I would suggest
to use "U" instead of "I" as it might be confusing late= r in case of
another patch from current CommitFest will make it into the master.
I'm referring to:

https://commitfest.post= gresql.org/patch/6242/

It uses parameter "-i" to start use multiple threads to populate = tables. 

> It would be reasonable to first collect performance measurements for > these modes and then decide whether to keep them, before proceeding wi= th
> a full implementation including their selection.

Since logic will be slightly different by following your proposal new set
of metrics will be required. That's for sure.

My main motivation in splitting one huge transaction to fill tables
into smaller ones comes from another idea that was put on
a backburner - running data population via multiple threads. This
idea is implemented in above mentioned patch by Mircea Cadariu.
By amount of changes in that patch it is clear that we're quite equal
by number of lines. Hence putting the change into my patch would
be overwhelming for any reviewer.

Another reason for smaller in size transactions ("one per scale")=
is my experience during generation of test databases that are much
bigger than host RAM (e.g., scale=3D5000). Data population phase is
not just slow, but more than often has to use multiple checkpoints
for such single transaction because even my max_wal_size was
smaller than size of such "change". One might argue that my DB
is not tuned properly, but it's a topic for another day. As a side
effect of decision to use multiple transactions raises another
issue - inability to use FREEZE optimisation for COPY commands
which leads to Autovacuum storm in turn even during very process
of data population.

> Thus, I propose reconsidering the approach to data generation modes > and adding a setting to control the number of transactions.
> I also suggest conducting new, more accurate performance measurements = to
> inform the decision on the necessity of the additional generation mode= s.

Agree and Agree. Both make perfect sense.

Best regards,
Boris
--_000_PH0PR08MB70206B42F5A79A518B610815889FAPH0PR08MB7020namp_--