Re: Per table autovacuum vacuum cost limit behaviour strange

A while back we were discussing rapid space bloat of tables under certain circumstances. One further case I am examining is a highly volatile single table, and how to tame its space blowout.

I've got a nice simple example (attached). Making use of pgbench to run it as usual ():

$ createdb cache
$ psql cache < schema.sql
$ pgbench -n -c8 -T300 -f volatile0.sql cache

...causes the table (imaginatively named 'cache0') to grow several GB with default autovacuum parameters. Some minimal changes will rein in the growth to about 100MB:

$ grep -e naptime -e autovacuum_vacuum_cost_limit postgresql.conf
autovacuum_naptime = 5s
autovacuum_vacuum_cost_limit = 10000

However the cost_limit setting is likely to be way too aggressive globally. No problem I figured, I'd leave it at the default (200) and use ALTER TABLE to change it for *just* the 'cache0' table:

cache=# ALTER TABLE cache0 SET (autovacuum_vacuum_cost_limit=10000);

However re-running the pgbench test results in several GB worth of space used by this table. Hmmm - looks like setting this parameter per table does not work how I expected. Looking at src/backend/postmaster/autovacuum.c I see some balancing calculations in autovac_balance_cost() and AutoVacuumUpdateDelay(), the effect which seems to be (after adding some debugging elogs) to reset the actual effective cost_limit back to 200 for this table: viz (rel 16387 is cache0):


LOG:  autovac_balance_cost(pid=24058 db=16384, rel=16387, cost_limit=200, cost_limit_base=10000, cost_delay=20)
LOG:  autovac_update_delay(pid=24058 db=16384, rel=16387, cost_limit=200, cost_delay=20)

Is this working as intended? I did wonder if it was an artifact of only having 1 table (creating another one made no difference)...or perhaps only 1 active worker... I found I had to lobotomize the balancing calc by doing:

cache=# ALTER TABLE cache0 SET (autovacuum_vacuum_cost_delay=0);

before I got the same effect as just setting the cost_limit globally. I'm now a bit confused about whether I understand how setting cost_limit and cost_delay via ALTER TABLE works (or in fact if it is working properly for that matter).