6

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.

1 Answer 1

10

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.

2
  • a. "you should never run VACUUM FULL" - Why? b. I cluster specific tables, of course.
    – Adam Matan
    Aug 26, 2009 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.. Aug 27, 2009 at 17:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .