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
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
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
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
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
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
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
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
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
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
> 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
>
>
> 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
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
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
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
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
16 matches
Mail list logo