Horton
Menu

Thread

  1. Table bloat threshold limit to trigger repack

    Durgamahesh Manne <maheshpostgres9@gmail.com> — 2026-02-08T04:19:05Z

    Hi

How much table bloat is acceptable before it affects performance in
PostgreSQL?



Regards
Durga Mahesh

  2. Re: Table bloat threshold limit to trigger repack

    Ron Johnson <ronljohnsonjr@gmail.com> — 2026-02-08T05:29:14Z

    On Sat, Feb 7, 2026 at 11:19 PM Durgamahesh Manne <maheshpostgres9@gmail.com>
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!

  3. Re: Table bloat threshold limit to trigger repack

    Durgamahesh Manne <maheshpostgres9@gmail.com> — 2026-02-08T05:43:07Z

    On Sun, 8 Feb, 2026, 10:59 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:

> On Sat, Feb 7, 2026 at 11:19 PM Durgamahesh Manne <
> maheshpostgres9@gmail.com> 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

  4. Re: Table bloat threshold limit to trigger repack

    Ron Johnson <ronljohnsonjr@gmail.com> — 2026-02-08T07:45:37Z

    On Sun, Feb 8, 2026 at 12:43 AM Durgamahesh Manne <maheshpostgres9@gmail.com>
wrote:

> On Sun, 8 Feb, 2026, 10:59 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
>
>> On Sat, Feb 7, 2026 at 11:19 PM Durgamahesh Manne <
>> maheshpostgres9@gmail.com> 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!

  5. Re: Table bloat threshold limit to trigger repack

    Durgamahesh Manne <maheshpostgres9@gmail.com> — 2026-02-08T09:44:37Z

    On Sun, 8 Feb, 2026, 13:15 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:

> On Sun, Feb 8, 2026 at 12:43 AM Durgamahesh Manne <
> maheshpostgres9@gmail.com> wrote:
>
>> On Sun, 8 Feb, 2026, 10:59 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
>>
>>> On Sat, Feb 7, 2026 at 11:19 PM Durgamahesh Manne <
>>> maheshpostgres9@gmail.com> 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

>

  6. Re: Table bloat threshold limit to trigger repack

    Peter J. Holzer <hjp-pgsql@hjp.at> — 2026-02-08T15:06:40Z

    On 2026-02-08 15:14:37 +0530, Durgamahesh Manne wrote:
> On Sun, 8 Feb, 2026, 13:15 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
>     On Sun, Feb 8, 2026 at 12:43 AM Durgamahesh Manne <
>     maheshpostgres9@gmail.com> wrote:
>         On Sun, 8 Feb, 2026, 10:59 Ron Johnson, <ronljohnsonjr@gmail.com>
>         wrote:
>             On Sat, Feb 7, 2026 at 11:19 PM Durgamahesh Manne <
>             maheshpostgres9@gmail.com> 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.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

  7. Re: Table bloat threshold limit to trigger repack

    Ron Johnson <ronljohnsonjr@gmail.com> — 2026-02-08T16:26:46Z

    On Sun, Feb 8, 2026 at 4:44 AM Durgamahesh Manne <maheshpostgres9@gmail.com>
wrote:

> On Sun, 8 Feb, 2026, 13:15 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
>
>> On Sun, Feb 8, 2026 at 12:43 AM Durgamahesh Manne <
>> maheshpostgres9@gmail.com> wrote:
>>
>>> On Sun, 8 Feb, 2026, 10:59 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
>>>
>>>> On Sat, Feb 7, 2026 at 11:19 PM Durgamahesh Manne <
>>>> maheshpostgres9@gmail.com> 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?
>>>>
>>>
>>>
>> 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?
>>
>
>
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
>

1. What is autovacuum_vacuum_scale_factor set to?
2. How often does the autovacuum run? (pg_stat_user_tables will tell you.)
3. Do you update any of those indexed columns?
4. How often do queries/reports need to read large chunks of the table (aka
sequentially scan it)?
5. Is performance currently suffering, or are you proactively worrying?

Note: Regular vacuuming eliminates bloat.

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

  8. Re: Table bloat threshold limit to trigger repack

    Durgamahesh Manne <maheshpostgres9@gmail.com> — 2026-02-08T18:05:17Z

    On Sun, 8 Feb, 2026, 21:57 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:

> On Sun, Feb 8, 2026 at 4:44 AM Durgamahesh Manne <
> maheshpostgres9@gmail.com> wrote:
>
>> On Sun, 8 Feb, 2026, 13:15 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
>>
>>> On Sun, Feb 8, 2026 at 12:43 AM Durgamahesh Manne <
>>> maheshpostgres9@gmail.com> wrote:
>>>
>>>> On Sun, 8 Feb, 2026, 10:59 Ron Johnson, <ronljohnsonjr@gmail.com>
>>>> wrote:
>>>>
>>>>> On Sat, Feb 7, 2026 at 11:19 PM Durgamahesh Manne <
>>>>> maheshpostgres9@gmail.com> 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?
>>>>>
>>>>
>>>>
>>> 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?
>>>
>>
>>
> 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
>>
>
> 1. What is autovacuum_vacuum_scale_factor set to?
> 2. How often does the autovacuum run? (pg_stat_user_tables will tell you.)
> 3. Do you update any of those indexed columns?
> 4. How often do queries/reports need to read large chunks of the table
> (aka sequentially scan it)?
> 5. Is performance currently suffering, or are you proactively worrying?
>
> Note: Regular vacuuming eliminates bloat.
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>

