We are facing a strange problem since yesterday.
In a database of ours there is a partitioned table containing historical data. That is, every week's data goes to a different partition and stays there till the end of time. There are no further modifications to these - no UPDATE, no DELETE, no TRUNCATE, even no INSERT once the week has passed.
At least this is the theory. We realized that some bug caused a heavy multiplication of a part of the data. The order of magnitude is about 60k useful and some 15-20M unnecessary rows. There are other rows as well, unaffected by the bug - the overall partition size varies between 6 and 15 GBs.
Once the bug was fixed, we decided to remove the unnecessary rows, keeping the latest one of the sometimes 9000 siblings. Of course, this means that some serious vacuuming will be needed as around a quarter of the tables is gone now. The nice autovacuum daemon jumps on the back of the partitions a few minutes after finishing the removal. And, at least for some of the partitions, when it finishes, has a few minute's rest, and starts again. And again. And...
It seems that this has an adverse effect on other IO-heavy processes, so we tried to VACUUM ANALYZE manually the table(s) in question. As expected, this terminates the autovacuum process, removes what should be removed and exists successfully. And then the autovacuum process jumps again, and again, and... Each run lasts more than two hours.
Have you ever experienced this? If yes, what was the solution? I have a feeling that a VACUUM FULL would solve it, but as the applications must be able to read the tables nearly continuously, it is not an option. Actually, we are migrating away from that structure, and once it is done, the problem disappears, but this issue seems to slow the migration process down significantly. So any idea would be more than welcome.
VACUUM FREEZEissued on the partitions? That should help keep autovacuum from running over and over. Do you use two-phase commit in your system at all? I believe I read something recently about that causing autovac issues, but since our apps don't use 2PC I can't verify that assertion.VACUUM FREEZEgenerates a really high IO load which block normal operation. The ever-ticking autovacuum is still a better solution... Probably I'll try it scheduled to some low-activity period.create table newpart as select * from oldpartand find a small window of time when you may lock out the readers and switch oldpart with newpart, then drop oldpart?SELECT version()?