public inbox for [email protected]help / color / mirror / Atom feed
Changing default fillfactor for the whole database 5+ messages / 4 participants [nested] [flat]
* Changing default fillfactor for the whole database @ 2025-04-27 02:31 Marcelo Fernandes <[email protected]> 0 siblings, 3 replies; 5+ messages in thread From: Marcelo Fernandes @ 2025-04-27 02:31 UTC (permalink / raw) To: [email protected] 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. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Changing default fillfactor for the whole database @ 2025-04-27 03:15 Christophe Pettus <[email protected]> parent: Marcelo Fernandes <[email protected]> 2 siblings, 0 replies; 5+ messages in thread From: Christophe Pettus @ 2025-04-27 03:15 UTC (permalink / raw) To: Marcelo Fernandes <[email protected]>; +Cc: [email protected] > On Apr 26, 2025, at 19:31, Marcelo Fernandes <[email protected]> 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/update pattern for a particular table, so I'm not sure it makes sense to have a system-wide default. I can certainly see it being a foot-gun, in setting it to (say) 50 and then some months later creating a table that is going to get very large, but is not update-heavy. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Changing default fillfactor for the whole database @ 2025-04-27 03:30 David Rowley <[email protected]> parent: Marcelo Fernandes <[email protected]> 2 siblings, 0 replies; 5+ messages in thread From: David Rowley @ 2025-04-27 03:30 UTC (permalink / raw) To: Marcelo Fernandes <[email protected]>; +Cc: [email protected] On Sun, 27 Apr 2025 at 14:31, Marcelo Fernandes <[email protected]> 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Changing default fillfactor for the whole database @ 2025-04-27 13:54 Ron Johnson <[email protected]> parent: Marcelo Fernandes <[email protected]> 2 siblings, 1 reply; 5+ messages in thread From: Ron Johnson @ 2025-04-27 13:54 UTC (permalink / raw) To: pgsql-general 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! ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Changing default fillfactor for the whole database @ 2025-04-27 19:21 Christophe Pettus <[email protected]> parent: Ron Johnson <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Christophe Pettus @ 2025-04-27 19:21 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: pgsql-general > On Apr 27, 2025, at 06:54, Ron Johnson <[email protected]> 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 fairly uncommon workloads. However, that doesn't mean we should add new ones. :-) ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-04-27 19:21 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-04-27 02:31 Changing default fillfactor for the whole database Marcelo Fernandes <[email protected]> 2025-04-27 03:15 ` Christophe Pettus <[email protected]> 2025-04-27 03:30 ` David Rowley <[email protected]> 2025-04-27 13:54 ` Ron Johnson <[email protected]> 2025-04-27 19:21 ` Christophe Pettus <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox