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 1tKdee-009tX7-7o for pgsql-performance@arkaria.postgresql.org; Mon, 09 Dec 2024 13:18:28 +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 1tKdea-008OqE-Cf for pgsql-performance@arkaria.postgresql.org; Mon, 09 Dec 2024 13:18:25 +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 1tKdeZ-008Oq1-RE for pgsql-performance@lists.postgresql.org; Mon, 09 Dec 2024 13:18:25 +0000 Received: from mout.perfora.net ([74.208.4.197]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tKdeY-001u5m-1a for pgsql-performance@lists.postgresql.org; Mon, 09 Dec 2024 13:18:23 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=sqlexec.com; s=s1-ionos; t=1733750293; x=1734355093; i=michaeldba@sqlexec.com; bh=8V747NDbUb3lpcqu4lEHCWZLNrmwJ1rh5TOGZ+SaqFY=; h=X-UI-Sender-Class:Content-Type:Content-Transfer-Encoding:From: Mime-Version:Subject:Date:Message-Id:References:Cc:In-Reply-To:To: cc:content-transfer-encoding:content-type:date:from:message-id: mime-version:reply-to:subject:to; b=zNwAv6s+nONjvGLKhgR+Y6S7S1MDwnbE2JNIRF/CCF3d2eyljx4DWbrx5W/LoMJJ aT6rRt8gC74M7z1URhmNb+p8S7oaT/b2q6bkUDBFUaasmq1q/fUAqnRX2sSy/LiEV h0gzVI0CIx32WBToNJ2slQTUT3svziVR0UY+C3jZQ0k4vSzV79TI0A0f1OrL88fSj xfjm3xif6Pj1SEgDNoA9gmkJAuCqCWUb2jtzygUcihC8GH6j+HUQ1L3zFfyFC+JM+ YaND+2EFA+Bp9qYpTnTAq8fsHBs/VudG+xNgP+2IQGUXPNlpcUGspfcnNQhhfAXXa glYau8kM8bKE0WvrXQ== X-UI-Sender-Class: 55c96926-9e95-11ee-ae09-1f7a4046a0f6 Received: from smtpclient.apple ([73.171.48.12]) by mrelay.perfora.net (mreueus004 [74.208.5.2]) with ESMTPSA (Nemesis) id 1Mj8BN-1toULp2mrF-00anYX; Mon, 09 Dec 2024 14:18:12 +0100 Content-Type: multipart/alternative; boundary=Apple-Mail-FC87A795-A703-4D06-BB70-C770A47AF233 Content-Transfer-Encoding: 7bit From: "MichaelDBA@sqlexec.com" Mime-Version: 1.0 (1.0) Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows Date: Mon, 9 Dec 2024 08:18:00 -0500 Message-Id: References: Cc: pgsql-performance@lists.postgresql.org In-Reply-To: To: Lars Aksel Opsahl X-Mailer: iPhone Mail (21G93) X-Provags-ID: V03:K1:xuz7fsUYgoVf5SYRKyEAm7LoVv+rZL8ACy/lT7KlwjL4gD8Ag38 2SHf8CkcGyFfjCKB7b1Pjtu0EkHLUM0tW3zPoUNT8EelhwwioESJn4VFDkNFjIrLWPPzgdM E23jvut1cuBspuOA4Eyq131z80VYLnQp3qeX3Uu3+rpENGYb8OjirJAur4tTJmxxCxhtaqz OGh/tJTZO1oGbsq7bhzTw== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:pmbiq1OcRgo=;i59icu1JIHMxysnBzuBWJcy/PS9 DPYAN2NJ47na6bGNDghtAcVgb5oztzz1j/oqhmseHUc4VnyqHogWnRbCZSMUGw9z9wBjZd4np Kgi0xOtL3/nrzeV7w9u67Dam9IPLCnnPsh214SRNKJgVLl6e6DA1VH2AgU9w4Ea8cFWDtL8IP Xn8afwB1+OnpiJY8AakA6FEt4gBCqNWJbu2CIYV6Q1aAOQF7Nb9eirk3Ve/stlbQXkmVfvJ6H GU3E1wD86FVqOhHQTPSPsldJc0LHRyv8o8MRlO9iOaLPHKeFpu6DuzGyeJcEiCp+pqgU+WJSt J7fMO/TKSVhUYGYacmeCcMRk3aI7ut/TIHOQ10QvDLK73bpBh8Tf7/fJ6I2mqbRqiHaxQM+3t ZCwFDHSVN4U/7ObJiidnH01tFiGCyIgJr86vvAPnnD5oPB4P+nPJrduOAMkq1TWbgMhLTglF1 gmcX6D9Up7TxcRH+plmGXAMuH6g6+dxku1HWpNk+54VL9EGTtuErqA4dy8Ra9eKybevS1LVoW NE8+QvUbCgX6xdexiOVR9jZR+DHFEpvE8Tfyzgn6BFHIVMZgs8e/hpEzcvQUrT2hf2kHEqQJ0 0CnFgm9LnROnIqb1nwVjqjHa+sXRH8Xa5LIfMG23xauTwDaNn2EErrehCyxmV5W2s7bfZvdKP qJZ5ap+MUXnmeMz9pUmnAHBJqT7tvymzEO8utW7/iL75fT/86ATYrh61aMdKmXfYxRnYA87i0 zc4WPXCp1C0gBL1EKJw53ZN32JqUHOfIf46jHZ7qFrwAjih1pwnxVnNq2qOPU30zSMNMsgqpP Ox3oGr9JZrJ5E/FsUFqWyDCq4pCxcpPgNTNkGKzFNbXQyfIrQkkoyFuN06zqHJbFsZAR/CbK8 yXuI8Bj7cP4CFGnnI8/pZvc28Qfsmin+/Tl0QrjpiD31mKhJnA9vTFuJoWqv5KpFY9+l3ZbTA Rh2ig1BKJ3x8wp7SVgMXBLEyoORQxvjtfAzLaSEh1q58aIY1sb6mZr/3N2nlrIiJD6v5mx9Mr k8PUj9zw8zLoZdDJgn0iYT6LHONyW2IQw4zjnXf List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail-FC87A795-A703-4D06-BB70-C770A47AF233 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable You could always turn off vacuuming at the t= able level and then resume later
<= div dir=3D"ltr">Sent from my iPhone

On Dec 9, 2024, at 6:03=E2=80=AFAM, Lars Aksel Opsahl <Lars.O= psahl@nibio.no> wrote:

=EF=BB=BF
Hi
When processing multiple simple feature layers through PostGIS Topology to p= erform overlays and eliminate small areas/slivers, we face a complex workflo= w. To manage the workload, we split the input into smaller jobs using the Content Balanced Grid. These jobs are then executed in paralle= l using Postgres Execute Parallel.
In one case, we processed a total of 750 cells, with an overall runtime of 4= 0 hours. However, one specific cell took over 12 hours to complete, most of w= hich was spent on removing small areas by deleting edges in PostGIS Topology= . 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 upd= ated code to use 100 parallel threads, we encountered the expected =E2=80=9CLWLock | SubtransControlLock=E2=80=9D b= ottleneck, 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 e= xecution, making it difficult to optimize job splitting in advance. Currentl= y, the only viable solution seems to be implementing a timer for each job. T= he job would terminate after a predefined time, committing its progress and returning control to the cal= ler. The caller must then trigger new jobs to complete the remaining tasks u= ntil done.
If there were a way to remove dead rows without requiring a commit from tota= lly unrelated jobs, it would be much easier. The tables in use are unlogged a= nd I already control the vacuum process from the caller. I can't use temp ta= bles. Such a mechanism would also prevent unrelated long running jobs to cause problems for my jobs. To a= void that problem now, I have to move my jobs to a server not used by o= thers also.

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

Thanks

Lars


= --Apple-Mail-FC87A795-A703-4D06-BB70-C770A47AF233--