Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I manage a big (some hundreds of gigs) database containing tables with various roles, some of them holding millions of records. Some tables only receive large number of inserts and deletes, some other few inserts and large number of updates.

Database runs on PostgreSQL 8.4 on a Debian 6.0 amd64 system with 16 gigabytes of RAM.

The question is sometimes autovacuum process on a table, takes a very long time (days) to complete. I want to be able to roughly tell how much time a particular vacuum command will take, to be able to decide whether to cancel it or not. Also if there were a progress indicator for postgres vacuum operations, it would be really helpful.

Edit:

I'm not looking for a bullet-proof solution. Just a rough hint on the number of dead tuples or necessary I/O bytes is enough to decide. It is really annoying to have no clue when VACUUM will finish, whatsoever.

I've seen that pg_catalog.pg_stat_all_tables has a column for number of dead tuples. So it is possible to have an estimation, even if it means one has to ANALYZE the table before. On the other hand, autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor settings alone prove that postgres itself knows something about the amount of change on the tables and probably puts it in the hands of the DBA too.

I'm not sure what query to run, because when I run VACUUM VERBOSE, I see that not only tables, but indexes on them are being processed too.

share|improve this question

1 Answer 1

This is very hard to determine. You can tune autovacuuming to be more agressive or to be milder. But when set to mild and it is lagging behind and the base I/O load is too high, it can happen that it never reaches a proper vacuumed state - then you see the process running and running and running. Furthermore, later PostreSQL editions have much improved autovacuum capabilities, this alone may be enough to move to one of them (preferably 9.2 as the most recent one).

The progress bar sounds a good idea but I imagine it is not that easy to implement meaningfully. As you have constant load on your tables it is quite possible that the progress is apparently going backwards (I mean that the dead row count/percentage increases instead of decreasing) - then what conclusion do you draw?

share|improve this answer
1  
I prefer to see some sort of progress indicator, even if it goes backward, rather than nothing. –  aalizadeh Jun 18 '13 at 9:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.