MIME-Version: 1.0
In-Reply-To: <CAJCZko+BFu4c4moqA5kMoJx3776xdYfEsxJQVNu_YYgrBmsCyQ@mail.gmail.com>
References: <CAJCZko+BFu4c4moqA5kMoJx3776xdYfEsxJQVNu_YYgrBmsCyQ@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Tue, 10 Feb 2026 08:05:06 -0800
Message-ID: <CAKFQuwYppwZRZQ128T+z638iMNGYcuLGHw42DD_AKSTnZu9QVQ@mail.gmail.com>
Subject: Re: Fillfactor effectiveness on existing table
To: Durgamahesh Manne <maheshpostgres9@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000005879af064a7a6bfa"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwYppwZRZQ128T%2Bz638iMNGYcuLGHw42DD_AKSTnZu9QVQ%40mail.gmail.com>
Precedence: bulk

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

On Tuesday, February 10, 2026, Durgamahesh Manne <maheshpostgres9@gmail.com=
>
wrote:

> Hi
>
> 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 ?
>
>
While free space on the page is necessary for HOT, it is not sufficient.

If you want to prove fillfactor isn=E2=80=99t buggy I=E2=80=99d suggest con=
triving a test
case instead inspecting complex real data.  A table with a single bigint
and say 50 fillfactor should be easily visible when inspecting the free
space of a page in the heap (not sure of the exact query for this though).
There is a page-inspect contrib extension that provides low-level details.

David J.

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

On Tuesday, February 10, 2026, Durgamahesh Manne &lt;<a href=3D"mailto:mahe=
shpostgres9@gmail.com">maheshpostgres9@gmail.com</a>&gt; wrote:<br><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc s=
olid;padding-left:1ex"><div dir=3D"auto">Hi=C2=A0<div dir=3D"auto"><br></di=
v><div dir=3D"auto">I added fillfactor with less than 100 to existing table=
 then ran vacuum full to take effect=C2=A0</div><div dir=3D"auto"><br></div=
><div dir=3D"auto">How to ensure the applied fillfactor is working successf=
ully=C2=A0</div><div dir=3D"auto"><br></div><div dir=3D"auto">A ratio of ho=
t updates in catalog table should higher than value of n_dead_tup or n_tup_=
upd? Or what ?</div><div dir=3D"auto"><br></div></div></blockquote><div><br=
></div><div>While free space on the page is necessary for HOT, it is not su=
fficient.</div><div><br></div><div>If you want to prove fillfactor isn=E2=
=80=99t buggy I=E2=80=99d suggest contriving a test case instead inspecting=
 complex real data.=C2=A0 A table with a single bigint and say 50 fillfacto=
r should be easily visible when inspecting the free space of a page in the =
heap (not sure of the exact query for this though).=C2=A0 There is a page-i=
nspect contrib extension that provides low-level details.</div><div><br></d=
iv><div>David J.</div><div><br></div>

--0000000000005879af064a7a6bfa--