On Thu, Apr 16, 2020 at 11:19 PM Justin Pryzby <pry...@telsasoft.com> wrote: > On Thu, Apr 16, 2020 at 06:16:45PM +0900, yuzuko wrote: > > The latest patch lets users set different autovacuum configuration for > > each partitioned > > tables like this, > > create table p3(i int) partition by range(i) with > > (autovacuum_analyze_scale_factor=0.0005, > > autovacuum_analyze_threshold=100); > > so users can configure those parameters according to partitioning strategies > > and other requirements. > > > > So I think this patch can solve problem you mentioned. > > I don't think that adequately allows what's needed. > > I think it out to be possible to get the "analyze parent whenever a child is > analyzed" behavior easily, without having to compute new thershold parameters > every time one adds partitions, detaches partitions, loades 10x more data into > one of the partitions, load only 10% as much data into the latest partition, > etc. > > For example, say a new customer has bunch of partitioned tables which each > currently have only one partition (for the current month), and that's expected > to grow to at least 20+ partitions (2+ years of history). How does one set > the > partitioned table's auto-analyze parameters to analyze whenever any child is > analyzed ? I don't think it should be needed to update it every month after > computing sum(child tuples). > > Possibly you could allow that behavior for some special values of the > threshold. Like if autovacuum_analyze_threshold=-2, then analyze the parent > whenever any of its children are analyzed. > > I think that use case and that need would be common, but I'd like to hear what > others think.
Having to constantly pay attention to whether a parent's analyze_threshold/scale_factor is working as intended would surely be an annoyance, so I tend to agree that we might need more than just the ability to set analyze_threshold/scale_factor on parent tables. However, I think we can at least start with being able to do *something* here. :) Maybe others think that this shouldn't be considered committable until we figure out a good analyze threshold calculation formula to apply to parent tables. For the cases in which parent's tuple count grows at about the same rate as partitions (hash mainly), I guess the existing formula more or less works. That is, we can set the parent's threshold/scale_factor same as partitions' and the autovacuum's existing formula will ensure that the parent is auto-analyzed in time and not more than needed. For time-series partitioning, the same formula won't work, as you have detailed in your comments. Is there any other partitioning pattern for which the current formula won't work? Considering that, how about having, say, a autovacuum_analyze_partition_parent_frequency, with string values 'default', 'partition'? -- 'default' assumes the same formula as regular tables, whereas with 'partition', parent is analyzed as soon as a partition is. -- Amit Langote EnterpriseDB: http://www.enterprisedb.com
