public inbox for [email protected]
help / color / mirror / Atom feedRe: Fillfactor effectiveness on existing table
5+ messages / 2 participants
[nested] [flat]
* Re: Fillfactor effectiveness on existing table
@ 2026-02-10 18:31 Laurenz Albe <[email protected]>
2026-02-11 02:28 ` Re: Fillfactor effectiveness on existing table Durgamahesh Manne <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2026-02-10 18:31 UTC (permalink / raw)
To: Durgamahesh Manne <[email protected]>; pgsql-general <[email protected]>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fillfactor effectiveness on existing table
2026-02-10 18:31 Re: Fillfactor effectiveness on existing table Laurenz Albe <[email protected]>
@ 2026-02-11 02:28 ` Durgamahesh Manne <[email protected]>
2026-02-11 06:37 ` Re: Fillfactor effectiveness on existing table Laurenz Albe <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Durgamahesh Manne @ 2026-02-11 02:28 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: pgsql-general <[email protected]>
On Wed, 11 Feb, 2026, 00:01 Laurenz Albe, <[email protected]> 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 <[email protected]>
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fillfactor effectiveness on existing table
2026-02-10 18:31 Re: Fillfactor effectiveness on existing table Laurenz Albe <[email protected]>
2026-02-11 02:28 ` Re: Fillfactor effectiveness on existing table Durgamahesh Manne <[email protected]>
@ 2026-02-11 06:37 ` Laurenz Albe <[email protected]>
2026-02-11 08:53 ` Re: Fillfactor effectiveness on existing table Durgamahesh Manne <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2026-02-11 06:37 UTC (permalink / raw)
To: Durgamahesh Manne <[email protected]>; +Cc: pgsql-general <[email protected]>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fillfactor effectiveness on existing table
2026-02-10 18:31 Re: Fillfactor effectiveness on existing table Laurenz Albe <[email protected]>
2026-02-11 02:28 ` Re: Fillfactor effectiveness on existing table Durgamahesh Manne <[email protected]>
2026-02-11 06:37 ` Re: Fillfactor effectiveness on existing table Laurenz Albe <[email protected]>
@ 2026-02-11 08:53 ` Durgamahesh Manne <[email protected]>
2026-02-14 00:24 ` Re: Fillfactor effectiveness on existing table Durgamahesh Manne <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Durgamahesh Manne @ 2026-02-11 08:53 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: pgsql-general <[email protected]>
On Wed, 11 Feb, 2026, 12:07 Laurenz Albe, <[email protected]> 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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fillfactor effectiveness on existing table
2026-02-10 18:31 Re: Fillfactor effectiveness on existing table Laurenz Albe <[email protected]>
2026-02-11 02:28 ` Re: Fillfactor effectiveness on existing table Durgamahesh Manne <[email protected]>
2026-02-11 06:37 ` Re: Fillfactor effectiveness on existing table Laurenz Albe <[email protected]>
2026-02-11 08:53 ` Re: Fillfactor effectiveness on existing table Durgamahesh Manne <[email protected]>
@ 2026-02-14 00:24 ` Durgamahesh Manne <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Durgamahesh Manne @ 2026-02-14 00:24 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: pgsql-general <[email protected]>
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, <[email protected]>
wrote:
>
>
> On Wed, 11 Feb, 2026, 12:07 Laurenz Albe, <[email protected]>
> 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
>
>>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-02-14 00:24 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-10 18:31 Re: Fillfactor effectiveness on existing table Laurenz Albe <[email protected]>
2026-02-11 02:28 ` Durgamahesh Manne <[email protected]>
2026-02-11 06:37 ` Laurenz Albe <[email protected]>
2026-02-11 08:53 ` Durgamahesh Manne <[email protected]>
2026-02-14 00:24 ` Durgamahesh Manne <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox