public inbox for [email protected]
help / color / mirror / Atom feedFrom: Lars Aksel Opsahl <[email protected]>
To: Greg Sabino Mullane <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Christophe Pettus <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows
Date: Tue, 10 Dec 2024 14:03:41 +0000
Message-ID: <AM7P189MB1028DE31357B6B278FDEC3589D3D2@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <CAKAnmmJLzTQAEzyBoLhG40WEN3siWFSBT68GSu2b_mF=WJL-JQ@mail.gmail.com>
References: <AM7P189MB10281DCA1FD196A5610484B29D3C2@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
<[email protected]>
<[email protected]>
<AM7P189MB102815FBBAC696A3FE173CEB9D3D2@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
<CAKAnmmJLzTQAEzyBoLhG40WEN3siWFSBT68GSu2b_mF=WJL-JQ@mail.gmail.com>
________________________________
Sent: Tuesday, December 10, 2024 2:03 PM
To: Lars Aksel Opsahl <[email protected]>
Cc: Tom Lane <[email protected]>; Christophe Pettus <[email protected]>; [email protected] <[email protected]>
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 protected]<mailto:[email protected]>> 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
view thread (17+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows
In-Reply-To: <AM7P189MB1028DE31357B6B278FDEC3589D3D2@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox