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

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

Sent from my iPhone

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?

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

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.

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.

> On Dec 9, 2024, at 03:02, Lars Aksel Opsahl <Lars.Opsahl@nibio.no> 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
otheropen transaction.  Any open transaction can potentially see rows that have been deleted or updated since it began,
andwithout 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.)

Christophe Pettus <xof@thebuild.com> writes:
>> On Dec 9, 2024, at 03:02, Lars Aksel Opsahl <Lars.Opsahl@nibio.no> wrote:
>> If there were a way to remove dead rows without requiring a commit from totally unrelated jobs, it would be much
easier.

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

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 ?

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?

 

Sent: Monday, December 9, 2024 5:07 PM
To: Christophe Pettus <xof@thebuild.com>
Cc: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows

 

Christophe Pettus <xof@thebuild.com> writes:
>> On Dec 9, 2024, at 03:02, Lars Aksel Opsahl <Lars.Opsahl@nibio.no> wrote:
>> If there were a way to remove dead rows without requiring a commit from totally unrelated jobs, it would be much easier.

> (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: Tom Lane <tgl@sss.pgh.pa.us>

Sent: Monday, December 9, 2024 5:07 PM

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

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

Sent: Tuesday, December 10, 2024 2:03 PM
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; Christophe Pettus <xof@thebuild.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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@nibio.no> 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. :) 

---

From: Hannu Krosing <hannuk@google.com>

To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>; Tom Lane <tgl@sss.pgh.pa.us>; Christophe Pettus <xof@thebuild.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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.

Sent: Tuesday, December 10, 2024 4:31 PM
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; Christophe Pettus <xof@thebuild.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows

 

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