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 05:29 Ron Johnson <[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:43 Durgamahesh Manne <[email protected]> parent: 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 07:45 Ron Johnson <[email protected]> parent: 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 09:44 Durgamahesh Manne <[email protected]> parent: Ron Johnson <[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 15:06 Peter J. Holzer <[email protected]> parent: Durgamahesh Manne <[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