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
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.