On Sun, Feb 8, 2026 at 1:05 PM Durgamahesh Manne <[email protected]> wrote:
> > > > 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. >> > > 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!
