public inbox for [email protected]help / color / mirror / Atom feed
Re: Table bloat threshold limit to trigger repack 5+ messages / 3 participants [nested] [flat]
* Re: Table bloat threshold limit to trigger repack @ 2026-02-08 16:26 Ron Johnson <[email protected]> 2026-02-08 18:05 ` 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 16:26 UTC (permalink / raw) To: pgsql-general 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! ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Table bloat threshold limit to trigger repack 2026-02-08 16:26 Re: Table bloat threshold limit to trigger repack Ron Johnson <[email protected]> @ 2026-02-08 18:05 ` Durgamahesh Manne <[email protected]> 2026-02-08 20:46 ` Re: Table bloat threshold limit to trigger repack Adrian Klaver <[email protected]> 2026-02-08 21:06 ` Re: Table bloat threshold limit to trigger repack Ron Johnson <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: Durgamahesh Manne @ 2026-02-08 18:05 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: pgsql-general 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Table bloat threshold limit to trigger repack 2026-02-08 16:26 Re: Table bloat threshold limit to trigger repack Ron Johnson <[email protected]> 2026-02-08 18:05 ` Re: Table bloat threshold limit to trigger repack Durgamahesh Manne <[email protected]> @ 2026-02-08 20:46 ` Adrian Klaver <[email protected]> 2026-02-11 02:41 ` Re: Table bloat threshold limit to trigger repack Durgamahesh Manne <[email protected]> 1 sibling, 1 reply; 5+ messages in thread From: Adrian Klaver @ 2026-02-08 20:46 UTC (permalink / raw) To: Durgamahesh Manne <[email protected]>; Ron Johnson <[email protected]>; +Cc: pgsql-general 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 [email protected] ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Table bloat threshold limit to trigger repack 2026-02-08 16:26 Re: Table bloat threshold limit to trigger repack Ron Johnson <[email protected]> 2026-02-08 18:05 ` Re: Table bloat threshold limit to trigger repack Durgamahesh Manne <[email protected]> 2026-02-08 20:46 ` Re: Table bloat threshold limit to trigger repack Adrian Klaver <[email protected]> @ 2026-02-11 02:41 ` Durgamahesh Manne <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Durgamahesh Manne @ 2026-02-11 02:41 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: Ron Johnson <[email protected]>; pgsql-general On Mon, 9 Feb, 2026, 02:16 Adrian Klaver, <[email protected]> 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 > [email protected] Hi @Adrian Klaver <[email protected]> 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Table bloat threshold limit to trigger repack 2026-02-08 16:26 Re: Table bloat threshold limit to trigger repack Ron Johnson <[email protected]> 2026-02-08 18:05 ` Re: Table bloat threshold limit to trigger repack Durgamahesh Manne <[email protected]> @ 2026-02-08 21:06 ` Ron Johnson <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Ron Johnson @ 2026-02-08 21:06 UTC (permalink / raw) To: pgsql-general 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! ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-02-11 02:41 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 16:26 Re: Table bloat threshold limit to trigger repack Ron Johnson <[email protected]> 2026-02-08 18:05 ` Durgamahesh Manne <[email protected]> 2026-02-08 20:46 ` Adrian Klaver <[email protected]> 2026-02-11 02:41 ` Durgamahesh Manne <[email protected]> 2026-02-08 21:06 ` Ron Johnson <[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