public inbox for [email protected]help / color / mirror / Atom feed
Re: Regarding fillfactor use case for only delete ops 3+ messages / 3 participants [nested] [flat]
* Re: Regarding fillfactor use case for only delete ops @ 2025-06-06 13:59 Ron Johnson <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Ron Johnson @ 2025-06-06 13:59 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: Durgamahesh Manne <[email protected]>; pgsql-general <[email protected]> On Fri, Jun 6, 2025 at 8:57 AM Laurenz Albe <[email protected]> wrote: > On Fri, 2025-06-06 at 14:10 +0530, Durgamahesh Manne wrote: > > Can we generate a fill factor for tables that have delete ops ? > > > > Does the fill factor really work and help to minimize the bloat for > tables that have delete ops? > > > > I have parent table with weekly partitions So for every week 50 to 60 gb > of bloat generates and autovacuum params already in place for child tables > > Nothing can ever avoid bloat caused by DELETE, except partitioning in a > way that you can drop a partition rather than running DELETE. > > Laurenz, Isn't the fill factor aimed at reducing bloat during updates of HOT tables? -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster! ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Regarding fillfactor use case for only delete ops @ 2025-06-06 23:04 Durgamahesh Manne <[email protected]> parent: Ron Johnson <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Durgamahesh Manne @ 2025-06-06 23:04 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: Laurenz Albe <[email protected]>; pgsql-general <[email protected]> On Fri, Jun 6, 2025 at 7:29 PM Ron Johnson <[email protected]> wrote: > On Fri, Jun 6, 2025 at 8:57 AM Laurenz Albe <[email protected]> > wrote: > >> On Fri, 2025-06-06 at 14:10 +0530, Durgamahesh Manne wrote: >> > Can we generate a fill factor for tables that have delete ops ? >> > >> > Does the fill factor really work and help to minimize the bloat for >> tables that have delete ops? >> > >> > I have parent table with weekly partitions So for every week 50 to 60 >> gb of bloat generates and autovacuum params already in place for child >> tables >> >> Nothing can ever avoid bloat caused by DELETE, except partitioning in a >> way that you can drop a partition rather than running DELETE. >> >> > Laurenz, > > Isn't the fill factor aimed at reducing bloat during updates of HOT tables? > > -- > Death to <Redacted>, and butter sauce. > Don't boil me, I'm still alive. > <Redacted> lobster! > Hi I believe that fill factor works exclusively for updates of HOT tables but not for delete ops Regards, Durga Mahesh ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Regarding fillfactor use case for only delete ops @ 2025-06-06 23:14 Adrian Klaver <[email protected]> parent: Durgamahesh Manne <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Adrian Klaver @ 2025-06-06 23:14 UTC (permalink / raw) To: Durgamahesh Manne <[email protected]>; Ron Johnson <[email protected]>; +Cc: Laurenz Albe <[email protected]>; pgsql-general <[email protected]> On 6/6/25 16:04, Durgamahesh Manne wrote: > > > Hi > > I believe that fill factor works exclusively for updates of HOT tables > but not for delete ops From here: https://www.postgresql.org/docs/current/sql-createtable.html "fillfactor (integer) The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page, and makes heap-only tuple updates more likely. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate. This parameter cannot be set for TOAST tables. " So it increases the chances of HOT updates, but is not restricted to them. > > Regards, > Durga Mahesh > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-06-06 23:14 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-06-06 13:59 Re: Regarding fillfactor use case for only delete ops Ron Johnson <[email protected]> 2025-06-06 23:04 ` Durgamahesh Manne <[email protected]> 2025-06-06 23:14 ` Adrian Klaver <[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