Thread: Changing default fillfactor for the whole database
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.
> On Apr 26, 2025, at 19:31, Marcelo Fernandes <marcefern7@gmail.com> wrote: > Does this make sense? Have I missed something about being able to change this > on a database level? 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/updatepattern for a particular table, so I'm not sure it makes sense to have a system-wide default. I can certainlysee it being a foot-gun, in setting it to (say) 50 and then some months later creating a table that is going toget very large, but is not update-heavy.
On Sun, 27 Apr 2025 at 14:31, Marcelo Fernandes <marcefern7@gmail.com> wrote: > I have a scenario where virtually all user tables in the database will need to > have a lower fill factor. > Does this make sense? Have I missed something about being able to change this > on a database level? 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
On Sat, Apr 26, 2025 at 10:31 PM Marcelo Fernandes <marcefern7@gmail.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!
> On Apr 27, 2025, at 06:54, Ron Johnson <ronljohnsonjr@gmail.com> 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 fairlyuncommon workloads. However, that doesn't mean we should add new ones. :-)