Can we revert the autovacuum postgresql.conf overrides back to the defaults?

[jrafanie]

While reviewing #968 to solve ManageIQ/container-postgresql#40, @Fryguy suggested we should consider removing the autovacuum overrides found here.

These were set a very long time ago and we have no background on whether it's still needed. We have seen some table not being autoanalyzed so perhaps our threshold settings are less than ideal.

[jrafanie]

@kbrock @bdunne do you have any thoughts? I think Jason and I are both leaning towards using the deleting the autovacuum_* settings and using the defaults. At least until we have a good reason to change them. We've seen some demo environments where autoanalyze isn't running on some tables and I think it's because of our thresholds.

[kbrock]

Configuring Vacuum

Darn, thought I already created an issue. Can't find it.

Table percentage change based

The default values are optimized for reasonably sized tables (guessing 100,000 records).
The threshold doesn't really come to play here, the scale_factor is the interesting one:

autovacuum_vacuum_scale_factor = 0.2 # table changes by by 20%
autovacuum_vacuum_threshold    = 50  # ignore

threshold only comes into play for very small tables. And in those cases, we don't need to vacuum / rerun statistics.

example: If a table with 10 records adds 4 records, the table changed by 40% which is bigger than the 20% factor, and it would run vacuum.
Which we all know isn't necessary.
So the threshold says that we need to at least add 50 records before taking into account the scale factor.

Table rows change based

As you guessed, once you get to a million records, that 20% sure is big.

autovacuum_vacuum_threshold = 10000
autovacuum_vacuum_scale_factor = 0

That will vacuum every 10k records added. We need to research what we want.

Middle ground

The configuration referenced is an attempt to work with big and small tables. Otherwise our schema would need to configure each table. And that is not schema.rb frinendly.

I kinda like what they set

autovacuum_naptime = 5min # not familiar with this one
autovacuum_vacuum_threshold = 500
autovacuum_analyze_threshold = 500
autovacuum_vacuum_scale_factor = 0.05

They de-emphaized the scale factor (table changes by 5%) but added the threshold up. Which looks good since if it is that small, then don't bother.

But regardless of what they are setting, it is not doing what we need...

Our issue

But our table has a ton of changes, and vacuum is still not running.

So that means those changes won't do everything we need.
I'm guessing the issue is the process can't find a good time to vacuum.

Vacuum defers to read/write operations. And they are always happening.

So we probably need to tell the system that we want to vacuum, even if it may slow down some processes.

The referenced article shows how to be overly aggressive. This is meant as an extreme example and now what we want to do. But it does show the factors that come into play.

autovacuum_vacuum_cost_delay = 0 #Turn off cost based vacuum
autovacuum_vacuum_cost_limit = 10000 #Max value

good reference: https://dba.stackexchange.com/questions/21068/aggressive-autovacuum-on-postgresql

but I realize that article is good because I've already read up on the topic.

TL;DR

we can keep or drop those changes. They are not addressing our issue.
We need to focus on telling our system that we need to vacuum even if someone is constantly writing to the metrics / events table.

[Fryguy]

autovacuum_naptime is basically the minimum amount of time between autovacuum on a high-write table. Default is normally 1 minute.