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 1tKbXe-009g2V-Pe for pgsql-performance@arkaria.postgresql.org; Mon, 09 Dec 2024 11:03:07 +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 1tKbXb-007M9K-Ug for pgsql-performance@arkaria.postgresql.org; Mon, 09 Dec 2024 11:03:05 +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.94.2) (envelope-from ) id 1tKbXb-007M98-Dj for pgsql-performance@lists.postgresql.org; Mon, 09 Dec 2024 11:03:04 +0000 Received: from mail-vi1eur03on2114.outbound.protection.outlook.com ([40.107.103.114] helo=EUR03-VI1-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tKbXY-001sGr-N3 for pgsql-performance@lists.postgresql.org; Mon, 09 Dec 2024 11:03:03 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector10001; d=microsoft.com; cv=none; b=IMd1HXlmgtJmVWGKTNIa6fzLSlsO4sNxfctvNclgJJnoJeOw9Yk6pTgLqLdxvPWPdmO+DlHr7u2yR4DAD4OKvtbGAfhw9eKr3NQ7No321HsDzz8S1Urnr8p+suRLqlgwIMg8JMoY4lKstpNaxqhbmiRYbqtcEJ413hotOaG0XXNav8LZ8kjkU74I/AnSVpBrLGHzOjmZwUy/85gb9xJ9tG5dO3leJY9HTsHffGki2dCzrACgruOiwPrPaviK6ukIP4g+Q0du13ujvppQ6NDklRoeHWsoVCNGS2BKGEH/HgE9JmH0+Jcs5HtiypLQenTH1Oriskg4ctWJpV5KVDtqOA== 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=sZPcwgt6emBSuf3BE+LokeX+nklVbMhEDAdwPnIbRZQ=; b=BJaGFTrG4AL5OW5g1fzr28h3f9VIKatCtEJyWXN3nqqMeQKElb/pzy2iZRpOocmqoXZv2S3nSStnqXsc0S3BViGr3qLkDTXaMeV34O4wDUOf3Tw3R4KYISgDeFgqQrsQ5hgRjEnYUokIWta9VDqKksW/FcSsPitScKykkxoxo1HGgc1yAtWqCK2VdQAsnYOE7/hfj0Iehk+nGjJ+vEStbLdzs5RyaJ12KBrFuunaCYDOoD+UxmB8OTW13QjayM1uf+r+worRo+l5tv09sm9N4dv8/x04p/jPy05rO8t2esJTZnv9gqq6vsgr/LdVGh02YE9IrRzmMv3TT9p4tZVfMQ== 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=sZPcwgt6emBSuf3BE+LokeX+nklVbMhEDAdwPnIbRZQ=; b=bQg1CZPR5m4kRDNBTK5mKxu1/7m/QXI5a8KDrgagpG+XSlfCQ7BSRTTv0Drqi4GH7d01GV7YEdbD0Vwdbg9YAb1n33VAlizhw8NmmjV3l8r3exGFvkNJ/hj2QcxDt4yOap9opqCdDTF/uSyDyf+jRBGDuMX+kpSzIypqhoBEtH8= Received: from AM7P189MB1028.EURP189.PROD.OUTLOOK.COM (2603:10a6:20b:174::18) by AS4P189MB1895.EURP189.PROD.OUTLOOK.COM (2603:10a6:20b:4b6::19) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.8207.23; Mon, 9 Dec 2024 11:02:54 +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; Mon, 9 Dec 2024 11:02:54 +0000 From: Lars Aksel Opsahl To: "pgsql-performance@lists.postgresql.org" Subject: 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+rgvhkWYwOHzJhBtew== Date: Mon, 9 Dec 2024 11:02:53 +0000 Message-ID: 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_|AS4P189MB1895:EE_ x-ms-office365-filtering-correlation-id: 5697b6a5-55bd-49a0-5e04-08dd184107d2 x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0;ARA:13230040|41320700013|376014|366016|1800799024|8096899003|38070700018; x-microsoft-antispam-message-info: =?Windows-1252?Q?GL3dzPhO689z2VhlQbK34hbwZkNsfKH+QvZgbigkPDdob/iFqHl/e93z?= =?Windows-1252?Q?IAb0Y0NKe6FvvZrCuAqnfmcCmCzhqE3SGCRfa44NxbASomuYZXMi4J01?= =?Windows-1252?Q?tmgizewdRJvdkXlTA51a95TRpo6s7+hASKq24iyQbS6E/qwk7peuBgox?= =?Windows-1252?Q?xWV29nP3etGx6AmvB7vRZu5MWNZz6/JVsNj6kvTAVEuRXTxkR+idkKdw?= =?Windows-1252?Q?sAPzC61mOFKmFaamKmLlnAO3cS5FqUosC4fS96B0j6dO3Pt/7fOp+BPM?= =?Windows-1252?Q?+6muQk8q7CIqS1z0prNiUl2qDOXlVVoQBT53um6aWOFT+7FKjGNhsng9?= =?Windows-1252?Q?2BdMwlmgHwm6x1ahSCNsGd+J+WpGwav+TgbTxXzWCBuRhgLo2Iq6lD8l?= =?Windows-1252?Q?GC6PbbhWYCFmfoyOpG++/LzrML6qbeThsQ/MKQnyuahktOxPv/mcLLNR?= =?Windows-1252?Q?vT5QOgSprz/eSx8+2GneuGhAbhzYZbZxt9I7M0q97d9cc7lk0yqEq9K2?= =?Windows-1252?Q?kuT9M35R2B9hqvrjbp+ra7iFSl7pf+/H6zoe6ps+rcyFyYclvWS7TFol?= =?Windows-1252?Q?zdLOjiJ+C5awFqWIuehLDNcMg1Kasci2VjgEMf2QyRUyoy4AKIfjeM3j?= =?Windows-1252?Q?1gHXa7562M/dFdWqtKJQOiXw8yll51KAVUHv72/A/VTA3qmFwBcurHH0?= =?Windows-1252?Q?1S4Nzqfq1qxlTuguR0ihMG/8I28fED5FVYX1JwSPeKbi4CW5PGut7V/M?= =?Windows-1252?Q?V41Mb1YdNzAnCEmetOjpIXKrOV8UyLRJmkmTYr9olW1QIX4shMxiVssb?= =?Windows-1252?Q?LBiW8b+K9aTYKjTcW4yMqorQaxzESKncTYz3oumpNirDmwtaemdhmCVu?= =?Windows-1252?Q?c4Xv/csP4c0fmmMhPX125kJcDBbuaZVSdwM8i1R4YlKwcLpufVeKy6ph?= =?Windows-1252?Q?f4vz/5ZPEtjDYOj9onGcCkId5VyKmY0+RRSDsTwuh0EnbEJqE6DNMGtL?= =?Windows-1252?Q?kjmY3OPdJwpdYmygZo0FxBnI0Qbdq0zeKD9bN0u359oTJaS4UE9aG485?= =?Windows-1252?Q?TOq8aDjkTxzYxhEa4IvARTQeVkHCgnUje4NRGi5sOLUYQBjrZB/4vhrZ?= =?Windows-1252?Q?rYq98njUBKC+h63hTj+HlDfJVpUacgN+l/NC6J2r7Qt/OI8ENU5IW86b?= =?Windows-1252?Q?LCiCBBqVNtyAuZlVOHP9qRBZjUbCQ8X22MUOcvTykHB9l05I4F6xDeWj?= =?Windows-1252?Q?9hqemBLZnVwmNjFqtEJB7C5HxxFTSxgSwj1uIh/m+IigIWwb5GB+1Cbz?= =?Windows-1252?Q?GwyfttZG4hTbyldFQnwZ34c3kygVezx/TcXTzpVYfU+EY8HzuTFCWDbG?= =?Windows-1252?Q?Nzr8CjYBRY0RuPw88SmqyHVWBYArREbpikS4GNXVxMzZbqj4mvJdN3Bf?= =?Windows-1252?Q?/asvJNzwY/MhEBvkym4++9ugqbQzFBN9F0ThPucDRUDs/5UcX+pCeg39?= =?Windows-1252?Q?ZwX9toZH194kJH2iV2GxBJyA98CkCQ=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)(376014)(366016)(1800799024)(8096899003)(38070700018);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?Windows-1252?Q?LD5UgL7YFojFPok28tiEHYDWKv8m8350Sjz8QGVvxBJJrxGBNvfQ8jDQ?= =?Windows-1252?Q?lHoKKSb7egp/kiZ5WX4rAMjgr28GMc1zwhaxYGAj0iIBWTh985TFFrV5?= =?Windows-1252?Q?RACUYX2JqxQ5kIcgtl8zZRblddxIpFP9oJ1tRXjtc5MEfLT/dE/Dlb+C?= =?Windows-1252?Q?cEnABp6fPt05bcaoy+Ea1/pQMmdxzA37SkYviAbAoQMmlNz4FCUzpHH+?= =?Windows-1252?Q?1Dh3Qf4fp4smtrPbnWuVu6/gxISCioVPfir9cBQ6XAsV6lUsr1pDdkzN?= =?Windows-1252?Q?pdySrcqbz177Ur1KfvfrBeunbE+8WvFgTdquLP/pQHWfnPiCi2u3qHo9?= =?Windows-1252?Q?AdAZlJSbyZg8nYuE5/jIrrF1t18MJydW/yqZ7maBwrgmZrgvk9LWiTl5?= =?Windows-1252?Q?MHfH4aChaTXyp5afX1sXualBpt1JtaEqueLTp4II8Zfqba2Pwo7+pTIT?= =?Windows-1252?Q?ji625ZRFcEd+HNcbOxQZxbxXVgB/ux2eNHa8l+Idj7/QC1KpUqKTbkEB?= =?Windows-1252?Q?rahIGE35EntPLClKG7Q6fSu6g9iRJqMKQS3ZlVQXy8LP6qC7V95JwDJm?= =?Windows-1252?Q?qsm2yrHwBU91qUAOWbInb88yZ4D10HmS6D07z/VXF86GRltgY08ElsAr?= =?Windows-1252?Q?D2wBM8+kGgb4y71tSkJLdfqpCicl5Ninm6CpYmaGMA6aRXA0LBdFBgut?= =?Windows-1252?Q?6JXUCzZSP9lKfC8mkB7KF1PrwB3x2F3LSGaxafbpD+yTVTkQykpm49bH?= =?Windows-1252?Q?TFXFwWanGz2U1M+cVkRrkXjh6MXAmusGdRo7VBdAmutg5JJxsEmUfbbc?= =?Windows-1252?Q?M9cvaqaCvR3SHDj1WyWGR9tbogoUezAQUgdz4FYNGwXQYKQmvyJHBTyq?= =?Windows-1252?Q?imk7G4FVlxzz+pk+JTgvIKFzSvXrrmHCVEFAhzTJmhB+zVx8zmC6hd/U?= =?Windows-1252?Q?/8+B8fP/xQZzMnm5Y5w/HWTUewy7UaP1bWkavw3itLcg7s7PQHSWdqZM?= =?Windows-1252?Q?HFg0+HCCHiV89gtRlr1H3nadmxMzGHt8vbW3B5FRRUVYB+qom8YPRlPn?= =?Windows-1252?Q?t8Ugv6fZwGv7OSdn0CO5F6p7yAeqpORABLIKWliFzrUTBNMwKEcRRQiB?= =?Windows-1252?Q?ox1FzLF9GSC3QD3eQZDYksuGwX89GH8uBNW5fgEvkl53tUjlvdIQFjRD?= =?Windows-1252?Q?pawuI98EJlRMi9KwG94/DCyeiVvU2LZKEaEx1TUAHJXbThUqzKJFpo5L?= =?Windows-1252?Q?rlZV9FkJQJiIIk0K38mHE8sU0TNeJcdbmvHoHke2fmGfuJ53zeDf/q3K?= =?Windows-1252?Q?7fKzTkL15nfpNkcL54DZ1zH4j3gVz6NZjr+5NZvs61wdBSfiwPj7MEJ8?= =?Windows-1252?Q?OeWkRoF0GmFL/20tf1bVU0Orxcbs4aX9fN3NOOS42DKv00p4d/+uscA6?= =?Windows-1252?Q?KyZeuvNJwn7gywYIB5SRqnrlFC2Op4ERgZJBasg6B3xFbxPHo51w4Wt3?= =?Windows-1252?Q?3NqkrzmKH3IIkqap3JTid35nRlNWSmORuNaB2iW7kenEt5DfDjwGfxuD?= =?Windows-1252?Q?Dm0sObVVupIyJs81UsvsSEeZDuJcnQ0O0Sv9Aenq4QfXcL+0fbL2bgZu?= =?Windows-1252?Q?IgtCH7eeRbCgzTkuDPCL4XmUfM2+kG6cpANov/5dENIfLozfzWRmp2ra?= =?Windows-1252?Q?MlHSX+tx0Do=3D?= Content-Type: multipart/alternative; boundary="_000_AM7P189MB10281DCA1FD196A5610484B29D3C2AM7P189MB1028EURP_" 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: 5697b6a5-55bd-49a0-5e04-08dd184107d2 X-MS-Exchange-CrossTenant-originalarrivaltime: 09 Dec 2024 11:02:53.9419 (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: xMAX1JKx4cBkP2krHgZ0f14s9O4QPC6YA4iAauSm8QMaWjB0P1LMVNpmG7A7FUyWUccVit01lFPQohya621SpA== X-MS-Exchange-Transport-CrossTenantHeadersStamped: AS4P189MB1895 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_AM7P189MB10281DCA1FD196A5610484B29D3C2AM7P189MB1028EURP_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Hi When processing multiple simple feature layers through PostGIS Topology to = perform overlays and eliminate small areas/slivers, we face a complex workf= low. To manage the workload, we split the input into smaller jobs using the= Content Balanced Grid. These jobs are then executed in parallel using Postgres Execute Para= llel. In one case, we processed a total of 750 cells, with an overall runtime of = 40 hours. However, one specific cell took over 12 hours to complete, most o= f which was spent on removing small areas by deleting edges in PostGIS Topo= logy. The root cause of this delay is related to removal of dead rows. By introducing periodic COMMIT statements and VACUUM (FULL) operations, we = managed to reduce the processing time for that single cell to approximately= 3 hours. However, when we scaled this updated code to use 100 parallel thr= eads, we encountered the expected =93LWLock | SubtransControlLock=94 bottle= neck, leading to an overall increase in runtime for all the cells. One issue is the inability to accurately estimate the size of a job before = execution, making it difficult to optimize job splitting in advance. Curren= tly, the only viable solution seems to be implementing a timer for each job= . The job would terminate after a predefined time, committing its progress = and returning control to the caller. The caller must then trigger new jobs = to complete the remaining tasks until done. If there were a way to remove dead rows without requiring a commit from tot= ally unrelated jobs, it would be much easier. The tables in use are unlogge= d and I already control the vacuum process from the caller. I can't use tem= p tables. Such a mechanism would also prevent unrelated long running jobs t= o cause problems for my jobs. To avoid that problem now, I have to move my = jobs to a server not used by others also. 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 ? Thanks Lars --_000_AM7P189MB10281DCA1FD196A5610484B29D3C2AM7P189MB1028EURP_ Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable
Hi
When processing multiple simple feature layers through PostGIS Topology to = perform overlays and eliminate small areas/slivers, we face a complex workf= low. To manage the workload, we split the input into smaller jobs using the Content Balanced Grid. These jobs are then executed in parall= el using Postgres Execute Parallel.
In one case, we processed a total of 750 cells, with an overall runtime of = 40 hours. However, one specific cell took over 12 hours to complete, most o= f which was spent on removing small areas by deleting edges in PostGIS Topo= logy. The root cause of this delay is related to removal of dead rows.
By introducing periodic COMMIT statements and VACUU= M (FULL) operations, we managed to reduce the processing time f= or that single cell to approximately 3 hours. However, when we scaled this = updated code to use 100 parallel threads, we encountered the expected =93LWLock | SubtransControlLock=94 bottleneck,= leading to an overall increase in runtime for all the cells.
One issue is the inability to accurately estimate the size of a job before = execution, making it difficult to optimize job splitting in advance. Curren= tly, the only viable solution seems to be implementing a timer for each job= . The job would terminate after a predefined time, committing its progress and returning control to the ca= ller. The caller must then trigger new jobs to complete the remaining tasks= until done.
If there were a way to remove dead rows without requiring a commit from tot= ally unrelated jobs, it would be much easier. The tables in use are unlogge= d and I already control the vacuum process from the caller. I can't use tem= p tables. Such a mechanism would also prevent unrelated long running jobs to cause problems for my jobs. To= avoid that problem now, I have to move my jobs to a server not used b= y others also.

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 ?

Thanks

Lars


--_000_AM7P189MB10281DCA1FD196A5610484B29D3C2AM7P189MB1028EURP_--