public inbox for [email protected]
help / color / mirror / Atom feedRe: Table bloat threshold limit to trigger repack
5+ messages / 3 participants
[nested] [flat]
* Re: Table bloat threshold limit to trigger repack
@ 2026-02-08 05:29 Ron Johnson <[email protected]>
2026-02-08 05:43 ` Re: Table bloat threshold limit to trigger repack Durgamahesh Manne <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Ron Johnson @ 2026-02-08 05:29 UTC (permalink / raw)
To: pgsql-general <[email protected]>
On Sat, Feb 7, 2026 at 11:19 PM Durgamahesh Manne <[email protected]>
wrote:
> Hi
>
> How much table bloat is acceptable before it affects performance in
> PostgreSQL?
>
How big is the table? (For small tables, it doesn't matter.) How active is
it? How frequently are records updated?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Table bloat threshold limit to trigger repack
2026-02-08 05:29 Re: Table bloat threshold limit to trigger repack Ron Johnson <[email protected]>
@ 2026-02-08 05:43 ` Durgamahesh Manne <[email protected]>
2026-02-08 07:45 ` Re: Table bloat threshold limit to trigger repack Ron Johnson <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Durgamahesh Manne @ 2026-02-08 05:43 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-general <[email protected]>
On Sun, 8 Feb, 2026, 10:59 Ron Johnson, <[email protected]> wrote:
> On Sat, Feb 7, 2026 at 11:19 PM Durgamahesh Manne <
> [email protected]> wrote:
>
>> Hi
>>
>> How much table bloat is acceptable before it affects performance in
>> PostgreSQL?
>>
>
> How big is the table? (For small tables, it doesn't matter.) How active is
> it? How frequently are records updated?
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
Hi
Table size 100gb
I use pgstattuple_approx to get Table bloat is about 16gb as of now since
after repack is done on 27th of January
Fillfactor already in place
It's very critical application with updates on non partitioned table
Regards
Durga Mahesh
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Table bloat threshold limit to trigger repack
2026-02-08 05:29 Re: Table bloat threshold limit to trigger repack Ron Johnson <[email protected]>
2026-02-08 05:43 ` Re: Table bloat threshold limit to trigger repack Durgamahesh Manne <[email protected]>
@ 2026-02-08 07:45 ` Ron Johnson <[email protected]>
2026-02-08 09:44 ` Re: Table bloat threshold limit to trigger repack Durgamahesh Manne <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Ron Johnson @ 2026-02-08 07:45 UTC (permalink / raw)
To: Durgamahesh Manne <[email protected]>; +Cc: pgsql-general <[email protected]>
On Sun, Feb 8, 2026 at 12:43 AM Durgamahesh Manne <[email protected]>
wrote:
> On Sun, 8 Feb, 2026, 10:59 Ron Johnson, <[email protected]> wrote:
>
>> On Sat, Feb 7, 2026 at 11:19 PM Durgamahesh Manne <
>> [email protected]> wrote:
>>
>>> Hi
>>>
>>> How much table bloat is acceptable before it affects performance in
>>> PostgreSQL?
>>>
>>
>> How big is the table? (For small tables, it doesn't matter.) How active
>> is it? How frequently are records updated?
>>
>> --
>> Death to <Redacted>, and butter sauce.
>> Don't boil me, I'm still alive.
>> <Redacted> lobster!
>>
>
>
> Hi
>
> Table size 100gb
> I use pgstattuple_approx to get Table bloat is about 16gb as of now since
> after repack is done on 27th of January
> Fillfactor already in place
> It's very critical application with updates on non partitioned table
>
What did you set the fillfactor to?
Have you minimized the number of indexes? (That lets HOT work better.)
How long does it take to VACUUM the table?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Table bloat threshold limit to trigger repack
2026-02-08 05:29 Re: Table bloat threshold limit to trigger repack Ron Johnson <[email protected]>
2026-02-08 05:43 ` Re: Table bloat threshold limit to trigger repack Durgamahesh Manne <[email protected]>
2026-02-08 07:45 ` Re: Table bloat threshold limit to trigger repack Ron Johnson <[email protected]>
@ 2026-02-08 09:44 ` Durgamahesh Manne <[email protected]>
2026-02-08 15:06 ` Re: Table bloat threshold limit to trigger repack Peter J. Holzer <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Durgamahesh Manne @ 2026-02-08 09:44 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-general <[email protected]>
On Sun, 8 Feb, 2026, 13:15 Ron Johnson, <[email protected]> wrote:
> On Sun, Feb 8, 2026 at 12:43 AM Durgamahesh Manne <
> [email protected]> wrote:
>
>> On Sun, 8 Feb, 2026, 10:59 Ron Johnson, <[email protected]> wrote:
>>
>>> On Sat, Feb 7, 2026 at 11:19 PM Durgamahesh Manne <
>>> [email protected]> wrote:
>>>
>>>> Hi
>>>>
>>>> How much table bloat is acceptable before it affects performance in
>>>> PostgreSQL?
>>>>
>>>
>>> How big is the table? (For small tables, it doesn't matter.) How active
>>> is it? How frequently are records updated?
>>>
>>> --
>>> Death to <Redacted>, and butter sauce.
>>> Don't boil me, I'm still alive.
>>> <Redacted> lobster!
>>>
>>
>>
>> Hi
>>
>> Table size 100gb
>> I use pgstattuple_approx to get Table bloat is about 16gb as of now since
>> after repack is done on 27th of January
>> Fillfactor already in place
>> It's very critical application with updates on non partitioned table
>>
>
> What did you set the fillfactor to?
> Have you minimized the number of indexes? (That lets HOT work better.)
> How long does it take to VACUUM the table?
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
Hi
Fillfactor 80
3 composite and pkey on one column as queries use those
Vacuum 3min to complete
Here autovacuum 5min to complete during load even with param tuning
Regards
Durga Mahesh
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Table bloat threshold limit to trigger repack
2026-02-08 05:29 Re: Table bloat threshold limit to trigger repack Ron Johnson <[email protected]>
2026-02-08 05:43 ` Re: Table bloat threshold limit to trigger repack Durgamahesh Manne <[email protected]>
2026-02-08 07:45 ` Re: Table bloat threshold limit to trigger repack Ron Johnson <[email protected]>
2026-02-08 09:44 ` Re: Table bloat threshold limit to trigger repack Durgamahesh Manne <[email protected]>
@ 2026-02-08 15:06 ` Peter J. Holzer <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Peter J. Holzer @ 2026-02-08 15:06 UTC (permalink / raw)
To: [email protected]
On 2026-02-08 15:14:37 +0530, Durgamahesh Manne wrote:
> On Sun, 8 Feb, 2026, 13:15 Ron Johnson, <[email protected]> wrote:
> On Sun, Feb 8, 2026 at 12:43 AM Durgamahesh Manne <
> [email protected]> wrote:
> On Sun, 8 Feb, 2026, 10:59 Ron Johnson, <[email protected]>
> wrote:
> On Sat, Feb 7, 2026 at 11:19 PM Durgamahesh Manne <
> [email protected]> wrote:
> How much table bloat is acceptable before it affects
> performance in PostgreSQL?
>
> How big is the table? (For small tables, it doesn't matter.) How
> active is it? How frequently are records updated?
>
> Table size 100gb
> I use pgstattuple_approx to get Table bloat is about 16gb as of now
> since after repack is done on 27th of January
> Fillfactor already in place
> It's very critical application with updates on non partitioned table
>
>
> What did you set the fillfactor to?
> Have you minimized the number of indexes? (That lets HOT work better.)
> How long does it take to VACUUM the table?
>
> Fillfactor 80
With a fillfactor of 80 you should expect at least 20% of unused space,
or a "bloat" of (100 / 80) - 100 = 25 %.
Your 16 GB seem to be less than that which is a bit unexpected, but
might happen if you have an update-heavy workload. If you have 16 GB
more bloat than expected (i.e. you have 67 GB of data and therefore
expected a table size of 67 * 100 / 80 = 84 GB, but have 100 GB
instead), that may be worth investigating but isn't terribly concerning
if you have many inserts.
hjp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | [email protected] | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-02-08 15:06 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-08 05:29 Re: Table bloat threshold limit to trigger repack Ron Johnson <[email protected]>
2026-02-08 05:43 ` Durgamahesh Manne <[email protected]>
2026-02-08 07:45 ` Ron Johnson <[email protected]>
2026-02-08 09:44 ` Durgamahesh Manne <[email protected]>
2026-02-08 15:06 ` Peter J. Holzer <[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