Horton
Menu

Thread

  1. Fillfactor effectiveness on existing table

    Durgamahesh Manne <maheshpostgres9@gmail.com> — 2026-02-10T15:55:38Z

    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 ?


Regards,
Durga Mahesh

  2. Re: Fillfactor effectiveness on existing table

    David G. Johnston <david.g.johnston@gmail.com> — 2026-02-10T16:05:06Z

    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’t buggy I’d suggest contriving 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.

  3. Re: Fillfactor effectiveness on existing table

    Ron Johnson <ronljohnsonjr@gmail.com> — 2026-02-10T17:28:03Z

    On Tue, Feb 10, 2026 at 11:05 AM 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’t buggy I’d suggest contriving 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?

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

  4. Re: Fillfactor effectiveness on existing table

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-02-10T18:31:08Z

    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

  5. Re: Fillfactor effectiveness on existing table

    Durgamahesh Manne <maheshpostgres9@gmail.com> — 2026-02-11T02:28:41Z

    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

>

  6. Re: Fillfactor effectiveness on existing table

    Durgamahesh Manne <maheshpostgres9@gmail.com> — 2026-02-11T02:34:02Z

    On Tue, 10 Feb, 2026, 22:58 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:

> On Tue, Feb 10, 2026 at 11:05 AM 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’t buggy I’d suggest contriving 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?
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>


Hi @Ron <ronljohnsonjr@gmail.com>
approx_free_space | 13227478672 approx_free_percent | 30.89065723142561

Free space can be considered as bloat for non toast table but not for both
toast and non toast I believe


Regards
Durga Mahesh

>

  7. Re: Fillfactor effectiveness on existing table

    Ron Johnson <ronljohnsonjr@gmail.com> — 2026-02-11T03:02:36Z

    On Tue, Feb 10, 2026 at 9:34 PM Durgamahesh Manne <maheshpostgres9@gmail.com>
wrote:

>
>
> On Tue, 10 Feb, 2026, 22:58 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
>
>> On Tue, Feb 10, 2026 at 11:05 AM 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’t buggy I’d suggest contriving 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?
>>
> Hi @Ron <ronljohnsonjr@gmail.com>
> approx_free_space | 13227478672 approx_free_percent | 30.89065723142561
>
> Free space can be considered as bloat for non toast table but not for both
> toast and non toast I believe
>

1) Did you vacuum?
2) What's the fillfactor?

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

  8. Re: Fillfactor effectiveness on existing table

    Durgamahesh Manne <maheshpostgres9@gmail.com> — 2026-02-11T03:26:38Z

    On Wed, 11 Feb, 2026, 08:32 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:

> On Tue, Feb 10, 2026 at 9:34 PM Durgamahesh Manne <
> maheshpostgres9@gmail.com> wrote:
>
>>
>>
>> On Tue, 10 Feb, 2026, 22:58 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
>>
>>> On Tue, Feb 10, 2026 at 11:05 AM 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’t buggy I’d suggest contriving 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?
>>>
>> Hi @Ron <ronljohnsonjr@gmail.com>
>> approx_free_space | 13227478672 approx_free_percent | 30.89065723142561
>>
>> Free space can be considered as bloat for non toast table but not for
>> both toast and non toast I believe
>>
>
> 1) Did you vacuum?
> 2) What's the fillfactor?
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>

@Ron <ronljohnsonjr@gmail.com>

Hi
1) vacuum runs daily once and autovacuum triggers for every 1 hour
2) 80

Regards
Durga Mahesh

>

  9. Re: Fillfactor effectiveness on existing table

    Laurenz Albe <laurenz.albe@cybertec.at> — 2026-02-11T06:37:12Z

    On Wed, 2026-02-11 at 07:58 +0530, Durgamahesh Manne wrote:
> Hi 
> @Laurenz Albe 
> 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

Did you read my e-mail?

Yours,
Laurenz Albe

  10. Re: Fillfactor effectiveness on existing table

    Durgamahesh Manne <maheshpostgres9@gmail.com> — 2026-02-11T08:53:47Z

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

> On Wed, 2026-02-11 at 07:58 +0530, Durgamahesh Manne wrote:
> > Hi
> > @Laurenz Albe
> > 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
>
> Did you read my e-mail?
>
> Yours,
> Laurenz Albe
>


Hi

Yes I have read. Will check get back to you soon. On fly info given earlier

Regards
Durga Mahesh

>

  11. Re: Fillfactor effectiveness on existing table

    Durgamahesh Manne <maheshpostgres9@gmail.com> — 2026-02-14T00:24:19Z

    Hi

All updates are HOT as per my observation

With one day bloat I see about 70 gb
Actual size of table is around 10gb only

I see strange behaviour even of completing repack activity for 14 and as
well as for 16  in the log

I think there was an issue at code side
How to fix this

2026-02-14 00:14:31.662
UTC,"offer","offer",674993,"127.0.0.1:28840",698fb1a0.a4cb1,376,"idle",2026-02-13
23:20:00 UTC,67/0,0,ERROR,26000,"unnamed prepared statement does not
exist",,,,,,,,,"e3d","client backend",,2064869707185898531 2026-02-14
00:14:31.792 UTC,"offer","offer",675945,"127.0.0.1:50690",698fb3f8.a5069,482,"ROLLBACK",2026-02-13
23:30:00 UTC,113/111425,3142683,LOG,00000,"temporary file: path
""base/pgsql_tmp/pgsql_tmp675945.44"", size
62053892",,,,,,"ROLLBACK",,,"e3d","client backend",,3749380189022910195
2026-02-14 00:14:32.849
UTC,"offer","offer",673259,"127.0.0.1:17360",698facf0.a45eb,842,"ROLLBACK",2026-02-13
23:00:00 UTC,65/168038,3142686,LOG,00000,"temporary file: path
""base/pgsql_tmp/pgsql_tmp673259.41"", size
64701020",,,,,,"ROLLBACK",,,"e3d","client backend",,3749380189022910195
2026-02-14 00:14:33.299
UTC,"offer","offer",675945,"127.0.0.1:50690",698fb3f8.a5069,483,"idle",2026-02-13
23:30:00 UTC,113/0,0,ERROR,26000,"unnamed prepared statement does not
exist",,,,,,,,,"e3d","client backend",,2064869707185898531 2026-02-14
00:14:33.299 UTC,"offer","offer",675945,"127.0.0.1:50690",698fb3f8.a5069,484,"idle",2026-02-13
23:30:00 UTC,113/0,0,ERROR,26000,"unnamed prepared statement does not
exist",,,,,,,,,"e3d","client backend",,2064869707185898531 2026-02-14
00:14:34.159 UTC,"offer","offer",674349,"127.0.0.1:61350",698fb074.a4a2d,396,"ROLLBACK",2026-02-13
23:15:00 UTC,49/153459,3142912,LOG,00000,"temporary file: path
""base/pgsql_tmp/pgsql_tmp674349.29"", size
44533064",,,,,,"ROLLBACK",,,"e3d","client backend",,3749380189022910195

Regards

Durga Mahesh

On Wed, 11 Feb, 2026, 14:23 Durgamahesh Manne, <maheshpostgres9@gmail.com>
wrote:

>
>
> On Wed, 11 Feb, 2026, 12:07 Laurenz Albe, <laurenz.albe@cybertec.at>
> wrote:
>
>> On Wed, 2026-02-11 at 07:58 +0530, Durgamahesh Manne wrote:
>> > Hi
>> > @Laurenz Albe
>> > 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
>>
>> Did you read my e-mail?
>>
>> Yours,
>> Laurenz Albe
>>
>
>
> Hi
>
> Yes I have read. Will check get back to you soon. On fly info given
> earlier
>
> Regards
> Durga Mahesh
>
>>