Fillfactor effectiveness on existing table

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.

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!

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

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

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

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!

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

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

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

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: