MIME-Version: 1.0
References: <CAJCZkoLkMLsz1raz+XLJ09PUdgWv7mw37+zaFw9jNMhFUC+SVg@mail.gmail.com>
 <65a54f809d0339d9d6021c35f7fa14fc20a7f2e6.camel@cybertec.at>
 <CANzqJaB8aRCuEYYDU_qK0WUVVdWdoCte=wDt5387uj=Ncw5_Pg@mail.gmail.com> <84b6da7feb3de406d20a4a0e80954520a6db6b6c.camel@cybertec.at>
In-Reply-To: <84b6da7feb3de406d20a4a0e80954520a6db6b6c.camel@cybertec.at>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Sat, 7 Jun 2025 14:42:38 -0400
Message-ID: <CANzqJaDxcBCXEw4M2yOSWp4MR6W3mRKade3sj5wHEtb1j+0bQw@mail.gmail.com>
Subject: Re: Regarding fillfactor use case for only delete ops
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Durgamahesh Manne <maheshpostgres9@gmail.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000a767040636ffb63c"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaDxcBCXEw4M2yOSWp4MR6W3mRKade3sj5wHEtb1j%2B0bQw%40mail.gmail.com>
Precedence: bulk

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

On Sat, Jun 7, 2025 at 7:53=E2=80=AFAM Laurenz Albe <laurenz.albe@cybertec.=
at>
wrote:

> On Fri, 2025-06-06 at 09:59 -0400, Ron Johnson wrote:
> > On Fri, Jun 6, 2025 at 8:57=E2=80=AFAM Laurenz Albe <laurenz.albe@cyber=
tec.at>
> wrote:
> > > On Fri, 2025-06-06 at 14:10 +0530, Durgamahesh Manne wrote:
> > > > Can we generate a fill factor for tables that have delete ops ?
> > > >
> > > > Does the fill factor really work and help to minimize the bloat for
> tables that have delete ops?
> > > >
> > > > I have parent table with weekly partitions So for every week 50 to
> 60 gb of bloat generates and autovacuum params already in place for child
> tables
> > >
> > > Nothing can ever avoid bloat caused by DELETE, except partitioning in=
 a
> > > way that you can drop a partition rather than running DELETE.
> >
> > Isn't the fill factor aimed at reducing bloat during updates of HOT
> tables?
>
> Yes, but not during DELETEs.
>

I still don't understand why OP is deleting from a date-partitioned table,
instead of dropping the oldest partitions.


> HOT updates also don't directly avoid bloat on tables; only on indexes.
> They reduce the bloat on tables inderectly, because the dead tuples can
> be cleaned up with less effort.
>

Thanks for the clarification.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Sat, Jun 7, 2025 at 7:53=E2=80=AFAM La=
urenz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cyb=
ertec.at</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_containe=
r"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bord=
er-left:1px solid rgb(204,204,204);padding-left:1ex">On Fri, 2025-06-06 at =
09:59 -0400, Ron Johnson wrote:<br>
&gt; On Fri, Jun 6, 2025 at 8:57=E2=80=AFAM Laurenz Albe &lt;<a href=3D"mai=
lto:laurenz.albe@cybertec.at" target=3D"_blank">laurenz.albe@cybertec.at</a=
>&gt; wrote:<br>
&gt; &gt; On Fri, 2025-06-06 at 14:10 +0530, Durgamahesh Manne wrote:<br>
&gt; &gt; &gt; Can we generate a fill factor for tables that have delete op=
s ?<br>
&gt; &gt; &gt; <br>
&gt; &gt; &gt; Does the fill factor really work and help to minimize the bl=
oat for tables that have delete ops?<br>
&gt; &gt; &gt; <br>
&gt; &gt; &gt; I have parent table with weekly partitions So for every week=
 50 to 60 gb of bloat generates and autovacuum params already in place for =
child tables=C2=A0<br>
&gt; &gt; <br>
&gt; &gt; Nothing can ever avoid bloat caused by DELETE, except partitionin=
g in a<br>
&gt; &gt; way that you can drop a partition rather than running DELETE.<br>
&gt; <br>
&gt; Isn&#39;t the fill factor aimed at reducing bloat during updates of HO=
T tables?<br>
<br>
Yes, but not during DELETEs.<br></blockquote><div><br></div><div>I still do=
n&#39;t understand why OP is deleting from a date-partitioned table, instea=
d of dropping the oldest partitions.</div><div>=C2=A0</div><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid r=
gb(204,204,204);padding-left:1ex">HOT updates also don&#39;t directly avoid=
 bloat on tables; only on indexes.<br>
They reduce the bloat on tables inderectly, because the dead tuples can<br>
be cleaned up with less effort.<br></blockquote><div><br></div><div>Thanks =
for the clarification.</div></div><div><br></div><span class=3D"gmail_signa=
ture_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div =
dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil=
 me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div=
></div></div></div></div>

--000000000000a767040636ffb63c--