Re: PostgreSQL and a Catch-22 Issue related to dead rows - Mailing list pgsql-performance

From Lars Aksel Opsahl
Subject Re: PostgreSQL and a Catch-22 Issue related to dead rows
Date
Msg-id AM7P189MB10280FC5CF98DCA9F9593E349D3C2@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM
Whole thread Raw
In response to Re: PostgreSQL and a Catch-22 Issue related to dead rows  (Greg Sabino Mullane <htamfids@gmail.com>)
List pgsql-performance


From: Greg Sabino Mullane <htamfids@gmail.com>
Sent: Monday, December 9, 2024 2:35 PM
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows
 
On Mon, Dec 9, 2024 at 6:03 AM Lars Aksel Opsahl <Lars.Opsahl@nibio.no> wrote:
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 of which 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.
Can you please expand exactly what you mean by "removal of dead rows" here, and what the exact issue you are facing is?
Hi

In the logs I have this  'tuples: 207 removed, 7983843 remain, 7954046 are dead but not yet removable removable cutoff: 1189559933, which was 2662 XIDs old when operation ended '
When the oldest job commits we are able to remove this dead rows.

By introducing periodic COMMIT statements and VACUUM (FULL) operations
Vacfull is a pretty rough solution, and almost always not the correct tool for the job, IMHO.

The problems is that I have to remove those dead rows to get an OK performance.
It's still not clear exactly what the ask is here, but there is little chance we would design an alternative MVCC system just to accommodate this use case.

The problem related dead remove is a quite common issue/problem for Postgresql which is also very nicely described here https://www.cybertec-postgresql.com/en/reasons-why-vacuum-wont-remove-dead-rows/
by Laurenz also.

Thanks

Lars

pgsql-performance by date:

Previous
From: Lars Aksel Opsahl
Date:
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows
Next
From: Rick Otten
Date:
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows