vacuum: Clears out deleted records from disk
Not exactly. VACUUM
marks rows that aren't visible to any active transaction any more as dead (and ready for re-use). It does not shrink the physical file size that represents the table, except for completely dead / empty pages at the physical end. The manual explains everything and probably better than I could recap here.
analyse: Updates the query planner
It's officially ANALYZE
, with Z
, but ANALYSE
is accepted as alternative spelling, too. And it updates statistics used by the query planner. Again, the manual already provides the best explanation.
In postgres.conf
... my presumption is that even though it's commented out, it should still be on?
That's correct. Again, consider details in the manual.
With that many write operations (a few thousand records a minute) your system is in bad need of regular VACUUM
/ ANALYZE
runs. You have read the manual by now, so you understand the consequences. If your table ...
hadn't been autovacuumed for more than a week.
... then that's bad for multiple reasons. Also consider @Daniel's answer how this may have transpired with a huge table like yours. Or maybe the high load constantly locks the table and never lets VACUUM
do its work. Again, it's all documented in the manual. Here is a related case with good answers how to tune settings:
Remember that you can have per-table
settings (STORAGE parameters) to fine tune for special needs of a special table and leave the rest of the system alone.
If you are mostly updating recently inserted rows, a FILLFACTOR
below 100 may be very helpful. You can compact the table (once) with CLUSTER
or pg_repack
an then set the FILLFACTOR
below 100. And for huge tables it may also help to set a higher STATISTICS
target for key columns with irregular data distribution.
Also, if old rows are updated seldom, partitioning might be a good solution, to treat old sections differently. It really depends on the complete picture ...
Also, don't forget indices, those can get bloated, too. Only keep indices that you actually need.
To see a count of dead and live tuples:
You won't see a "autovacuum" process. You will only see postgres.exe
processes. And because autovacuum doesn't run all the time, you will only occasionally see an additional process starting and stopping (but it will still be named postgres.exe
).
If you want to verify that auto vacuum is really running you can change the configuration property log_autovacuum_min_duration
to 0 (zero) and then each auto-vacuum run will be written to the logfile.
If you use a tool like ProcessExplorer you can easily see new processes when they start up:
The highlighted process only lives for a short moment and is likely to be the auto-vacuum process doing its job (as nothing else was going on in my system I can be pretty sure about that)
Why query retrieved mentioned result?
Auto vacuum only does "something" when it's necessary. If you never change your tables (no updates, no deletes) there is nothing to do for auto vacuum.
Best Answer
There is no good reason to keep autovacuum disabled in a production database, and you will run into problems if you do that.
It may make sense to disable autovacuum during exceptional operations, where you run a manual vacuum anyway and don't want your performance sapped by a background job, but more often than not such trickery is a premature optimization.
I would run
VACUUM (FULL)
on the database, followed byVACUUM (ANALYZE)
. That shoud take care of any damage done.Then enable autovacuum.