Thread: Regarding fillfactor use case for only delete ops

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

Yours,
Laurenz Albe

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
adrian.klaver@aklaver.com

On 6/6/25 16:09, Durgamahesh Manne wrote:
> 

> Hi
> 
> I have partitioned table with retention policy of 90 days and will be 
> auto purged partitions beyond 90 days and  for last 90 days tables 
> inserts and deletes usually run through apl call
> Here due to deletes ops on last 90 days weekly partitions bloat 
> generates 50gb to 60gb for on weekly partition even we set autovacuum 
> params already in place for at child tables level

1) How are you measuring bloat?

2) Are inserts being done on the partitions all the way out to the 90 days?


> 
> Regards,
> Durga Mahesh

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Fri, 2025-06-06 at 09:59 -0400, Ron Johnson wrote:
> On Fri, Jun 6, 2025 at 8:57 AM Laurenz Albe <laurenz.albe@cybertec.at> 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
alreadyin 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.
>
> Isn't the fill factor aimed at reducing bloat during updates of HOT tables?

Yes, but not during DELETEs.

HOT updates also don't directly avoid bloat on tables; only on indexes.
They reduce the bloat on tables inderectly, because the dead tuples can
be cleaned up with less effort.

Yours,
Laurenz Albe