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

2024-12-11 Thread Lars Aksel Opsahl
Sent: Tuesday, December 10, 2024 4:31 PM To: Lars Aksel Opsahl Cc: Tom Lane ; Christophe Pettus ; 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

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

2024-12-10 Thread Lars Aksel Opsahl
From: Hannu Krosing To: Greg Sabino Mullane Cc: Lars Aksel Opsahl ; Tom Lane ; Christophe Pettus ; 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

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

2024-12-10 Thread Hannu Krosing
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, Somethi

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

2024-12-10 Thread Greg Sabino Mullane
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 wor

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

2024-12-10 Thread Lars Aksel Opsahl
Sent: Tuesday, December 10, 2024 2:03 PM To: Lars Aksel Opsahl Cc: Tom Lane ; Christophe Pettus ; 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

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

2024-12-10 Thread Greg Sabino Mullane
On Tue, Dec 10, 2024 at 3:55 AM Lars Aksel Opsahl 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

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

2024-12-10 Thread Lars Aksel Opsahl
From: Tom Lane 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 la

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

2024-12-09 Thread Lars Aksel Opsahl
From: Tom Lane Sent: Monday, December 9, 2024 5:07 PM To: Christophe Pettus Cc: Lars Aksel Opsahl ; pgsql-performance@lists.postgresql.org Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows Christophe Pettus writes: >> On Dec 9, 2024, at

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

2024-12-09 Thread Lars Aksel Opsahl
From: Rick Otten Sent: Monday, December 9, 2024 3:25 PM To: Lars Aksel Opsahl Cc: pgsql-performance@lists.postgresql.org Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows Yes there are very good reason for the way removal for dead rows work now, but is there any chance of

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

2024-12-09 Thread Tom Lane
Christophe Pettus writes: >> On Dec 9, 2024, at 03:02, Lars Aksel Opsahl 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 st

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

2024-12-09 Thread Christophe Pettus
> On Dec 9, 2024, at 03:02, Lars Aksel Opsahl 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

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

2024-12-09 Thread Rick Otten
> > > 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 imp

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

2024-12-09 Thread Lars Aksel Opsahl
From: Greg Sabino Mullane Sent: Monday, December 9, 2024 2:35 PM To: Lars Aksel Opsahl Cc: 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

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

2024-12-09 Thread Lars Aksel Opsahl
From: michael...@sqlexec.com Sent: Monday, December 9, 2024 2:18 PM To: Lars Aksel Opsahl Cc: pgsql-performance@lists.postgresql.org Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows You could always turn off vacuuming at the table level and

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

2024-12-09 Thread Greg Sabino Mullane
On Mon, Dec 9, 2024 at 6:03 AM Lars Aksel Opsahl 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. Th

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

2024-12-09 Thread michael...@sqlexec.com
You could always turn off vacuuming at the table level and then resume laterSent from my iPhoneOn Dec 9, 2024, at 6:03 AM, Lars Aksel Opsahl wrote: Hi When processing multiple simple feature layers through PostGIS Topology to perform overlays and eliminate small areas/slivers, we face a co