On Monday 08 December 2014 10:17:37 Jeff Janes wrote: > On Mon, Dec 8, 2014 at 4:54 AM, Vincent de Phily < > I don't think that routine vacuums even attempts to update relfrozenxid, or > at least doesn't try very hard.
AFAIU it does (the 6th paragraph in 23.1.4 seems to imply it), and vacuum_freeze_min_age controls when. Perhaps lowering that value would help prepare a lot of the anti-wraparound work.
There are two things that make autovac do an anti-wraparound vacuum. One of them is if the table is already going to be vacuumed anyway and vacuum_freeze_table_age is exceeded. In that case, the vacuum that was going to happen anyway gets promoted to be an anti-wraparound vacuum. The other case if whenautovacuum_freeze_max_age is exceeded. Then it forces a vacuum to happen even when there is no other reason for it to occur. I think the language in the 6th paragraph is contrasting these two ways to get an anti-wraparound vacuum, not contrasting ordinary vacuum with anti-wraparound vacuum.
Pity there's no "frozen pages map" (or is there ?) to keep track of pages will all-frozen tuples, it could speed up that anti-wraparound vacuum.
But not speed it up by much. As you discovered, most of the time is spent dealing with the indexes.
> Are you sure that routine vacuums have been running to completion on this > table, as opposed to getting interrupted by something before finishing each > time?
I checked pg_stat_user_tables.last_{auto_,}vacuum for this table and they are both null. This is seriously worrying. I've seen autovacuum take a few days on this table but always assumed that it finished properly. And I'm pretty sure vacuuming does some work, otherwise my disk usage woul grow much faster. I have no idea what could cause vacuuming to systematically stop before the end. Maybe I'll find something in the logs.
If it gets interrupted by another process needing the lock, it will be in the logs. But more recent versions than 9.1 will log more useful information.
If it is interrupted by a database shutdown or restart, it only logs that it was shutdown, it doesn't log what it was in the middle of doing at the time. (Perhaps that should be changed?).