MIME-Version: 1.0
References: 
 <AM7P189MB10281DCA1FD196A5610484B29D3C2@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
 <5EE4CC0C-E624-4E82-A2E8-B2EBA44D3521@thebuild.com>
 <3908773.1733760455@sss.pgh.pa.us>
 <AM7P189MB102815FBBAC696A3FE173CEB9D3D2@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
In-Reply-To: 
 <AM7P189MB102815FBBAC696A3FE173CEB9D3D2@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Tue, 10 Dec 2024 08:03:28 -0500
Message-ID: 
 <CAKAnmmJLzTQAEzyBoLhG40WEN3siWFSBT68GSu2b_mF=WJL-JQ@mail.gmail.com>
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows
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>
Content-Type: multipart/alternative; boundary="00000000000089d33e0628ea1dfa"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKAnmmJLzTQAEzyBoLhG40WEN3siWFSBT68GSu2b_mF%3DWJL-JQ%40mail.gmail.com>
Precedence: bulk

--00000000000089d33e0628ea1dfa
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Dec 10, 2024 at 3:55=E2=80=AFAM Lars Aksel Opsahl <Lars.Opsahl@nibi=
o.no>
wrote:

> Is it difficult to add parameter like force-dead-rows-removal that we sen=
d
> 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. :)

Cheers,
Greg

--00000000000089d33e0628ea1dfa
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Dec 10, 2024 at 3:55=E2=80=AFAM L=
ars Aksel Opsahl &lt;<a href=3D"mailto:Lars.Opsahl@nibio.no">Lars.Opsahl@ni=
bio.no</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"=
><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border=
-left:1px solid rgb(204,204,204);padding-left:1ex"><div class=3D"msg4085429=
715062828888">


<div dir=3D"ltr">
<blockquote style=3D"margin-left:0.8ex;padding-left:1ex;border-left:3px sol=
id rgb(200,200,200)">
<div style=3D"font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Cali=
bri,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)"><span style=3D"co=
lor:rgb(34,34,34);font-family:Aptos;font-size:11pt">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
</span><span style=3D"font-family:Aptos;font-size:11pt"><code>?</code></spa=
n></div></blockquote></div></div></blockquote><div>I&#39;m still not sure w=
hat the ask here is - complete literal removal of the dead rows? That&#39;s=
 not how PG works. I&#39;m wondering if we are not in an XY problem. Your q=
ueries are slow, and you think it&#39;s because of autovacuum&#39;s=C2=A0ou=
tput re dead rows. But let&#39;s take a step back and look at the actual qu=
eries being run that are slowing down. Perhaps there are other solutions: l=
ess indexing, more freezing, smarter updates, different partitioning, tweak=
ing fillfactor, etc. etc. There are lots of things we can try that will be =
orders of magnitude simpler than trying to redesign MVCC/vacuuming. :)=C2=
=A0</div><div><br></div><div>Cheers,</div><div>Greg</div><div><br></div></d=
iv></div>

--00000000000089d33e0628ea1dfa--