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

From: Lars Aksel Opsahl <[email-masked]>
Date: Mon, 9 Dec 2024 11:02:53 +0000

Hi
When processing multiple simple feature layers through PostGIS Topology to perform overlays and eliminate small areas/slivers, we face a complex workflow. To manage the workload, we split the input into smaller jobs using the Content Balanced Grid<https://gitlab.com/nibioopensource/content_balanced_grid>. These jobs are then executed in parallel using Postgres Execute Parallel<https://gitlab.com/nibioopensource/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 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.
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 threads, we encountered the expected “LWLock | SubtransControlLock” 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. Currently, 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 totally unrelated jobs, it would be much easier. The tables in use are unlogged and I already control the vacuum process from the caller. I can't use temp 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 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

---

From: "[email-masked]" <[email-masked]>
Date: Mon, 9 Dec 2024 08:18:00 -0500

You could always turn off vacuuming at the table level and then resume later

Sent from my iPhone

On Dec 9, 2024, at 6:03 AM, Lars Aksel Opsahl <[email-masked]> wrote:

Hi

When processing multiple simple feature layers through PostGIS Topology to perform overlays and eliminate small areas/slivers, we face a complex workflow. 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 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 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.

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 threads,
we encountered the expected “LWLock | SubtransControlLock” 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. Currently, 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 totally unrelated jobs, it would be much easier. The tables in use are unlogged and I already control the vacuum process from the caller. I can't use temp 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 by others also.

Thanks

Lars

---

From: Greg Sabino Mullane <[email-masked]>
Date: Mon, 9 Dec 2024 08:35:20 -0500

On Mon, Dec 9, 2024 at 6:03 AM Lars Aksel Opsahl <[email-masked]>
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?

> 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.

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 ?
>

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.

Cheers,
Greg

---

From: Lars Aksel Opsahl <[email-masked]>
Date: Mon, 9 Dec 2024 13:37:33 +0000

________________________________
From: [email-masked] <[email-masked]>
Sent: Monday, December 9, 2024 2:18 PM
To: Lars Aksel Opsahl <[email-masked]>
Cc: [email-masked] <[email-masked]>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows

Hi

I am running without auto vacuuming for those  tables already.

---

From: Lars Aksel Opsahl <[email-masked]>
Date: Mon, 9 Dec 2024 13:56:54 +0000

________________________________
From: Greg Sabino Mullane <[email-masked]>
Sent: Monday, December 9, 2024 2:35 PM
To: Lars Aksel Opsahl <[email-masked]>
Cc: [email-masked] <[email-masked]>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows

On Mon, Dec 9, 2024 at 6:03 AM Lars Aksel Opsahl <[email-masked]<mailto:[email-masked]>> 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 '

There are some more info here  https://gitlab.com/nibioopensource/resolve-overlap-and-gap/-/issues/97.

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

---

From: Rick Otten <[email-masked]>
Date: Mon, 9 Dec 2024 09:25:45 -0500

> this behavior for instance for unlogged tables ?
Are you saying your job is I/O bound (not memory or cpu).  And that you can
only improve I/O performance by committing more frequently because the
commit removes dead tuples which you have no other means to clear?   Is
your WAL already on your fastest disk?

All of your parallel jobs are operating on the same set of rows?  So
partitioning the table wouldn't help?

---

From: Christophe Pettus <[email-masked]>
Date: Mon, 9 Dec 2024 06:32:59 -0800

> On Dec 9, 2024, at 03:02, Lars Aksel Opsahl <[email-masked]> wrote:
> If there were a way to remove dead rows without requiring a commit from totally unrelated jobs, it would be much easier.

Without seeing into the future, PostgreSQL doesn't know if a particular open transaction is "totally unrelated" to any other open transaction.  Any open transaction can potentially see rows that have been deleted or updated since it began, and without knowing what operations are coming, it doesn't know if it is safe to remove them.

(Strictly speaking, the rows you are describing are not "dead," in that they are still visible to some transaction.)

---

From: Tom Lane <[email-masked]>
Date: Mon, 09 Dec 2024 11:07:35 -0500

> (Strictly speaking, the rows you are describing are not "dead," 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.

			regards, tom lane

---

From: Lars Aksel Opsahl <[email-masked]>
Date: Tue, 10 Dec 2024 05:19:24 +0000

From: Rick Otten <[email-masked]>
Sent: Monday, December 9, 2024 3:25 PM
To: Lars Aksel Opsahl <[email-masked]>
Cc: [email-masked] <[email-masked]>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows

Are you saying your job is I/O bound (not memory or cpu).  And that you can only improve I/O performance by committing more frequently because the commit removes dead tuples which you have no other means to clear?   Is your WAL already on your fastest disk?

All of your parallel jobs are operating on the same set of rows?  So partitioning the table wouldn't help?

The problem is not IO or CPU bound, or related to WAL files, but that "dead rows" are impacting the sql queries. About partitioning at this stage, the data are split in about 750 different topology structures. We have many workers working in parallel on these different structures but only one worker at the same on the same structure.

Thanks

Lars

---

From: Lars Aksel Opsahl <[email-masked]>
Date: Tue, 10 Dec 2024 05:32:44 +0000

________________________________
From: Tom Lane <[email-masked]>
Sent: Monday, December 9, 2024 5:07 PM
To: Christophe Pettus <[email-masked]>
Cc: Lars Aksel Opsahl <[email-masked]>; [email-masked] <[email-masked]>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows

                        regards, tom lane

Hi

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

Lars

---

From: Lars Aksel Opsahl <[email-masked]>
Date: Tue, 10 Dec 2024 08:55:00 +0000

regards, tom lane

Hi again.

On my bike to work this morning I was thinking more about this.

When the application run vacuum I find this  "7954046 are dead but not yet removable removable" so the job knows what rows that are involved.

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 ?

Just to test what happens and also how it affects performance.

The tables I work on are unlogged , vacuum command is controlled by the application. If this option is only applicable on unlogged tables which are unsafe anyway and from command line , maybe that can help to reduce complexity and side effects.

I am now testing on PostgreSQL "16.4 (Ubuntu 16.4-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit"

Thanks.

Lars

---

From: Greg Sabino Mullane <[email-masked]>
Date: Tue, 10 Dec 2024 08:03:28 -0500

On Tue, Dec 10, 2024 at 3:55 AM Lars Aksel Opsahl <[email-masked]>
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. :)

Cheers,
Greg

---

From: Lars Aksel Opsahl <[email-masked]>
Date: Tue, 10 Dec 2024 14:03:41 +0000

________________________________
Sent: Tuesday, December 10, 2024 2:03 PM
To: Lars Aksel Opsahl <[email-masked]>
Cc: Tom Lane <[email-masked]>; Christophe Pettus <[email-masked]>; [email-masked] <[email-masked]>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows

On Tue, Dec 10, 2024 at 3:55 AM Lars Aksel Opsahl <[email-masked]<mailto:[email-masked]>> 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

---

From: Greg Sabino Mullane <[email-masked]>
Date: Tue, 10 Dec 2024 10:31:37 -0500

Thanks for that link; seeing actual queries is a big help. One thing to try
is to get some index-only scans to run. Regular indexes need to consult the
heap (main table) for visibility information, and a bloated table can make
that consultation expensive.

For example, an index like this should work to trigger an index-only scan:

create index gregtest on node(geom) include(node_id) where
containing_face=0;

For those not following that link, the query is:

SELECT node_id, geom FROM node WHERE containing_face IN (0)
AND geom &&
'0102000020A21000000200000025DDA6B95DC62540F4E713991CE84D4017EE7636A3C625404E468D0D23E84D40'::geometry

Or if containing_face is not always 0, a more generic variant:

create index gregtest on node(geom, containing_face) include (node_id);

What is the nature of the updates that are causing that many dead rows in
the first place?

Cheers,
Greg

---

From: Hannu Krosing <[email-masked]>
Date: Tue, 10 Dec 2024 21:26:11 +0100

If there are unremovable rows it usually also means that index-only scan
degrades to index-scan-with-visibility-checks-in-tables.

