On Sat, Apr 26, 2025 at 10:31 PM Marcelo Fernandes <marcefe...@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!
