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 1tKsrf-00BMHd-Fa for pgsql-performance@arkaria.postgresql.org; Tue, 10 Dec 2024 05:32:55 +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 1tKsrc-00GIHz-UJ for pgsql-performance@arkaria.postgresql.org; Tue, 10 Dec 2024 05:32:54 +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 1tKsrc-00GIHq-FN for pgsql-performance@lists.postgresql.org; Tue, 10 Dec 2024 05:32:53 +0000 Received: from mail-db8eur05on2072a.outbound.protection.outlook.com ([2a01:111:f403:2614::72a] helo=EUR05-DB8-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 1tKsrZ-0021Zn-Fo for pgsql-performance@lists.postgresql.org; Tue, 10 Dec 2024 05:32:52 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=eV6YqWmvhaEWNCKUQ9LVkPfdR8mO642UgKypyZjrc3NqEAIu2eA4FG82mTG3W4ht7Z9UKGuuZQsU699afAB4QyhogkpcwX+bqLogG8mmp3R8uBAKdr3nMyWrlYam77RmTPknGHobw0e/pIObV0+AbxtVKqMubnJLuAdPOUHFcioN+9hc6XqEP60zc2O+w2A0JW8OXeJaXHkf3qh6RdNgghtfTe33CLSSUy7iBFPbkG/vFt+IW7q8BcoiyTKqfoBQ9oCORkib8u1alzuOY3DeZttYEgob/AYMVskvzsTxu3VsBRwvtSC5VHEoLR4Vvr2WaipSPrXfS+a6B7K4qaTvDg== 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=CLBnAjEvVTDEC+g9m7+RESnKFv5IVUQk5Dq0e8/PPnI=; b=oqEHHr9KgisOJ8OtuMARY3x+BUWkn8YIx3NFyih4CO4XxfJnS1mjrhrNF7p3I/jj3rgoEHD+u5YmtUnzNUmVCCdvVALZeBwbvVCPzUJ11SKwV0NFFd4UMhTlNyTOpTbD7mcXOihuGGg2eWKLKi5rb4ELNnYV8DnuZqbdH7ceaaTbiu6w0CaueRAPGb4417gyjgAKlBjvU9OsJwsio0yRr3kN6DdoDiwHX/JuNV3prrltutBf5YZaE0/eGp8TzL3sJGS0zLmG8Y03/BX0nP6TrHt2Ow+J/WeylE1XZoorPHvyiK5FmKT2KTfzPwDIeviJKGS7qnYOd7bqPqFkA7lnhQ== 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=CLBnAjEvVTDEC+g9m7+RESnKFv5IVUQk5Dq0e8/PPnI=; b=DQWmD2CNtn+F+J/TaDN/nNmzKy0hm+suk1Mc5hf7kP6ExZgpzyjh+qnYgHvUUxFo02Jh3zbsrBHsvlo2uSMwhR87soJn+5uY23kk7UVD/VlujBlSvq9d5BCNrPozsntJcXemFCfCS1AAiuvTxaIMxwuZSMaM7zgfW+Al69vlk0c= Received: from AM7P189MB1028.EURP189.PROD.OUTLOOK.COM (2603:10a6:20b:174::18) by DB5P189MB2575.EURP189.PROD.OUTLOOK.COM (2603:10a6:10:48e::20) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8230.10; Tue, 10 Dec 2024 05:32:45 +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:32:44 +0000 From: Lars Aksel Opsahl To: Tom Lane , Christophe Pettus 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+jSAgAAaboCAAN3zvQ== Date: Tue, 10 Dec 2024 05:32:44 +0000 Message-ID: References: <5EE4CC0C-E624-4E82-A2E8-B2EBA44D3521@thebuild.com> <3908773.1733760455@sss.pgh.pa.us> In-Reply-To: <3908773.1733760455@sss.pgh.pa.us> 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_|DB5P189MB2575:EE_ x-ms-office365-filtering-correlation-id: 26620f18-7739-438a-62a0-08dd18dc1328 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|41320700013|366016|1800799024|376014|7053199007|8096899003|38070700018; x-microsoft-antispam-message-info: =?iso-8859-1?Q?VJA5MnA4ubwawqR4F/5OhaIXqM2oa618DnWqSPhJnhH2mSpcVQ1fdj85zh?= =?iso-8859-1?Q?GoxkJ2c2ch4ZYJ2g9PTG4d/goZgDVjB/TS99wkyDBOe27L9OwKov0frU4A?= =?iso-8859-1?Q?NBRdLACv5m/JpI/QRT/O3SGinWtTID7W6p8GDVj259xEiSx2KvWr/UMDdL?= =?iso-8859-1?Q?JGr0a+oVHUahQB/c4zZWcdHyz7Pz0AOZjkTlQEV1oiNbjnZyd84kyDQbeS?= =?iso-8859-1?Q?2312I7ARDZya/OW8KT+QwWqos+eGjwBa6MAl8cLhqP15sZrDmatCXXSdHN?= =?iso-8859-1?Q?SKp006Ki4bpux20m+8sznl1Di63UG1ZLUoTBQeNvkVADJ2/5Ijl6KI1l1H?= =?iso-8859-1?Q?Doxdtc3/OkvWFRMZs5pVKVK8V8x7Z1tMXAIPjKtm3jj0q51mcF/N2IDQeL?= =?iso-8859-1?Q?Ou4ZF7wQahW3WnxckmhaLm30h7MX3l1sdK8szg3D9+i3CFdZuE3qm0U+rX?= =?iso-8859-1?Q?Z1Ds7cNYZ4XtOszXWg0l92ZTetwJECXlsU8oOirD4HO1jmFpDkkQrB81rG?= =?iso-8859-1?Q?0ECjNO3J3WnZLCv2V+tJ82ghmlJbtgzypi7TJnVngij4ZXUiI9kXGTlXVS?= =?iso-8859-1?Q?KtsAq42C9zsaZZ7lERT9ZxVIK9bX7MZSJlO5SH296Ia598mSHWiMf0L1Hs?= =?iso-8859-1?Q?c233isctJFnVcnJ+KWCKgL+Q6X0/mnIp+Skv8P+Z4UK13rH9Hrone1ZVgT?= =?iso-8859-1?Q?ufx0AoFrzs5Sb6NF6sSB0/bgCIHOg7i3gjAWFP/N2mOm1bGomcBdOuvDT+?= =?iso-8859-1?Q?enesu6IyzA89fWWtpLb4tBO3mKJ2GTJHnTpetgbqXqXIHXugBKFphYM2pS?= =?iso-8859-1?Q?z/t4m4ZE/N+q9nU1BV8YvBPU7tr+YufDO/4sA0yu5R3xdk6izpS/RhHVV9?= =?iso-8859-1?Q?QIj6Kfp24JlzjO2bb1lflPrXIlY78/4gMnOyiPHFv+7WG0jD7MyIJpTLLj?= =?iso-8859-1?Q?fde/RFyiGu2BEAT9PHxsZ7SnP3jDj4Zr62CEUfghiYElbKj8guVWE0QKVN?= =?iso-8859-1?Q?iScnGY/fQaKF/qJfdseX7U2ss3P6An+fU/U7wqjrXambMf9W5ThtM2rH6f?= =?iso-8859-1?Q?kM1ubx278MKNX9Bx3LEhQQXr45p9kzvcrfnGhnpnGmg1Ks0g6KKCKmb5un?= =?iso-8859-1?Q?GdOxf7XVzckbgoWDQiZ4xg06WaU+61QLvetNWaRZJI00zTyisxCzaI8NM6?= =?iso-8859-1?Q?23Rp1as4e13aq3LjNvGrO0FDzygcYbcDxQ+EDlZFMm6E1a+NXa54TahMjj?= =?iso-8859-1?Q?91ZHQ8tLbjs1jKTyxvJigyWQRVvF9PBOjWHvUK7Un3ixyijAbR1cne2AsI?= =?iso-8859-1?Q?uMSAWjrqoUYwfgp9VHha9bO3vVFNCwqYcEq/i0Xr3SY0lBwIwEJLoYGkjX?= =?iso-8859-1?Q?yvfHnonW0lXUMrLjeOQhTIfy/cRZeW7aftsRnUwP4EGh83Q6Nu3MvTHqAT?= =?iso-8859-1?Q?O5jMygchDrHzWVu/DDWStqEVm8y0VlkQlhqwag=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)(41320700013)(366016)(1800799024)(376014)(7053199007)(8096899003)(38070700018);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?V6J4dcnpbCX/EfxHv1rvA0gR6k/y5eTDK9k5APEZ1pfm0kDFyo4MwoDhc0?= =?iso-8859-1?Q?w9Y5QGocIu+isXoweXWj5hDv3N5gAGadU84buWW5DYQck+TS2W4R1Dmivu?= =?iso-8859-1?Q?9G/8q+pc0BaBhz2SDlluTm6c3UvBdXnCFFZ3U/zhBe1UIpTxlzn+FV+wfC?= =?iso-8859-1?Q?22eJb9ojvlIzRej5HrnkGUALovWn1V4EPqTdfgKn56aux/+u8MrD3OIMvZ?= =?iso-8859-1?Q?ePNJ6GViISs0Z7M1WNFRyl3Taca/tlyN8AnL5N9/Pm1xldF7HUswB5St7e?= =?iso-8859-1?Q?1JEPZ9MJKPkaPDQ3kuQCZ9EarGn8xKbxWX8ADOUeECmS5DAsd2pXy9T4AD?= =?iso-8859-1?Q?kUiTfVaJNlr7hX60jzolckefnlKH7qrThsa3xO+Y/Xu/eefynb88qeF3/T?= =?iso-8859-1?Q?lgLuff60ZcwC2jHFwpfR/S7xLrEdZtdAatLVSiaE+VQ+3selVNVFUt4V6e?= =?iso-8859-1?Q?B/Q+0xpmxr3+IIdEN/ZoKG8D7M0CPrA4hGlkKcJLRCpFVGe3REwW/0f++G?= =?iso-8859-1?Q?Q6EARggbu/Vt1JK3HVO3co0OasrmDL263e0GuFOMlF1QkhDdx6Zqegfa+q?= =?iso-8859-1?Q?Qe7Y4H80UZEtt8rXI03B51J5iLSE1mH3awPo6InOFJIJ3BmEsxR9v99hsx?= =?iso-8859-1?Q?jaoJXQBB8BEfq+ZBO7XmvkAAUi1ABrdJKTojqvr3l6438qB2k6kqIBZ8Ea?= =?iso-8859-1?Q?jBUAeJMFrR4/EsfE3q6eUNMO3RbNB/gTtHBDAH4g9EQlfCpAkEMpQQWyiI?= =?iso-8859-1?Q?qI0XKjZHi96E0vwdBxEIt2Ry0gCjWVGesr5hH5aF661P7bm/QIT2mUxbrP?= =?iso-8859-1?Q?pez9iULxvMqkQn3sdWo0/R5mGmCnpwMlCOHYfWm/5CadH3KWCiJQPB0nir?= =?iso-8859-1?Q?pTa0jVwOXI8So32R2NUlHzPMguTYhrWlMtYSbFu4uLm6Zz7j1rcV+WfbwC?= =?iso-8859-1?Q?KljL+9ikpXmJ6yt6zun2cPFnH0x9GHaRaWDUp4BN4PFEz2eerD1r7IERiC?= =?iso-8859-1?Q?nOGcgbdyjxy6nwu6PJ6DIpHBE4txFdXeYfMK87ru33A8RLeHPAucL/e2oe?= =?iso-8859-1?Q?m0s7zfjTrPSB+2JiK8iKAfIdgKmyfmJDguVKG71hdoM5eLZDiblfgxACwP?= =?iso-8859-1?Q?vskFQuqoIsX7fmUa55yc4mnnVlHE009be8CNRjwjqDisvyW0A5K8Ep8sid?= =?iso-8859-1?Q?5+mdkmpF4rz6oi+QxotA33B86eHFSxxF8t1vpHlQW6WY1tk9/0EHE6q/XK?= =?iso-8859-1?Q?W0COBId40uXs+GRdsNtDNOE76T8UnM8iXrFztPpHSGaQN7yILCNazQ2n2c?= =?iso-8859-1?Q?JyQefL8rI3ShjXwyUa/hpgFZL9/vQDFa9yoOlt25qWPc9zKcEWku7hMm1Y?= =?iso-8859-1?Q?lAPP32xuX5VM8BqPiKoDcN6WtZa2tQvnXathNLIwMM09DvFJUHjTNOhk2N?= =?iso-8859-1?Q?R4bVl8sOsZVZ9xNQ1xDa6jSR/dGPrPelhHU1xYj8T6KR8xxNT9SKSHVWoM?= =?iso-8859-1?Q?fVd7GVkiJltQNECtaHT3aeEb7emxIdiVNURmTS+G/L5xYFNPD+j86RY2lM?= =?iso-8859-1?Q?kcWq9JoIKjVs1Fh2d2/fWOljMlKtdL7f9E9zOpHb7NSgsg/GP9zTeCulz6?= =?iso-8859-1?Q?6vDob782iUa1t5daG1LR8lgTEv9D1+hrFY?= Content-Type: multipart/alternative; boundary="_000_AM7P189MB1028B388987982CEA5541DEF9D3D2AM7P189MB1028EURP_" 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: 26620f18-7739-438a-62a0-08dd18dc1328 X-MS-Exchange-CrossTenant-originalarrivaltime: 10 Dec 2024 05:32:44.9509 (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: JY+GBwpcXVnlv5gdGPOkIhyhwtEkfkXsJ9StYEUfQ7B+Ps5yUHPDNGwlPqq6YD1NDL7q6onbnCc5ui46x6NW9Q== X-MS-Exchange-Transport-CrossTenantHeadersStamped: DB5P189MB2575 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_AM7P189MB1028B388987982CEA5541DEF9D3D2AM7P189MB1028EURP_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable ________________________________ From: Tom Lane Sent: Monday, December 9, 2024 5:07 PM To: Christophe Pettus Cc: Lars Aksel Opsahl ; pgsql-performance@lists.postg= resql.org Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows Christophe Pettus writes: >> On Dec 9, 2024, at 03:02, Lars Aksel Opsahl wrote= : >> If there were a way to remove dead rows without requiring a commit from = totally unrelated jobs, it would be much easier. > (Strictly speaking, the rows you are describing are not "dead," in that t= hey are still visible to some transaction.) We do only very coarse-grained analysis of whether a row is "dead". In principle, if vacuum had access to all the live snapshots of all sessions, it could realize that a row really is dead even though it's later than the current global xmin horizon. But discovering that would be quite difficult and therefore expensive. Notably, sessions would have to expose far more of their snapshot state than they do today, and there would have to be interlocks to allow other sessions to inspect that state safely, and that'd probably put us into much the same sort of too-many-lock-conflicts problem that the OP has already. I don't think there's any free lunch here. Maybe there's some other compromise between amount-of-state-exposed versus dead-row-discoverability, but finding a better way would take a great deal of creative effort and testing. regards, tom lane Hi Thanks for the clarifications , back to more divide and conquer work then. Lars --_000_AM7P189MB1028B388987982CEA5541DEF9D3D2AM7P189MB1028EURP_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Monday, December 9, 2024 5:07 PM
To: Christophe Pettus <xof@thebuild.com>
Cc: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>; pgsql-perfo= rmance@lists.postgresql.org <pgsql-performance@lists.postgresql.org><= br> Subject: Re: PostgreSQL and a Catch-22 Issue related to dead ro= ws
 
Christophe Pettus <xof@thebuild.com> writes:
>> On Dec 9, 2024, at 03:02, Lars Aksel Opsahl <Lars.Opsahl@nibio.= no> wrote:
>> If there were a way to remove dead rows without requiring a commit= from totally unrelated jobs, it would be much easier.

> (Strictly speaking, the rows you are describing are not "dead,&qu= ot; in that they are still visible to some transaction.)

We do only very coarse-grained analysis of whether a row is "dead"= ;.
In principle, if vacuum had access to all the live snapshots of
all sessions, it could realize that a row really is dead even though
it's later than the current global xmin horizon.  But discovering that=
would be quite difficult and therefore expensive.  Notably, sessions would have to expose far more of their snapshot state than they do
today, and there would have to be interlocks to allow other sessions
to inspect that state safely, and that'd probably put us into much the
same sort of too-many-lock-conflicts problem that the OP has already.

I don't think there's any free lunch here.  Maybe there's some
other compromise between amount-of-state-exposed versus
dead-row-discoverability, but finding a better way would take
a great deal of creative effort and testing.

            &nb= sp;           regards, to= m lane


Hi

Thanks for the clarifications , back to more divide and conquer work then.

Lars

--_000_AM7P189MB1028B388987982CEA5541DEF9D3D2AM7P189MB1028EURP_--