I think the ask is to be able to remove the recently dead rows that are not
visible in any current snapshot and can never become visible to any future
snapshot,
Something that Tom described as currently not easily doable above.

Maybe we could figure out a way for long-running transactions to upload
their snapshot set to some meta-vacuum process which can use it to
determine which rows fall into that category. In this way the change would
affect only the long-running transactions and if we do it onl maybe once a
minute it should not be too heavy overhead even for these transactions.

On Tue, Dec 10, 2024 at 4:32 PM Greg Sabino Mullane <[email-masked]>
wrote:

> Thanks for that link; seeing actual queries is a big help. One thing to
> try is to get some index-only scans to run. Regular indexes need to consult
> the heap (main table) for visibility information, and a bloated table can
> make that consultation expensive.
>

---

From: Lars Aksel Opsahl <[email-masked]>
Date: Wed, 11 Dec 2024 07:32:52 +0000

________________________________
From: Hannu Krosing <[email-masked]>
To: Greg Sabino Mullane <[email-masked]>
Cc: Lars Aksel Opsahl <[email-masked]>; Tom Lane <[email-masked]>; Christophe Pettus <[email-masked]>; [email-masked] <[email-masked]>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows

If there are unremovable rows it usually also means that index-only scan degrades to index-scan-with-visibility-checks-in-tables.

I think the ask is to be able to remove the recently dead rows that are not visible in any current snapshot and can never become visible to any future snapshot,
Something that Tom described as currently not easily doable above.

Maybe we could figure out a way for long-running transactions to upload their snapshot set to some meta-vacuum process which can use it to determine which rows fall into that category. In this way the change would affect only the long-running transactions and if we do it onl maybe once a minute it should not be too heavy overhead even for these transactions.
Hi

This is tested but only works when the load is very low.

To fix the problem with xmin we use this https://gitlab.com/nibioopensource/postgres_execute_parallel to start the jobs,  but the control is returned to the caller at given intervals, and the caller also does a commit and gets a new snapshot and xmin is updated. If all jobs done are done the caller may start a new batch of jobs.

One of the 750 jobs takes for hours to remove (https://postgis.net/docs/ST_RemEdgeNewFace.html) almost 30.000 edges.  I do this in a loop takes around 500 edges in every loop. If the time used is to long I forced a commit in this loop in addition to the analyze I do. Adding the commit really helps on execution time but only when running a very few jobs in parallel. With this new commit I could see that dead rows were removed.

The issue with the new commit in the loop where I remove edges is that it becomes problem when many treads run parallel related to subtransactions per transaction
like discussed here https://postgrespro.com/list/thread-id/2478202 and many other threads. Almost like a catch 22, fixing the dead rows problem is causing a new bigger problem.

So to solve problems with subtransactions per transaction I can make new joblist for removing edges also, but that makes things more complicated.

Thanks

Lars.

---

From: Lars Aksel Opsahl <[email-masked]>
Date: Wed, 11 Dec 2024 13:29:23 +0000

Sent: Tuesday, December 10, 2024 4:31 PM

Thanks for that link; seeing actual queries is a big help. One thing to try is to get some index-only scans to run. Regular indexes need to consult the heap (main table) for visibility information, and a bloated table can make that consultation expensive.

create index gregtest on node(geom) include(node_id) where containing_face=0;

SELECT node_id, geom FROM node WHERE containing_face IN (0)
AND geom && '0102000020A21000000200000025DDA6B95DC62540F4E713991CE84D4017EE7636A3C625404E468D0D23E84D40'::geometry

What is the nature of the updates that are causing that many dead rows in the first place?

Cheers,
Greg

Hi

Thank's I did not know about the include option, I will have this mind when working on this.

I have done some simple testing with it now, but I have not seen any big changes yet, but issue in case 97 is not the same as in issue  67. What problems cases we end up with, will vary depending on the simple feature layers we run overlay between and for instance how many surfaces we need remove from the topology layer before we produce the final result.

In this case based on pg_stat_statements in seems like a lot off the time is used on updates when removing edges.

Why we get all this dead rows are related Postgis Topology database structure and input data. In some cases we get input data with verry many almost parallel lines which are close and that will cause a lot off edges be removed.

Lars