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.

  • Have you tried a VACUUM FREEZE issued 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.
    – bma
    Commented Sep 18, 2013 at 15:12
  • Unfortunately, VACUUM FREEZE generates 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. Commented Sep 19, 2013 at 11:34
  • 1
    Would it be feasible to recreate partitions with create table newpart as select * from oldpart and find a small window of time when you may lock out the readers and switch oldpart with newpart, then drop oldpart? Commented Sep 19, 2013 at 15:54
  • Can you show output of SELECT version()?
    – filiprem
    Commented Nov 12, 2013 at 14:20


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.