Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a huge (~150 GBytes) postgresql database whose performance has dropped during the last weeks. In order to improve performance, I intend to perform FULL VACUUM, CLUSTER and ANALYZE.

In which order should I perform these operations? I don't mind the time it takes to perform, just the performance improvements of the database.

Thanks,

Udi

share|improve this question

1 Answer 1

up vote 5 down vote accepted

You should never run VACUUM FULL, if that's what you are talking about.

You should run VACUUM and ANALYZE regularly, on all databases. The easiest thing to do here is to just run VACUUM ANALYZE as one command, which will do both of them - that will also be faster.

Unless, that is, you can use autovacuum. If you're on 8.3 or newer, that would be the recommendation in most cases. Then you don't end up running it unnecessarily.

You shouldn't run CLUSTER on everything in the database, just because you can. If you have specific tables, run it there. But most workloads don't need a regular CLUSTER at all - and certainly not on all tables.

The tables that you do run CLUSTER on don't need VACUUM.

share|improve this answer
    
a. "you should never run VACUUM FULL" - Why? b. I cluster specific tables, of course. –  Adam Matan Aug 26 '09 at 16:25
    
VACUUM FULL is only intended to recover from situations where you haven't run VACUUM often enough. During normal operations, it shouldn't be needed. And in most cases, you can use CLUSTER instead of VACUUM FULL and it'll be faster.. –  Magnus Hagander Aug 27 '09 at 17:28

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.