Subject: Changing default fillfactor for the whole database

From: Marcelo Fernandes <[email-masked]>
Date: Sun, 27 Apr 2025 14:31:19 +1200

Hi there,

I have a scenario where virtually all user tables in the database will need to
have a lower fill factor.

It would have been handy to have a way to set this default, but as of now, I
don't think the default can be changed.

I have looked at `share/postgresql.conf.sample` file, and couldn't see
anything indicating this value could be changed.

Though I'm aware there's an ALTER TABLE command that can be used for that, I
think it would be good to avoid some future errors (forgetting to set it) if I
could change the default value.

Does this make sense? Have I missed something about being able to change this
on a database level?

Thanks,
Marcelo.

---

From: Christophe Pettus <[email-masked]>
Date: Sat, 26 Apr 2025 20:15:42 -0700

> On Apr 26, 2025, at 19:31, Marcelo Fernandes <[email-masked]> wrote:

You haven't missed anything; there's no setting that controls the default for fillfactor.

Everyone's use-case is different, of course, but my feeling is that a correct fillfactor is very sensitive to the insert/update pattern for a particular table, so I'm not sure it makes sense to have a system-wide default.  I can certainly see it being a foot-gun, in setting it to (say) 50 and then some months later creating a table that is going to get very large, but is not update-heavy.

---

From: David Rowley <[email-masked]>
Date: Sun, 27 Apr 2025 15:30:24 +1200

> Does this make sense? Have I missed something about being able to change this

I suspect that it's possible that you can use an event trigger [1]
that triggers on CREATE TABLE to get what you want here. If the docs
don't give you enough information, then maybe [2] will have something
you can work with. I've not used them much, so I don't have any
recipes for you.

David

[1] https://www.postgresql.org/docs/current/sql-createeventtrigger.html
[2] https://github.com/postgres/postgres/blob/master/src/test/regress/sql/event_trigger.sql

---

From: Ron Johnson <[email-masked]>
Date: Sun, 27 Apr 2025 09:54:24 -0400

> Does this make sense? Have I missed something about being able to change

Following Christophe's post: it's possible to query the catalog to find
tables which do not have a fill factor.  Combine that with format() and
\gexec to get a script you can run weekly, to catch new tables which you
forgot to set when creating.

I agree with him, though, that this a foot-gun: most table's aren't that
UPDATE heavy.  Run pgstattuple and pgstatindex every week (I do it on
Sundays around 05:00 local time, and store the results in a table; makes it
easy to find bloated tables), and only change the fill factor on
consistently bloated tables.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Christophe Pettus <[email-masked]>
Date: Sun, 27 Apr 2025 12:21:30 -0700

> On Apr 27, 2025, at 06:54, Ron Johnson <[email-masked]> wrote:
> 
> I agree with him, though, that this a foot-gun: most table's aren't that UPDATE heavy. 

There is plenty of precedent for GUCs that seem to be useful, but really should never be touched except in the case of fairly uncommon workloads.  However, that doesn't mean we should add new ones.  :-)