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.
