MIME-Version: 1.0
References: <CAJCZko+BFu4c4moqA5kMoJx3776xdYfEsxJQVNu_YYgrBmsCyQ@mail.gmail.com>
 <8039d3e65e099b6046a48209fd1c194b1493fd8d.camel@cybertec.at>
In-Reply-To: <8039d3e65e099b6046a48209fd1c194b1493fd8d.camel@cybertec.at>
From: Durgamahesh Manne <maheshpostgres9@gmail.com>
Date: Wed, 11 Feb 2026 07:58:41 +0530
Message-ID: <CAJCZko+HNA++sW-a0cZWmPn7gPNDy2HKGXMSshMb5TFPzC5Qjw@mail.gmail.com>
Subject: Re: Fillfactor effectiveness on existing table
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000000aee75064a83221a"
Archived-At: <https://www.postgresql.org/message-id/CAJCZko%2BHNA%2B%2BsW-a0cZWmPn7gPNDy2HKGXMSshMb5TFPzC5Qjw%40mail.gmail.com>
Precedence: bulk

--0000000000000aee75064a83221a
Content-Type: text/plain; charset="UTF-8"

On Wed, 11 Feb, 2026, 00:01 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:

> On Tue, 2026-02-10 at 21:25 +0530, Durgamahesh Manne wrote:
> > I added fillfactor with less than 100 to existing table then ran vacuum
> full to take effect
> >
> > How to ensure the applied fillfactor is working successfully
> >
> > A ratio of hot updates in catalog table should higher than value of
> n_dead_tup or n_tup_upd? Or what ?
>
> Run
>
>   SELECT n_tup_upd, n_tup_hot_upd
>   FROM pg_stat_all_tables
>   WHERE relname = 'your table';
>
> Then wait for an hour and run the query again.
>
> See how much "n_tup_upd" and "n_tup_hot_upd" increased in that hour.
> If the increase is about the same for both, almost all of your UPDATEs
> are HOT.
>
> Yours,
> Laurenz Albe
>

Hi
@Laurenz Albe <laurenz.albe@cybertec.at>
offer=> SELECT relname AS table_name, n_tup_upd AS updates, n_tup_hot_upd
AS hot_updates FROM pg_stat_all_tables WHERE relname = 'market';
table_name | updates | hot_updates
market | 5997455764 | 3752865557

Regards
Durga Mahesh

>

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

<div dir=3D"auto"><div><br><br><div class=3D"gmail_quote gmail_quote_contai=
ner"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, 11 Feb, 2026, 00:01 Laur=
enz Albe, &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@cybe=
rtec.at</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"=
margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On Tue, 2026=
-02-10 at 21:25 +0530, Durgamahesh Manne wrote:<br>
&gt; I added fillfactor with less than 100 to existing table then ran vacuu=
m full to take effect=C2=A0<br>
&gt; <br>
&gt; How to ensure the applied fillfactor is working successfully=C2=A0<br>
&gt; <br>
&gt; A ratio of hot updates in catalog table should higher than value of n_=
dead_tup or n_tup_upd? Or what ?<br>
<br>
Run<br>
<br>
=C2=A0 SELECT n_tup_upd, n_tup_hot_upd<br>
=C2=A0 FROM pg_stat_all_tables<br>
=C2=A0 WHERE relname =3D &#39;your table&#39;;<br>
<br>
Then wait for an hour and run the query again.<br>
<br>
See how much &quot;n_tup_upd&quot; and &quot;n_tup_hot_upd&quot; increased =
in that hour.<br>
If the increase is about the same for both, almost all of your UPDATEs<br>
are HOT.<br>
<br>
Yours,<br>
Laurenz Albe<br></blockquote></div></div><div dir=3D"auto"><br></div><div d=
ir=3D"auto">Hi=C2=A0</div><div dir=3D"auto"><span class=3D"gmail_chip gmail=
_plusreply" dir=3D"auto"><a href=3D"mailto:laurenz.albe@cybertec.at" style=
=3D"color:#15c;text-decoration:underline">@Laurenz Albe</a></span><span>=C2=
=A0</span></div><div dir=3D"auto">offer=3D&gt; SELECT relname AS table_name=
, n_tup_upd AS updates, n_tup_hot_upd AS hot_updates FROM pg_stat_all_table=
s WHERE relname =3D &#39;market&#39;;=C2=A0</div><div dir=3D"auto">table_na=
me | updates | hot_updates=C2=A0</div><div dir=3D"auto">market | 5997455764=
 | 3752865557=C2=A0</div><div dir=3D"auto"><br></div><div dir=3D"auto">Rega=
rds</div><div dir=3D"auto">Durga Mahesh</div><div dir=3D"auto"><div class=
=3D"gmail_quote gmail_quote_container"><blockquote class=3D"gmail_quote" st=
yle=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
</blockquote></div></div></div>

--0000000000000aee75064a83221a--