The documentation has information like "This parameter can only be set in the postgresql.conf file or on the server command line." that will tell you in advance which settings will fail when you attempt to set them through SQL statements.
Ah. I missed that. Sorry for asking stupid questions.
But autovacuum is pretty smart about not vacuuming tables until reasonably necessary. And beware that autovacuum is also controlling when to analyze a table. Mass inserts are probably changing the characteristics of your table such that it needs to be analyzed to allow the planner to properly optimize your queries.
Okay, that makes more sense to me; because the stats would be changing quickly and so while vacuuming may not be necessary, analyzing would be. At the same time, I can't afford to analyze if it's causing my inserts to take over 50 ms. Something else I should add: if my selects are slow, that's annoying; but if my inserts are slow, that could be disastrous. Does analyze increase the efficiency of inserts or just selects? (I assumed the latter.) Obviously, I will need to analyze sometimes, but perhaps not nearly as often as postgres would predict under the circumstances.
Have you identified that vacuum is actually causing a problem? If not, I'd leave it alone. The system tables have a lot of information on table vacuuming and analyzing:
Not indubitably, but circumstantially, I did notice that significantly fewer of my commits were taking over 50 ms after I set autovacuum_enabled=off on many of my tables. Unfortunately, it was not an isolated experiment, so I can't really be sure. At the same time, I'm hesitant to turn it back on until I'm sure it either didn't make a difference or I've got a better plan for how to deal with this.
select
relname,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze,
vacuum_count,
autovacuum_count,
analyze_count,
autoanalyze_count
from
pg_stat_user_tables;
Apparently the last four columns don't exist in my database. As for the first four, that is somewhat illuminating. It looks like the last_autovacuum that occurred on any of my tables was late Monday evening (almost two days before I set autovacuum_enabled=off). The last_autoanalyze on one of the tables where I set autovacuum_enabled=off was yesterday at 10:30, several hours before I disabled auto-vacuum. (I've had others since then on tables where I didn't disable auto-vacuum.) It looks like disabling auto-vacuum also disabled auto-analyze (did it?) but it also looks like that might not have been the continuous problem I thought it was.
So if it's not auto-vacuuming that's making my inserts so slow, what is it? I'm batching my inserts (that didn't seem to help at all actually, but maybe cause I had already turned off synchronous_commit anyway). I've gotten rid of a bunch of indices (especially those with low cardinality–that I did around the same time as disabling auto-vacuum, so that could account for the coincidental speed up). I'm not sure what else I could be doing wrong. It's definitely better than it was a few days ago, but I still see "LOG: duration: 77.315 ms statement: COMMIT" every minute or two.
Thank you,
-Alessandro
