Re: Changing default fillfactor for the whole database

On Sat, Apr 26, 2025 at 10:31 PM Marcelo Fernandes <marcefern7(at)gmail(dot)com>
wrote:

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

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!