public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Changing default fillfactor for the whole database
Date: Sun, 27 Apr 2025 09:54:24 -0400
Message-ID: <CANzqJaAQCt8gE2dyZ2W6fP-iWiZqykojNipGduds+N=WJuNyRw@mail.gmail.com> (raw)
In-Reply-To: <CAM2F1VMu747bv5X3wTvhzLQUWRaJzREdaS=coVz+WjxkBjzvng@mail.gmail.com>
References: <CAM2F1VMu747bv5X3wTvhzLQUWRaJzREdaS=coVz+WjxkBjzvng@mail.gmail.com>

On Sat, Apr 26, 2025 at 10:31 PM Marcelo Fernandes <[email protected]>
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!

view thread (5+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Changing default fillfactor for the whole database
  In-Reply-To: <CANzqJaAQCt8gE2dyZ2W6fP-iWiZqykojNipGduds+N=WJuNyRw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox