public inbox for [email protected]
help / color / mirror / Atom feedFrom: Durgamahesh Manne <[email protected]>
To: Ron Johnson <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Table bloat threshold limit to trigger repack
Date: Sun, 8 Feb 2026 23:35:17 +0530
Message-ID: <CAJCZkoK_JMcjawyCAGSo8gc8sHad7ugFLZzjuqP2Jmkn_G=Ecw@mail.gmail.com> (raw)
In-Reply-To: <CANzqJaCMWyPCcYhp0fd0=jatpcExFNsJ6=Gz8ZxEeUHGE-h+MQ@mail.gmail.com>
References: <CAJCZkoJF-97BatLoVf1SUBTAX8BrSeX0dgZS+tBOM2imNnGRFQ@mail.gmail.com>
<CANzqJaCoH2My0YU9cKDDAtSx5vsTyXcpVH9y7WxZCddNmRPU8w@mail.gmail.com>
<CAJCZkoK-ipKCuiLuc=S=nrNVCPnbsKAUOs6cS9=1kdftcBQFPw@mail.gmail.com>
<CANzqJaAO4AbdceTA=4bmSA4sCG6viP28hDeeYjYOUe=jzLVbmg@mail.gmail.com>
<CAJCZkoLc=YXc4H2-aGbp2e-R+WsaLRxiAoj2acLP4=A7z+7xAg@mail.gmail.com>
<CANzqJaCMWyPCcYhp0fd0=jatpcExFNsJ6=Gz8ZxEeUHGE-h+MQ@mail.gmail.com>
On Sun, 8 Feb, 2026, 21:57 Ron Johnson, <[email protected]> wrote:
> On Sun, Feb 8, 2026 at 4:44 AM Durgamahesh Manne <
> [email protected]> 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:
>>>>>
>>>>>> 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
>
Attachments:
[image/jpeg] IMG-20250928-WA0011.jpg (203.5K, 3-IMG-20250928-WA0011.jpg)
download | view image
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Table bloat threshold limit to trigger repack
In-Reply-To: <CAJCZkoK_JMcjawyCAGSo8gc8sHad7ugFLZzjuqP2Jmkn_G=Ecw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox