MIME-Version: 1.0
References: <CAM2F1VMu747bv5X3wTvhzLQUWRaJzREdaS=coVz+WjxkBjzvng@mail.gmail.com>
In-Reply-To: <CAM2F1VMu747bv5X3wTvhzLQUWRaJzREdaS=coVz+WjxkBjzvng@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Sun, 27 Apr 2025 09:54:24 -0400
Message-ID: <CANzqJaAQCt8gE2dyZ2W6fP-iWiZqykojNipGduds+N=WJuNyRw@mail.gmail.com>
Subject: Re: Changing default fillfactor for the whole database
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000007184810633c2e8df"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaAQCt8gE2dyZ2W6fP-iWiZqykojNipGduds%2BN%3DWJuNyRw%40mail.gmail.com>
Precedence: bulk

--0000000000007184810633c2e8df
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sat, Apr 26, 2025 at 10:31=E2=80=AFPM Marcelo Fernandes <marcefern7@gmai=
l.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.

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

--0000000000007184810633c2e8df
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Sat, Apr 26, 2025 at 10:31=E2=80=AFPM =
Marcelo Fernandes &lt;<a href=3D"mailto:marcefern7@gmail.com">marcefern7@gm=
ail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container=
"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex">Hi there,<br>
<br>
I have a scenario where virtually all user tables in the database will need=
 to<br>
have a lower fill factor.<br>
<br>
It would have been handy to have a way to set this default, but as of now, =
I<br>
don&#39;t think the default can be changed.<br>
<br>
I have looked at `share/postgresql.conf.sample` file, and couldn&#39;t see<=
br>
anything indicating this value could be changed.<br>
<br>
Though I&#39;m aware there&#39;s an ALTER TABLE command that can be used fo=
r that, I<br>
think it would be good to avoid some future errors (forgetting to set it) i=
f I<br>
could change the default value.<br>
<br>
Does this make sense? Have I missed something about being able to change th=
is<br>
on a database level?<br></blockquote><div><br></div><div>Following Christop=
he&#39;s post: it&#39;s possible to query the catalog to find tables which =
do not have a fill factor.=C2=A0 Combine that with format() and \gexec to g=
et a script you=C2=A0can run weekly, to catch new tables which you forgot t=
o set when creating.</div><div><br></div><div>I agree with him, though, tha=
t this a foot-gun: most table&#39;s aren&#39;t that UPDATE heavy.=C2=A0 Run=
 pgstattuple and pgstatindex every week (I do it on Sundays around 05:00 lo=
cal time, and store the results in a table; makes it easy to find bloated t=
ables), and only change the fill factor on consistently bloated tables.</di=
v></div><div><br></div><span class=3D"gmail_signature_prefix">-- </span><br=
><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;R=
edacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<=
br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div></div></div>

--0000000000007184810633c2e8df--