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

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 03:02,

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

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 add

Re: can a blocked transaction affect the performance of one that is blocking it?

On Mon, Dec 9, 2024 at 13:16 Eric Schwarzenbach wrote: > Hi, > > Could one transaction (one that should be relatively simple and short) > cause another complex, long running transaction (involving INSERTS, on a > table the first transaction may be reading from) to take many orders of > magnitude

can a blocked transaction affect the performance of one that is blocking it?

Hi, Could one transaction (one that should be relatively simple and short) cause another complex, long running transaction (involving INSERTS, on a table the first transaction may be reading from) to take many orders of magnitude longer than it would normally? (short of competing for system r

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

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

> 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

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

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 mailto:lars.op

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

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 th

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

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

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

PostgreSQL and a Catch-22 Issue related to dead rows

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