Subject: Re: Regarding fillfactor use case for only delete ops

From: Ron Johnson <[email-masked]>
Date: Fri, 6 Jun 2025 09:59:14 -0400

On Fri, Jun 6, 2025 at 8:57 AM Laurenz Albe <[email-masked]>
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!

---

From: Durgamahesh Manne <[email-masked]>
Date: Sat, 7 Jun 2025 04:34:21 +0530

On Fri, Jun 6, 2025 at 7:29 PM Ron Johnson <[email-masked]> wrote:

>> > 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
>>

Hi

I believe that fill factor works exclusively for updates of HOT tables  but
not for delete ops

Regards,
Durga Mahesh

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 6 Jun 2025 16:14:27 -0700

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.

-- 
Adrian Klaver
[email-masked]