Re: PostgreSQL and a Catch-22 Issue related to dead rows

Lars Aksel Opsahl <Lars.Opsahl@xxxxxxxx> · Tue, 10 Dec 2024 14:03:41 +0000

Sent: Tuesday, December 10, 2024 2:03 PM

To: Lars Aksel Opsahl <Lars.Opsahl@xxxxxxxx>

Cc: Tom Lane <tgl@xxxxxxxxxxxxx>; Christophe Pettus <xof@xxxxxxxxxxxx>; pgsql-performance@xxxxxxxxxxxxxxxxxxxx <pgsql-performance@xxxxxxxxxxxxxxxxxxxx>

Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows

On Tue, Dec 10, 2024 at 3:55 AM Lars Aksel Opsahl <Lars.Opsahl@xxxxxxxx> wrote:

Is it difficult to add parameter like force-dead-rows-removal that we send to the vacuum job that will remove this rows like this
?

I'm still not sure what the ask here is - complete literal removal of the dead rows? That's not how PG works. I'm wondering if we are not in an XY problem. Your queries are slow,
 and you think it's because of autovacuum's output re dead rows. But let's take a step back and look at the actual queries being run that are slowing down. Perhaps there are other solutions: less indexing, more freezing, smarter updates, different partitioning,
 tweaking fillfactor, etc. etc. There are lots of things we can try that will be orders of magnitude simpler than trying to redesign MVCC/vacuuming. :) 

Hi

Yes we can solve this with more divide and conquer working but it's complicating thing a lot. I basically do partitioning now by splitting Postgis Topology up many hundreds and sometime many thousands off different topologies, but there is a limit to how much
 I can split up and later merge because this also has a cost.

So the main issue seems to be related to dead rows.  We have samples of queries like this 'SELECT node_id,geom FROM node WHERE containing_face = 0;' going from 2008.947 ms to 0.072 ms, when we did a commit  on an unrelated job so xmin did not block removal
 off dead rows. Here is also some more info 
https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/issues/67 .Doing anlayze just before the query does not help, the only thing that seems help is get read off rows marked as "DETAIL: 195929 dead row versions cannot be removed yet, oldest
 xmin: 3475136501 "

Seen from the outside I am not asking for redesign 🙂just a parameter to test forced removal of dead rows when working on unnlogged tables and running vacuum from command line, but may seem to be more complicated than I was hopeing.

Thanks.

Lars