Hi

Periodic maintenance activity already enabled that runs for everyday once

1).sclae factor for toast 0.06 and non toast 0.1
2).observers that autovacuum runs for every 1hour
3).2indexed columns are being updated but I think it shouldn't be
4).most of the time index scan but not sequential scan
5).Seem to be good average latency is less  for queries
But trying to optimize better than now

Triggers are already removed

Any recommendations are welcome
Please find attached ss

>

  9. Re: Table bloat threshold limit to trigger repack

    Adrian Klaver <adrian.klaver@aklaver.com> — 2026-02-08T20:46:38Z

    On 2/8/26 10:05, Durgamahesh Manne wrote:
> 

> Any recommendations are welcome
> Please find attached ss
> 

FYI, you can copy and paste from a terminal session in Windows.

Just select the text with the mouse and hit Enter, then Ctl+V to paste 
it somewhere else.

Text in an email is easier to work with if someone wants to set up a 
test case on their machine.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

  10. Re: Table bloat threshold limit to trigger repack

    Ron Johnson <ronljohnsonjr@gmail.com> — 2026-02-08T21:06:09Z

    On Sun, Feb 8, 2026 at 1:05 PM Durgamahesh Manne <maheshpostgres9@gmail.com>
wrote:

>
>
>
> On Sun, 8 Feb, 2026, 21:57 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
>
>> On Sun, Feb 8, 2026 at 4:44 AM Durgamahesh Manne <
>> maheshpostgres9@gmail.com> wrote:
>>
>>> On Sun, 8 Feb, 2026, 13:15 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
>>>
>>>> On Sun, Feb 8, 2026 at 12:43 AM Durgamahesh Manne <
>>>> maheshpostgres9@gmail.com> wrote:
>>>>
>>>>> On Sun, 8 Feb, 2026, 10:59 Ron Johnson, <ronljohnsonjr@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> On Sat, Feb 7, 2026 at 11:19 PM Durgamahesh Manne <
>>>>>> maheshpostgres9@gmail.com> 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?
>>>>>>
>>>>>
>>>>>
>>>> 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?
>>>>
>>>
>>>
>> 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
>>>
>>
>> 1. What is autovacuum_vacuum_scale_factor set to?
>> 2. How often does the autovacuum run? (pg_stat_user_tables will tell you.)
>> 3. Do you update any of those indexed columns?
>> 4. How often do queries/reports need to read large chunks of the table
>> (aka sequentially scan it)?
>> 5. Is performance currently suffering, or are you proactively worrying?
>>
>> Note: Regular vacuuming eliminates bloat.
>>
>
>
Hi
>
> Periodic maintenance activity already enabled that runs for everyday once
>
> 1).sclae factor for toast 0.06 and non toast 0.1
>

Good.


> 2).observers that autovacuum runs for every 1hour
>

Good.


> 3).2indexed columns are being updated but I think it shouldn't be
>

Interesting.  As you seemingly suspect, fewer index updates speed things up.


> 4).most of the time index scan but not sequential scan
>

Well, as you probably know, bloat makes sequential scans slower, since
there's more file to scan.  Sometimes, though, you've got to choose "faster
updates" or "faster sequential scans".


> 5).Seem to be good average latency is less  for queries
> But trying to optimize better than now
>

If it's heavy on the updates, then lowering that fill factor *after*
eliminating updates of indexed fields will definitely speed UPDATE
statements *at the expense of* table sequential scans.

https://www.postgresql.org/docs/17/storage-hot.html


> Triggers are already removed
>

+1

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

  11. Re: Table bloat threshold limit to trigger repack

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

    On Mon, 9 Feb, 2026, 02:16 Adrian Klaver, <adrian.klaver@aklaver.com> wrote:

> On 2/8/26 10:05, Durgamahesh Manne wrote:
> >
>
> > Any recommendations are welcome
> > Please find attached ss
> >
>
> FYI, you can copy and paste from a terminal session in Windows.
>
> Just select the text with the mouse and hit Enter, then Ctl+V to paste
> it somewhere else.
>
> Text in an email is easier to work with if someone wants to set up a
> test case on their machine.
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com


Hi @Adrian Klaver <adrian.klaver@aklaver.com>

In this case autovacuum triggers for every 1hour on toast table

Access method: heap Options: fillfactor=80,
autovacuum_vacuum_cost_limit=2000, autovacuum_vacuum_cost_delay=10,
autovacuum_enabled=true, autovacuum_vacuum_scale_factor=0.1,
autovacuum_vacuum_threshold=100000, autovacuum_analyze_threshold=20000,
toast.autovacuum_enabled=true, toast.autovacuum_vacuum_scale_factor=0.1,
toast.autovacuum_vacuum_cost_limit=3000,
toast.autovacuum_vacuum_threshold=400000


Please let me know if any recommendations
And need to increase speed of autovacuum execution time which takes 5min to
complete
Ram 128gb
Cpu 32

Autovacuum work mem 6gb assigned
Buffer usage limit?
Regards
Durga Mahesh