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.94.2) (envelope-from ) id 1tKseo-00BL8I-7v for pgsql-performance@arkaria.postgresql.org; Tue, 10 Dec 2024 05:19:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tKsel-00G5bF-5N for pgsql-performance@arkaria.postgresql.org; Tue, 10 Dec 2024 05:19:36 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tKsek-00G5a9-Jy for pgsql-performance@lists.postgresql.org; Tue, 10 Dec 2024 05:19:36 +0000 Received: from mail-vi1eur02on2135.outbound.protection.outlook.com ([40.107.241.135] helo=EUR02-VI1-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tKseg-0021UH-QE for pgsql-performance@lists.postgresql.org; Tue, 10 Dec 2024 05:19:33 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=XJzL4zoz4oNTXSEvhJn9LwBxTqR1flbqz0++NyP+8Ev8kajU6E9oAyZBUxTYuNLTQKsHBZRVxACjqKaI8NhvnWABSM1XKdIU+wZvrnCTK6ZHdrCkl3bR4nWwvLZ2Tg98JYvtAwXcEjIj+hMnsTRKHWmHkycMlb7IQ113FGPb029uDPoDq1PSE/9eRQVjF1cOSiYGk/wQawajqSUynf1ak/Xi1FaSNhJL7B9kgcAHiqYemIfy0Y8/SCePdKxkBixM7wLPJKSjMWzwfu/HFcnfzPvPTdNJOMLDxP2O8sg2JJi+FRxwT1bOmRPSAn0udbfg4v8jB7vUB/SA1ewxjI1+9A== 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=M8Di+w6tfGYMz+FqJR//WlFbUD3FPMBuy+r570x6kD4=; b=ZCJ8ch+9UVAowgn77QBLPL9SSCs7C4sA7Bw6Qoc2ZC8vI09fe2jAGYgmsBM9u6hqgnou9Uf5z6u+XLZMDY/Y7/FjFMtMAu6Dwmvk7VtvpRmRGvnxnazUbFP4P5EOk4TMNM5yiVM9r7D/15SApR4kPEQrjwvGUriZ96xSjg2FfJgOUeaFDNqOV9CIXE9rmbWGo9xlBl5IfCHYrrKQKV82L5TbmlPqCtzS9MJL29b66HSY2zjurJmzbpGb42nNKxAwt1Ev9gPNPGeAIpgBKIPiC+i2DI8dCP/Ks3vBlLEyIloU9N9IDkRu/X3naIOW3j0GOgI46RKNzHLaGzrckx58EQ== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=nibio.no; dmarc=pass action=none header.from=nibio.no; dkim=pass header.d=nibio.no; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=nibio.onmicrosoft.com; s=selector2-nibio-onmicrosoft-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=M8Di+w6tfGYMz+FqJR//WlFbUD3FPMBuy+r570x6kD4=; b=jYjJiaCUqi4cBI/8yYUJ5twDFvzz2POijaPrWH2KMwvasRYFW4CI/T608DKGzoRK1TAJlLYdmY6btNc++6J17KSQuk0F1RAm8L2S0CT+RtX/rA98rBbUP59nn4R/24btaKhWDVTO8gtjUfHK6Qlxvw0Q+KGsnq3xUUddaX+DOGM= Received: from AM7P189MB1028.EURP189.PROD.OUTLOOK.COM (2603:10a6:20b:174::18) by DB8P189MB0999.EURP189.PROD.OUTLOOK.COM (2603:10a6:10:160::20) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8230.20; Tue, 10 Dec 2024 05:19:25 +0000 Received: from AM7P189MB1028.EURP189.PROD.OUTLOOK.COM ([fe80::7021:938f:7ff2:6406]) by AM7P189MB1028.EURP189.PROD.OUTLOOK.COM ([fe80::7021:938f:7ff2:6406%6]) with mapi id 15.20.8230.016; Tue, 10 Dec 2024 05:19:24 +0000 From: Lars Aksel Opsahl To: Rick Otten CC: "pgsql-performance@lists.postgresql.org" Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows Thread-Topic: PostgreSQL and a Catch-22 Issue related to dead rows Thread-Index: AQHbSiR8fAE8+rgvhkWYwOHzJhBte7Ld+C6AgADzTbM= Date: Tue, 10 Dec 2024 05:19:24 +0000 Message-ID: References: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: authentication-results: dkim=none (message not signed) header.d=none;dmarc=none action=none header.from=nibio.no; x-ms-publictraffictype: Email x-ms-traffictypediagnostic: AM7P189MB1028:EE_|DB8P189MB0999:EE_ x-ms-office365-filtering-correlation-id: 68df9f30-885c-403d-18d2-08dd18da3631 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|376014|1800799024|366016|41320700013|38070700018|7053199007|8096899003; x-microsoft-antispam-message-info: =?iso-8859-1?Q?SEx3Lv46Oyzp61dfHGLSuirIcNFs7wGdnpPNjD69P3y58/XU3X0Bbnh8g2?= =?iso-8859-1?Q?4ZUGQYmmqWM/7DtJ57tUuQev9zwCHEkAh5ApW4YVE90Vtq65guigOCsWRQ?= =?iso-8859-1?Q?w4zqPPLyTXjYYjYDmigMKsXTJbs9yhLh34n0dyHFG/Fk0ea/eH/YKZCb8W?= =?iso-8859-1?Q?F6SeEPKUQQEKjq6mzIni4ikCIyUIZKkBEdMk6W5OZ/YP65/kab3BIZhgqw?= =?iso-8859-1?Q?j9yj6Ld61GYW5xKqfZtZhhA6mQSRUp997P9VJ2wl3mM/Vp2OJjsVoBZDDJ?= =?iso-8859-1?Q?BziehfxQoLLRP/as4YvDQAGX/EwqYgQvVJjNh735tGYr8Yy15Ej3kANDQJ?= =?iso-8859-1?Q?QwOvWPRHlzUuliVOzZ3jRnH3PSKRDYyFjAgcmBdXk7vuYvZUNvei4+pRLW?= =?iso-8859-1?Q?vgPESgEhTdgFtnNTpfyDrPvgWd70YkIfK91ciODlwsP/W9fYZyDPf3YiQx?= =?iso-8859-1?Q?veJ1nGdQJER+Llaj/HVLmJ9tfJaL+N2ke0bywXPzsckorphyhvWEh+rof+?= =?iso-8859-1?Q?Jnu5iYWixhRynIpVVECO+Jfl3HKYaOTtd+YLjTP0Ts0aVMywkxjqolcxC/?= =?iso-8859-1?Q?U6mBeuv+UkIf9LPfsLQ4kzSm2KS0l2Orl7l1PM+aq4f4iNwmetDXl/hnn7?= =?iso-8859-1?Q?bOqRHO7A+6ifCLSggfF5X5yz1Ml/wxUrxG9Jw5U6sDvnTq7v6AHLFCG5Gm?= =?iso-8859-1?Q?cgK7VpR4LAolilKNAdEaf9YHYvMAjIGy1/Q8V6uzzTcppVxoBcvpYtuNYr?= =?iso-8859-1?Q?rGPjgK3yPkNtB32kacJ583zWEpz83Ffr4qbrn2XD2ryvkvHmHpI9LNinKL?= =?iso-8859-1?Q?anNMJdT7r1jloWpxXo9W6h2M5cZ5KOaks+2ST/eYyZGaR4gJn01yZQjC2L?= =?iso-8859-1?Q?I5Cu4XLJ6nsX8ERrKc9lhbR9V29Ft4t0+pTNt7/g1GCAuUPECcNtm6cRMA?= =?iso-8859-1?Q?8PC3jVEYmxSlcGsl59Gtd9nkDBbl3ctzPnRxcu3awsAhq8c0lWnmqLxStA?= =?iso-8859-1?Q?HmwjYb1LTL0t7/y6GYrmDBtUKk3gobdn6DQdRSmP9qfQ444YWqOPJk2/ml?= =?iso-8859-1?Q?EKlg9MKbAEtZiPEbCgiqCJI02NglnZ0AyYyrdVRuDUt99pu0JubNtN9jXO?= =?iso-8859-1?Q?+tV7452IXYieNIN9wGsL+22i/P3TcG+7mweE2QNr38a8g0pGq4vaiIkL3z?= =?iso-8859-1?Q?eF2+GD20mN8e/C8hKIPOsrmAmccxZVMfq/jlDp8lQ30J8riRX0aLVbTCYl?= =?iso-8859-1?Q?TyQlPjeXcK1pxeIALl5vbWDkbXZPGRLAQuomcctP+PfF/uOyuxWVF8EBvU?= =?iso-8859-1?Q?ueohzIPKQFyvAZ+Z+cdEDcTmIN254RwgEsGskNYurCzPiWW2uMOnkc4FPK?= =?iso-8859-1?Q?0I275LKHM4vunqZEgGzwGp0FrpfzkPBXDJvBZni0zHaYHSo7VqEnxNO6fM?= =?iso-8859-1?Q?FH1LNJRq/6LkJtRTzCXtrIAXWd3fsKSvdIBsoA=3D=3D?= x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:AM7P189MB1028.EURP189.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(13230040)(376014)(1800799024)(366016)(41320700013)(38070700018)(7053199007)(8096899003);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?6vmEE1HIQ7RIHck+W31nz8FCqC1d8iubwo/LYWGe4WLXaK7aUAu7ZobDjd?= =?iso-8859-1?Q?6xMEPULbjzMGxRnIpj33P3gIpXDT3OErgpPncQPr/c9ERccHFKJH1+ju9U?= =?iso-8859-1?Q?027gA8OAyrMjJtfOqnnCPvh7mR6SO0T1F+GzfjVKZkPPrHQE78GRpSDG2L?= =?iso-8859-1?Q?LNyo7WBKhRW5+iuybwxpbJnnGNP3cEUnpP/OugQ2XkcouPl4sU0uYWgORF?= =?iso-8859-1?Q?f5Hep0HiHrxWG7f63jMJMWbJJ+bT7sDNF7zgEpFc0rUaGz0eYgDbvL01c/?= =?iso-8859-1?Q?JB6aRz7gDHMgs3BenXPdEPTiRnSai0QJ4Z0bwQLfuBOvFzaUIg9os//pPV?= =?iso-8859-1?Q?4VkUV0O6J5GY3JtAkr4McxXMhdTcwaKRLWXORHWGa3yJMxCArACW0Dlyb2?= =?iso-8859-1?Q?VYMJQu5f1lDGGAF5pEAdg6s4p/zONVj9wY1eSDOzkVJJXx9y2IY2MCwrDl?= =?iso-8859-1?Q?GdfWc0d0+qJLT+b8LC2oDbJAbCL9IYUDdxcAkAEws+utA/jdqfQRLX0KYH?= =?iso-8859-1?Q?NWkqvMLrlLYwgGxBXTJmjNYFCyMeLsOGMKTJb9dorZutwYQ89T+CUprZGy?= =?iso-8859-1?Q?CqxSPYfpne2QhyX0mLqsVWZqY7DPdHK2WmljcPlJQc6vI/Xclz5ziELvRU?= =?iso-8859-1?Q?NpxLA5kHmG/iADJ0qwmJZIRui9Sl+OuFVJAVIUI+tF8aCIK3pxZxB5IJKS?= =?iso-8859-1?Q?NBAg9YB2bTAvZV93L2x4noIiIPeCiLfUl6tm/kqr/z6l9lSUZ6HJr2PBQX?= =?iso-8859-1?Q?ibJFC5ser9ujq85LaXRyyma1ntrcgK4MmfYPG9fLGmynJ7UdDyQxAFmKFa?= =?iso-8859-1?Q?J2f3HkH+bV8cGkACxevaTjeo8QLBQjGoLaa6GxT5K4juKtlYkfW1oxs0kL?= =?iso-8859-1?Q?OA5Lii89f6iR8EgnIvy7uRIMokQCqyHZXl/SFKBg+FgJ//EqA2ea3gouS6?= =?iso-8859-1?Q?J49Li0Zk3iKzkkjF8HO89xkogkDdoWmvWZxq8vX9qU2rdBRBWsLIIobpyM?= =?iso-8859-1?Q?IXLQZ9FJHvb4j+EOoRhSZDhEbTDSaV7VgKx3AbKBl0WzriJvkNbyVtB7nF?= =?iso-8859-1?Q?h1qA1OncOhKd6U137qUvinVZyMxyEmy2JH3PSmndisLtKYiTAmB7rXrD9y?= =?iso-8859-1?Q?AyZ8woInB3wNLFkDx4DU/7Wv25ZZPCNYIIoTwzs+0Kv3ojHhekj8u6ehUL?= =?iso-8859-1?Q?Iz7gxDeS4DkHf1IcvkwXSUJcpgmA3eSnR7zKOmon/3R/S96i/fupbKJiP4?= =?iso-8859-1?Q?j7NMD5xJWQcNKmuJJz2n4GO0d3GAU4vG8AoAZWNk+2BmdcrHHamNU5oDdV?= =?iso-8859-1?Q?MZzL/2Hkycl5uTKp/qVnPE5NCjzAnpUUv3jlnqvpdfTH1f24a3X1r7OmLj?= =?iso-8859-1?Q?Q9Hlp9rutXSFv556z+sMMZJAXLxj5Nwq/AqXUmoL+mnpBsG9jABM8mWJ5Y?= =?iso-8859-1?Q?xU4d0ldf5teX1PWRgKxSswEWj11tH3Z7jCZ7w0WalEWVmvnFPpB7gmTgv5?= =?iso-8859-1?Q?q02z5bBXR7WlmJB7BVGW1GxpJXd6tC1heHuRcJI9tr6KDrrFdkGIPgHjMx?= =?iso-8859-1?Q?6v7LRrHMvv0xiv4GEZrHT6r4CU3nb2wZqkPT8bs+Z5j7uYsS/hcbtQC9NP?= =?iso-8859-1?Q?PKT21II0jfQ4FRwWo7mjc3oZti26itWVgB?= Content-Type: multipart/alternative; boundary="_000_AM7P189MB102832DD646EF0D81EB710DB9D3D2AM7P189MB1028EURP_" MIME-Version: 1.0 X-OriginatorOrg: Nibio.no X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: AM7P189MB1028.EURP189.PROD.OUTLOOK.COM X-MS-Exchange-CrossTenant-Network-Message-Id: 68df9f30-885c-403d-18d2-08dd18da3631 X-MS-Exchange-CrossTenant-originalarrivaltime: 10 Dec 2024 05:19:24.7148 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 6df9a8ea-1ed0-462c-85a8-831200d5f7c5 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: DiLoksMVRlL5OrABUxj7qQdva4F0cFKMfcxKVC12NnBZdm7Rt6bjQMYQPUEIoHhdchazmUf42rrv0fhzEgXB0g== X-MS-Exchange-Transport-CrossTenantHeadersStamped: DB8P189MB0999 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_AM7P189MB102832DD646EF0D81EB710DB9D3D2AM7P189MB1028EURP_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable From: Rick Otten Sent: Monday, December 9, 2024 3:25 PM To: Lars Aksel Opsahl Cc: pgsql-performance@lists.postgresql.org Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows Yes there are very good reason for the way removal for dead rows work now, = but is there any chance of adding an option when creating table to disable = this behavior for instance for unlogged tables ? Are you saying your job is I/O bound (not memory or cpu). And that you can= only improve I/O performance by committing more frequently because the com= mit removes dead tuples which you have no other means to clear? Is your W= AL already on your fastest disk? All of your parallel jobs are operating on the same set of rows? So partit= ioning the table wouldn't help? The problem is not IO or CPU bound, or related to WAL files, but that "dead= rows" are impacting the sql queries. About partitioning at this stage, the= data are split in about 750 different topology structures. We have many wo= rkers working in parallel on these different structures but only one worker= at the same on the same structure. Thanks Lars --_000_AM7P189MB102832DD646EF0D81EB710DB9D3D2AM7P189MB1028EURP_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
From: Rick Otten <rottenwindfish@gmail.com>
Sent: Monday, December 9, 2024 3:25 PM
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performanc= e@lists.postgresql.org>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead ro= ws
 

Yes there are very good reason for the way removal for dead rows work now, = but is there any chance of adding an option when creating table to disable = this behavior for instance for unlogged tables ?

Are you saying yo= ur job is I/O bound (not memory or cpu).  And that you can only improv= e I/O performance by committing more frequently because the commit removes = dead tuples which you have no other means to clear?   Is your WAL already on your fastest disk?

All of your paral= lel jobs are operating on the same set of rows?  So partitioning the t= able wouldn't help?
 

The problem is not IO or CPU bound, or related to WAL files, but that = "dead rows" are impacting the sql queries. About partitioning at = this stage, the data are split in about 750 different topology structures. = We have many workers working in parallel on these different structures but only one worker at the same on the same structure= .

Thanks

Lars
--_000_AM7P189MB102832DD646EF0D81EB710DB9D3D2AM7P189MB1028EURP_--