Subject: Regarding fillfactor use case for only delete ops

From: Durgamahesh Manne <[email-masked]>
Date: Fri, 6 Jun 2025 14:10:46 +0530

Hi  Team

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

Regards,
Durga Mahesh

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 06 Jun 2025 14:57:02 +0200

On Fri, 2025-06-06 at 14:10 +0530, Durgamahesh Manne wrote:
> 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.

Yours,
Laurenz Albe

---

From: Ron Johnson <[email-masked]>
Date: Fri, 6 Jun 2025 10:01:19 -0400

On Fri, Jun 6, 2025 at 4:36 AM Durgamahesh Manne <[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
>

This confuses me.  It seems that you are deleting old data instead of just
dropping the old partitions.

What did I misunderstand?

-- 
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:39:20 +0530

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

> On Fri, Jun 6, 2025 at 4:36 AM Durgamahesh Manne <
> [email-masked]> wrote:
>
>> Hi  Team
>>

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

Regards,
Durga Mahesh

---

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

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?

-- 
Adrian Klaver
[email-masked]