MIME-Version: 1.0
References: <CAJCZko+BFu4c4moqA5kMoJx3776xdYfEsxJQVNu_YYgrBmsCyQ@mail.gmail.com>
 <CAKFQuwYppwZRZQ128T+z638iMNGYcuLGHw42DD_AKSTnZu9QVQ@mail.gmail.com>
In-Reply-To: <CAKFQuwYppwZRZQ128T+z638iMNGYcuLGHw42DD_AKSTnZu9QVQ@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 10 Feb 2026 12:28:03 -0500
Message-ID: <CANzqJaBcssS-inkrW=cxqxd5_1-Re-0xY6totprgypSHC-mkmg@mail.gmail.com>
Subject: Re: Fillfactor effectiveness on existing table
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000008c0ccc064a7b9461"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaBcssS-inkrW%3Dcxqxd5_1-Re-0xY6totprgypSHC-mkmg%40mail.gmail.com>
Precedence: bulk

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

On Tue, Feb 10, 2026 at 11:05=E2=80=AFAM David G. Johnston <
david.g.johnston@gmail.com> wrote:

> 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 c=
ontriving 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=
.
>

What about pgstattuple.free_space and free_percent?

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

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

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Feb 10, 2026 at 11:05=E2=80=AFAM =
David G. Johnston &lt;<a href=3D"mailto:david.g.johnston@gmail.com">david.g=
.johnston@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quo=
te_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">On Tuesday,=
 February 10, 2026, Durgamahesh Manne &lt;<a href=3D"mailto:maheshpostgres9=
@gmail.com" target=3D"_blank">maheshpostgres9@gmail.com</a>&gt; wrote:<br><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"auto">Hi=C2=A0=
<div dir=3D"auto"><br></div><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 fill=
factor is working successfully=C2=A0</div><div dir=3D"auto"><br></div><div =
dir=3D"auto">A ratio of hot updates in catalog table should higher than val=
ue 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 neces=
sary for HOT, it is not sufficient.</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 big=
int 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).=
=C2=A0 There is a page-inspect contrib extension that provides low-level de=
tails.</div></blockquote><div><br></div><div>What about=C2=A0pgstattuple.fr=
ee_space and=C2=A0free_percent?</div><div><br></div></div><span class=3D"gm=
ail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signatu=
re"><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>

--0000000000008c0ccc064a7b9461--