Why does autovacuum: VACUUM ANALYZE (to prevent wraparound) run?

Question

I have an autovacuum VACUUM ANALYZE query running on a table, and it always takes many hours, even a couple of days to finish. I know Postgres runs autovacuum jobs occasionally to perform cleanup and maintenance tasks, and it's necessary. However, most tables simply have an VACUUM, not a VACUUM ANALYZE.

Why does this specific table require a vacuum analyze, and how can I resolve the issue of it taking so long?

On a separate note, I did not notice this vacuum analyze query running before a few days ago. This is when I was attempting to create an index, and it failed prematurely saying it ran out of open files (or something like that). Could this contribute to the vacuum analyze running for so long?

Answer 1

Upgrading from PG 9.1 to PG 9.5 forced a situation where a number of tables reached their XID freeze limit. As a result, the running system is running autovacuum processes on a number of tables, many of them indicating '(to prevent wraparound)'. This has been a very busy database up to this point, so I am not surprised.

Since I can't force autovacuum to not carry this out, and since it's a bad idea to do so, I reconfigured the otherwise idle database to run autovacuum at a high rate of activity so it will complete faster (hopefully) and we can get back to business.

I set the following temporarily in my postgres.conf and it seems to be working quite well. Really gets the I/O cranking. I am leaving out the additional settings that optimize the WAL size and transactions, since that is highly system dependent:

# TEMPORARY -- aggressive autovacuum
autovacuum_max_workers = 16           # max number of autovacuum subprocesses
autovacuum_vacuum_cost_delay = 4ms    # default vacuum cost delay for
                                      #     autovacuum, in milliseconds;
autovacuum_vacuum_cost_limit = 10000  # default vacuum cost limit for autovacuum

I stop and start the db server and then monitor the transactions occurring using a shell call like so:

watch -d -n 300 psql -c "select query from pg_stat_activity;"

Answer 2

I think the VACUUM ANALYZE is a red herring. The table came due for both a VACUUM and an ANALYZE at the same time, so it is doing a VACUUM ANALYZE, but I really doubt that the ANALYZE is contributing to the problem at all.

I wonder if the "VACUUM (to prevent wrap around)" is ever finishing, or if it getting interrupted part way through and therefore restarting without ever making real progress. A good inspection of your log files should help clarify this (as well as help clarify exactly what that thing about running out of open files was about).

Also, based on the size of the table and your settings for cost-based vacuuming, you should be able to estimate how long the vacuum should take and compare that how long it is actually taking.

Also, the transaction throughput on your system is very relevant to wrap-around issues. Wraparound vacuums should be very rare, unless your database is extraordinarily active.