I have a table which is about 12 GB in size. It has had a vacuum full analyze run on it, and then immediately after, I run vacuum analyze and it takes about 90 seconds to complete. If I continue to run vacuum analyze on it, it continues to take about 90 seconds each time. This system has a single 7200 RPM drive in it, so it's not a very fast drive. I was under the impression that if nothing had been done to the table since it was last vacuumed, that it would return immediately. Further, this is an append-only table, so why should it need to be vacuumed at all? We ran into cases where after writing to it long enough, the PGSQL autovacuum process would kick in and force a vacuum saying something about preventing wrap around. I don't understand why it would do this if it is append-only and we are using 64-bit sequences as IDs without OIDs turned on. What would be wrapping around without a vacuum? We tried to mitigate this by manually running vacuum programmatically, but then we end up using all the disk IO just running vacuum all the time, because it is constantly running through the entire table even though very little (if anything) has been done to it since the last vacuum.
Is this described behavior expected? If so, why?
If it is not expected, what should I be looking for which might explain why it is taking so long?
Eliot Gable
"We do not inherit the Earth from our ancestors: we borrow it from our children." ~David Brower
"I decided the words were too conservative for me. We're not borrowing from our children, we're stealing from them--and it's not even considered to be a crime." ~David Brower
"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not live to eat.) ~Marcus Tullius Cicero
